HotSpot Gateway pada router menyediakan kemampuan untuk otentikasi pada klien sebelum mendapatkan akses ke jaringan publik (internet). Dengan hotspot ini, pemilik jaringan dapat mengontrol siapa saja yang dapat mengakses ke jaringannya, berapa alat, berapa lama dan beberapa pengaturan lainnya.
Daftar Isi
Hotspot Untuk Waktu Koneksi Yang Bervariasi
Otentikasi hotspot dengan radius yang terhubung ke API/Webservice memungkinkan untuk membuat hotspot dengan masa aktif bervariatif. Hotspot dapat diatur dengan ketentuan: terdapat sebuah router dengan profil tamu dengan pengaturan hanya dapat terkoneksi ke 1 alat saja dan login setiap 1 hari sekali
— Hotspot Dengan Waktu Koneksi Internet Yang Bervariasi Untuk Tamu

Ide

- Terdapat sebuah router dengan beberapa profil hotspot, antara lain profil tamu dengan pengaturan hanya dapat terkoneksi ke 1 alat saja dan login maksimal beberapa hari.
- Mengakomodir adanya fitur Randomise Mac, yaitu dengan cara memutus koneksi dari alat yang dicurigai sebagai randomise Mac kemudian yang terkoneksi paling lama demi menjaga jumlah alat yang terkoneksi tidak melebihi pengaturan. Deteksi Randomise Mac: digunakan untuk melakukan login kurang dari 6 kali
##ORDER BY ifnull(loginnum, 0)>5, acctstarttime
- Login hotspot akan terkoneksi ke sebuah API
- Terdapat sebuah tabel yang menampung upass, di mana upass tersebut dapat belaku selama sekian hari dan penghitungan masa berlaku dimulai dari waktu login. Sebagai contoh, misalnya apabila masa berlaku 3 hari dan loginnya tanggal 1 Januari 2023, maka upass tersebut dapat dipakai untuk melakukan login pada tanggal 1 – 3 Januari 2023

Timeout
Timeout | Fungsi |
---|---|
keepalive-timeout | berapa lama waktu tunggu untuk perangkat terhubung kembali secara fisik |
idle-timeout | berapa lama waktu tunggu untuk perangkat kembali mengirimkan paket |
session-timeout | berapa lama waktu yang diberikan untuk pengguna |
lease | berapa lama waktu yang dibutuhkan oleh DHCP untuk melepaskan alamat dinamis dan bisa diperbaharui di tengah-tengah waktu |
Membuat Tabel dan Mekanisme Penyimpanan/Pengambilan Password
- database upass
#database MySQL
#membuat database
CREATE DATABASE `databasetamu`;
#membuat tabel untuk membuat data login ke hotspot untuk tamu
CREATE TABLE `databasetamu`.`tamu` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_tamu` varchar(100) NOT NULL,
`password` varchar(255) DEFAULT NULL,
`hari` smallint(5) unsigned DEFAULT NULL,
`tgl_mulai` date DEFAULT NULL,
`tgl_akhir` date DEFAULT NULL,
`tgl_generate` date DEFAULT NULL,
`kodepanggil` VARCHAR(100) NULL,
PRIMARY KEY (`id_tamu`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
ALTER TABLE `databasetamu`.`tamu`
ADD UNIQUE `unik_idtamu` (`id_tamu` ASC),
ADD INDEX `idx_cektgl` (`tgl_mulai` ASC, `tgl_akhir` ASC),
ADD INDEX `idx_tglgenerate` (`tgl_generate` ASC),
ADD INDEX `idx_kodepanggil` (`kodepanggil` ASC);
#membuat user untuk koneksi dari API ke database ini
CREATE USER user@localhost IDENTIFIED BY 'passworduser';
GRANT USAGE ON databasetamu.* TO user@localhost;
GRANT ALL ON databasetamu.* TO user@localhost;
FLUSH PRIVILEGES;
#apabila memasukkan data, maka tgl_generate akan otomatis terisi dengan tanggal saat ini menggunakan DATE(NOW())
DROP TRIGGER IF EXISTS `databasetamu`.`tamu_BEFORE_INSERT`;
DELIMITER $$
USE `databasetamu`$$
CREATE DEFINER=CURRENT_USER TRIGGER `databasetamu`.`tamu_BEFORE_INSERT` BEFORE INSERT ON `tamu` FOR EACH ROW
BEGIN
SET NEW.tgl_generate = DATE(NOW());
SET NEW.id_tamu = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tamu');
END$$
DELIMITER ;
#apabila user login ke hotspot, maka tgl_mulai diupdate dengan tanggal saat ini dan mengakibatkan tgl_akhir akan otomatis terisi sekian hari dari hari ini, sehingga upass itu hanya dapat dipakai sekian hari saja
DROP TRIGGER IF EXISTS `databasetamu`.`tamu_BEFORE_UPDATE`;
DELIMITER $$
USE `databasetamu`$$
CREATE DEFINER=CURRENT_USER TRIGGER `databasetamu`.`tamu_BEFORE_UPDATE` BEFORE UPDATE ON `tamu` FOR EACH ROW
BEGIN
#tanggal akhir akan berubah, jika
#mula-mula tgl_mulai kosong, kemudian sekarang tgl_mulai ada isinya dan
#|- tgl_akhir masih null, atau
#|- tanggal sekarang sebelum tgl_akhir (belum kadaluarsa)
IF ISNULL(OLD.tgl_mulai) AND NOT ISNULL(NEW.tgl_mulai) AND (ISNULL(NEW.tgl_akhir) OR DATE(NOW()) < NEW.tgl_akhir) THEN
SET NEW.tgl_akhir = DATE_ADD(NEW.tgl_mulai, INTERVAL NEW.hari-1 DAY);
END IF;
END$$
DELIMITER ;
#membuat password acak sepanjang 8 karakter
#UUID() akan membuat string acak UUID, tetapi ada bagian yang cenderung sama jika dipanggil dalam waktu berdekatan
#sehingga SHA2() 256-bit membuat string menjadi terlihat lebih acak
#ambil 8 karakter pertama dengan LEFT()
DELIMITER $$
CREATE DEFINER=CURRENT_USER FUNCTION `databasetamu`.`random_password`() RETURNS varchar(8) CHARSET latin1
BEGIN
RETURN LEFT(SHA2(UUID(), 256), 8);
END$$
DELIMITER ;
#membuat user sebanyak yang diinginkan
#kodepanggil_ dapat diisi dengan NULL, artinya tidak ada kode panggil
#tanggalkadaluarsa_ dapat diisi dengan NULL, artinya tidak ada kadaluarsanya
CREATE DEFINER=CURRENT_USER PROCEDURE `generate_password`(jumlah_ SMALLINT, kunci_enkripsi_ VARCHAR(255), hari_ SMALLINT, kodepanggil_ VARCHAR(100), tanggalkadaluarsa_ DATE)
BEGIN
DECLARE i_ INT;
DECLARE k_ VARCHAR(100);
SET k_ = ifnull(kodepanggil_, concat(random_password (), random_password ()));
SET i_ = 0;
WHILE i_ < jumlah_ DO
#password dibuat acak dengan random_password()
#password dienkripsi dalam bentuk teks dengan TO_BASE64(AES_ENCRYPT())
INSERT INTO `databasetamu`.tamu (password, hari, kodepanggil, tgl_akhir) VALUES (TO_BASE64(AES_ENCRYPT(random_password (), kunci_enkripsi_)), hari_, k_, tanggalkadaluarsa_);
SET i_ = i_ + 1;
END WHILE;
END$$
DELIMITER ;
#reset password
DELIMITER $$
USE `db_tamu`$$
CREATE PROCEDURE `reset_password` (id_tamu_ varchar(100), password_baru_ varchar(100), kunci_enkripsi_ varchar(255))
BEGIN
UPDATE tamu SET password=TO_BASE64(AES_ENCRYPT(password_baru_, kunci_enkripsi_)) WHERE id_tamu=id_tamu_;
END$$
#menampilkan data upass sesuai dengan tanggal yang diinginkan, guna dicetak untuk dibagi
DELIMITER $$
CREATE DEFINER=CURRENT_USER PROCEDURE `databasetamu`.`get_data_password_tglgenerate`(tglgenerate_ DATE, kunci_enkripsi_ VARCHAR(255))
BEGIN
#password diperlihatkan dengan didekripsi dalam bentuk teks menggunakan CONVERT(AES_DECRYPT(FROM_BASE64()), CHAR)
SELECT id_tamu, hari, tgl_mulai, tgl_akhir, CONVERT(AES_DECRYPT(FROM_BASE64(password), kunci_enkripsi_ ), CHAR) AS passwordasli, IF(ISNULL(tgl_akhir), ".", CONCAT(", maksimal digunakan pada ", tgl_akhir)) AS exp FROM `databasetamu`.tamu WHERE tgl_generate = tglgenerate_;
END$$
DELIMITER ;
#menampilkan data upass sesuai dengan kode panggil ketika generate password, guna dicetak untuk dibagi
DELIMITER $$
CREATE DEFINER=CURRENT_USER PROCEDURE `databasetamu`.`get_data_password_panggil`(kodepanggil_ VARCHAR(100), kunci_enkripsi_ VARCHAR(255))
BEGIN
#password diperlihatkan dengan didekripsi dalam bentuk teks menggunakan CONVERT(AES_DECRYPT(FROM_BASE64()), CHAR)
SELECT id_tamu, hari, tgl_mulai, tgl_akhir, CONVERT(AES_DECRYPT(FROM_BASE64(password), kunci_enkripsi_ ), CHAR) AS passwordasli, IF(ISNULL(tgl_akhir), ".", CONCAT(", maksimal digunakan pada ", tgl_akhir)) AS exp FROM `databasetamu`.tamu WHERE kodepanggil = kodepanggil_;
END$$
DELIMITER ;
#menampilkan data upass sesuai dengan id ketika generate password, guna dicetak untuk dibagi
DELIMITER $$
CREATE USER PROCEDURE `get_data_password_id`(id_tamu_ varchar(100), kunci_enkripsi_ varchar(255))
BEGIN
#password diperlihatkan dengan didekripsi dalam bentuk teks menggunakan CONVERT(AES_DECRYPT(FROM_BASE64()), CHAR)
SELECT id_tamu, hari, tgl_mulai, tgl_akhir, convert(AES_DECRYPT(from_base64(password), kunci_enkripsi_ ), char) as passwordasli, IF(ISNULL(tgl_akhir), ".", CONCAT(" dan terakhir pada ", tgl_akhir)) AS exp from tamu where id_tamu=id_tamu_;
END$$
DELIMITER ;
Membuat Mekanisme Login
- database upass
- api upass
- server radius
- router
#database MySQL
USE `databasetamu`;
DROP procedure IF EXISTS `cek_password`;
DELIMITER $$
USE `databasetamu`$$
CREATE PROCEDURE `cek_password` (id_tamu_ varchar(100), password_ varchar (100), kunci_enkripsi_ varchar(255))
BEGIN
DECLARE check_password_ BOOLEAN;
DECLARE timeoutmaksimal_ INT;
DECLARE timeouttamu_ INT;
#periksa apakah upass benar CONVERT(AES_DECRYPT(FROM_BASE64()))
#dan belum login ISNULL()/masih boleh login
SET check_password_ = IFNULL((SELECT CONVERT(AES_DECRYPT(FROM_BASE64(password), kunci_enkripsi_), char)=password_ AS passwordasli FROM tamu WHERE id_tamu=id_tamu_ AND (ISNULL(tgl_mulai) OR (tgl_mulai <= DATE(NOW()) and tgl_akhir >= DATE(NOW())))), 0);
#apabila diperbolehkan login dan tgl_mulai belum diisi, maka tgl_mulai diisi dengan tanggal sekarang
IF check_password_ THEN
UPDATE tamu SET tgl_mulai=DATE(NOW()) WHERE id_tamu=id_tamu_ AND ISNULL(tgl_mulai);
END IF;
#menghitung waktu timeout, maksmimal setiap 30 hari
SET timeoutmaksimal_ = 24 * 60 * 60 * 30;
SET timeouttamu_ = ifnull((SELECT TIME_TO_SEC(TIMEDIFF(CONCAT(tgl_akhir, ' 23:59:59'), NOW())) + 1 FROM databasetamu.tamu where id_tamu=id_tamu_), 24 * 60 * 60);
#mengembalikan status apakah boleh login atau tidak
SELECT check_password_ as isallowed, if(timeouttamu_ > timeoutmaksimal_, timeoutmaksimal_, timeouttamu_) as timeout;
END$$
DELIMITER ;
//alamat: https://service.example.org/ceklogin.php
//file: /var/www/service/ceklogin.php
//pengaturan doc-root ke /var/www/service untuk subdomain service ada di /etc/apache2/sites-enabled atau /etc/nginx/sites-enabled
//webservice berbasis PHP yang digunakan oleh login hotspot untuk memeriksa upass
<?php
//kunci enkripsi
$kunci = "iniadalahkuncienkripsi";
//menangkap user dan password
$user = filter_input(INPUT_POST, "user", FILTER_SANITIZE_STRING);
$pass = filter_input(INPUT_POST, "pass", FILTER_SANITIZE_STRING);
$servername_dbtamu = "db.example.org";
$username_dbtamu = "userdbtamu";
$password_dbtamu = "passworddbtamu";
$dbname_dbtamu = "databasetamu";
//koneksi ke basisdata
$conntamu = new mysqli($servername_dbtamu, $username_dbtamu, $password_dbtamu, $dbname_dbtamu);
$conntamu->init();
$conntamu->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
$conntamu->ssl_set('/etc/mysql/ssl/client-key.pem', '/etc/mysql/ssl/client-cert.pem', '/etc/mysql/ssl/ca-cert.pem', NULL, NULL);
$conntamu->real_connect($servername_dbtamu, $username_dbtamu, $password_dbtamu, $dbname_dbtamu, 3306, NULL, MYSQLI_CLIENT_SSL);
if ($conntamu->connect_error)
{
die('CONNERR');
}
//cek user non tamu di sini
//berikan idle-timeout yang berbeda-beda sebagai id grup
//misalnya:
//grp-tamu: ""
//define ("TIMEOUT_PEGAWAI_", 86400);
//define ("TIMEOUT_PIMPINAN_", 86401);
//artinya, gunakan idle-timeout untuk mengetahui di router user tersebut masuk grup mana, misalnya: jika di router ada user dengan idle-timeout 86400 (1d 00:00:00) maka itu pegawai; jika di router ada user dengan idle-timeout 86401 (1d 00:00:01) maka itu pimpinan
//cek password menggunakan function cek_password yang telah dibuat di basis data
$stmt = $conntamu->prepare("call `databasetamu`.`cek_password`(?, ?, ?)");
$stmt->bind_param("sss", $user, $pass, $kunci);
$stmt->execute();
if ($result = $stmt->get_result())
{
$row = $result->fetch_assoc();
if (is_array($row))
{
if ($row['isallowed'])
{
echo json_encode(["isallowed" => true, "timeout" => $row["timeout"], "loginas" => "tamu"]);
exit();
}
}
}
//tidak memenuhi semua testing user di atas
echo json_encode(["isallowed" => false, "timeout" => 0, "loginas" => ""]);
Pada server radius, terdapat dua buah server, yaitu nginx/apache2 untuk menangani API dan freeradius untuk berperan sebagai server radius. Harus HTTPS.
Konsep:
- yang bisa menggunakan internet adalah user yang username-nya terdaftar pada tabel radcheck, sehingga perlu ada mekanisme untuk user yang sudah tidak valid lagi di API, yaitu dengan cara me-rename username-nya di tabel radcheck
- password dapat dibuat model one time password (OTP) untuk otentikasi menggunakan API
<?
//alamat: https://radius.example.org/cek.php
//file: /var/www/hotspot/cek.php
//pengaturan doc-root ke /var/www/hotspot ada di /etc/apache2/sites-enabled atau /etc/nginx/sites-enabled
//hotspot dengan freeradius 3.0
//otentikasi menggunakan API
//alamat dari router, di mana pengaturan hotspot berada
$urlRedirect = "http://unisa.wifi/login";
$urlApi = "https://service.example.org/ceklogin.php";
$publicProfileHostpot = 'radius-profile';
$passwordAttribute = 'Cleartext-Password';
//ubah variabel $unik dengan string random
//tidak boleh diganti dengan rand(), uuid() atau fungsi random.. harus diganti dengan string random
$unik = "bXLlSpeNtnr2uTm";
$db1 = [
'host' => 'localhost',
'username' => 'userdatabaseradius',
'password' => 'passworddatabaseradius',
'database' => 'radius',
];
$connhotspot = new mysqli($db1['host'], $db1['username'], $db1['password'], $db1['database']);
$connhotspot->init();
$connhotspot->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
$connhotspot->ssl_set('/etc/mysql/ssl/client-key.pem', '/etc/mysql/ssl/client-cert.pem', '/etc/mysql/ssl/ca-cert.pem', NULL, NULL);
$connhotspot->real_connect($db1['host'], $db1['username'], $db1['password'], $db1['database'], 3306, NULL, MYSQLI_CLIENT_SSL);
if ($connhotspot->connect_error) {
die('Connection failed');
}
$username = $_POST['username'];
$password = $_POST['password'];
$via = $_POST['via'];
//cek upass melalui API
$curl = curl_init();
curl_setopt_array($curl, [
CURLOPT_URL => $urlApi,
CURLOPT_RETURNTRANSFER => true,
CURLOPT_ENCODING => '',
CURLOPT_MAXREDIRS => 10,
CURLOPT_TIMEOUT => 30,
CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
CURLOPT_CUSTOMREQUEST => 'POST',
CURLOPT_POSTFIELDS => 'user='.$username.'&pass='.$password,
CURLOPT_HTTPHEADER => [
'content-type: application/x-www-form-urlencoded',
],
]);
$response = curl_exec($curl);
$err = curl_error($curl);
curl_close($curl);
$block = "_BLOCK_".$unik."_";
//jika koneksi API gagal
if ($err) {
header('Location: '.$urlRedirect);
exit;
} else {
$api_result = json_decode($response, true);
//jika variabel isallowed bernilai true
if (true == $api_result['isallowed']) {
// berhasil login
$role = $api_result['loginas'];
$timeout = $api_result['timeout'];
//password sebenarnya hanya digunakan sekali, pada saat redirect, sehingga password dibuat sistem OTP
$password = substr(md5(uniqid()),20);
//jika berhasil login, maka hilangkan _BLOCK_
$queryUpdateRadcheck = "UPDATE radcheck SET username=? WHERE username=? and attribute=?";
$stmt = $connhotspot->prepare($queryUpdateRadcheck);
$userblok = $username.$block;
$stmt->bind_param("sss", $username, $userblok, $passwordAttribute);
$stmt->execute();
$queryRadcheck = "SELECT id,username FROM radcheck WHERE username=? AND attribute=?";
$stmt = $connhotspot->prepare($queryRadcheck);
$stmt->bind_param("ss", $username, $passwordAttribute);
$stmt->execute();
$getRadcheck = $stmt->get_result();
if ($getRadcheck->num_rows <= 0) {
// insert to radcheck
$queryInsertRadcheck = "INSERT INTO radcheck (username, attribute, op, value) VALUES (?, ?, ':=',?)";
$stmt = $connhotspot->prepare($queryInsertRadcheck);
$stmt->bind_param("sss", $username, $passwordAttribute, $password);
$stmt->execute();
$queryInsertRadusergroup = "INSERT INTO radusergroup (username, groupname, priority) VALUES (?, ?, '1')";
$stmt = $connhotspot->prepare($queryInsertRadusergroup);
$groupname = $publicProfileHostpot.'-'.$role;
$stmt->bind_param("ss", $username, $groupname);
$stmt->execute();
} else {
// update password
$result = $getRadcheck->fetch_assoc();
$queryUpdateRadcheck = "UPDATE radcheck SET value=? WHERE id=?";
$stmt = $connhotspot->prepare($queryUpdateRadcheck);
$id = $result['id'];
$stmt->bind_param("ss", $password, $id);
$stmt->execute();
}
if (in_array($role, ["tamu"]))
{
$sessiontimeout = $timeout;
$idletimeout = 0;
}
else
{
$sessiontimeout = 0;
$idletimeout = $timeout;
}
//di mikrotik, idle-timeout harus dibuat none pada profile user
//pengaturan time out sesuai dengan informasi di basis data
//insert/update sisa hari, maksimal 30 hari
//misalnya, tamu memiliki jatah 45 hari, maka akan login 30 hari, kemudian 15 hari
$queryInsertTimeout = "INSERT INTO radreply (username, attribute, op, value) VALUES (?, 'Session-Timeout', ':=',?), (?, 'Idle-Timeout', ':=',?) ON DUPLICATE KEY UPDATE value=VALUES(value)";
$stmt = $connhotspot->prepare($queryInsertTimeout );
$stmt->bind_param("ssss", $username, $sessiontimeout, $username, $idletimeout);
$stmt->execute();
}
else {
//jika gagal login, maka tambahkan _BLOCK_
$queryUpdateRadcheck = "UPDATE radcheck SET username=? WHERE username=? and attribute=?";
$stmt = $connhotspot->prepare($queryUpdateRadcheck);
$newpass = $username.$block;
$stmt->bind_param("sss", $newpass, $username, $passwordAttribute);
$stmt->execute();
$password = "";
}
//kembalikan ke router
if (empty($via))
{
$urlRedirect = "http://unisa.wifi/login";
header('Location:'.$urlRedirect.'?username='.$username.'&password='.$password);
}
echo json_encode(["password" => $password, , "api_status" => $api_result]);
}
Pengaturan basis data freeradius ada di /etc/freeradius/3.0/mods-enabled/sql dan /etc/freeradius/3.0/mods-config/sql/main/mysql/queries.conf
#lihat tabel radcheck, belum ada yang diblok
SELECT id, username FROM radcheck where username like '%block%';
#Empty set (0.00 sec)
SELECT id, username FROM radcheck where username = 'tamu';
#+----+----------+
#| id | username |
#+----+----------+
#| 3 | tamu |
#+----+----------+
#1 rows in set (0.00 sec)
#Query OK, 0 rows affected (0.04 sec)
#lihat tabel radcheck setelah ada yang diblok karena user sudah tidak valid di API
SELECT id, username FROM radcheck where username like '%block%';
#+----+-----------------------------+
#| id | username |
#+----+-----------------------------+
#| 3 | tamu_BLOCK_bXLlSpeNtnr2uTm_ |
#+----+-----------------------------+
#1 rows in set (0.00 sec)
#Query OK, 0 rows affected (0.04 sec)
#persiapan limit user
ALTER TABLE `radius`.`radcheck`
ADD UNIQUE INDEX `radcheck_unique` (`username` ASC, `attribute` ASC);
ALTER TABLE `radius`.`radgroupcheck`
ADD UNIQUE INDEX `radgroupcheck_unique` (`groupname` ASC, `attribute` ASC);
ALTER TABLE `radius`.`radreply`
ADD UNIQUE INDEX `radreply_unique` (`username` ASC, `attribute` ASC);
ALTER TABLE `radius`.`radgroupreply`
ADD UNIQUE INDEX `radgroupreply_unique` (`groupname` ASC, `attribute` ASC);
#OTP (one time password)
#mengubah password di radcheck menjadi password lain
#agar tidak dapat digunakan login dengan login?username=<username>&password=<password>
DROP TRIGGER IF EXISTS `radius`.`radpostauth_AFTER_INSERT`;
DELIMITER $$
USE `radius`$$
CREATE DEFINER = CURRENT_USER TRIGGER `radius`.`radpostauth_AFTER_INSERT` AFTER INSERT ON `radpostauth` FOR EACH ROW
BEGIN
#update password karena sistemnya OTP, baik login sukses maupun gagal
UPDATE radcheck SET value=left(md5(uuid()), 20) WHERE username=NEW.username AND attribute="Cleartext-Password";
#tandai akun yang melebihi batas
IF NEW.reply = "Access-Accept" THEN
#tandai koneksi yang melebihi batas
call `mark_to_stop`(NEW.username, NEW.callingstationid);
#register ke radrandomisemacfactor mac yang berhasil login, asumsinya jika sudah dipakai lebih dari sama dengan 5 maka dianggap bukan randomise mac
INSERT INTO radrandomisemacfactor (username, callingstationid) VALUES (NEW.username, NEW.callingstationid) ON DUPLICATE KEY UPDATE loginnum=loginnum+1;
END IF;
END$$
DELIMITER ;
Pengaturan sebuah grup dapat dilakukan di freeradius pada tabel radgroupreply dan pengaturan sebuah user dapat dilakukan pada tabel radreply, tetapi tidak semua attribute di router dapat disesuaikan. Apabila ada atribut yang sama di radgrupreply dan radreply, maka yang digunakan adalah value yang ada di radgrupreply. Untuk mikrotik dapat dibaca di https://wiki.mikrotik.com/wiki/Manual:RADIUS_Client#Supported_RADIUS_Attributes

Pada kesempatan kali ini akan dibatasi up/download (WISPr-Bandwidth-Max-Up/WISPr-Bandwidth-Max-Down).
SELECT * FROM radius.radgroupreply where groupname='grp-tamu';
#+----+-----------+--------------------------+----+---------+
#| id | groupname | attribute | op | value |
#+----+-----------+--------------------------+----+---------+
#| 1 | grp-tamu | WISPr-Bandwidth-Max-Up | := | 2000000 |
#| 2 | grp-tamu | WISPr-Bandwidth-Max-Down | := | 2000000 |
#+----+-----------+--------------------------+----+---------+
#2 rows in set (0.00 sec)
#Query OK, 0 rows affected (0.04 sec)
#Idle-Timeout dapat diaplikasikan di radreply dan radgroupreply apabila idle-timeout bernilai none di profile user di mikrotik
Berapa lama harus login lagi khusus untuk tamu (Session-Timeout dalam detik) dan berapa lama non-tamu dapat idle (Idle-Timeout dalam detik) sudah diatur di cek.php.
Berapa alat bisa login (Port-Limit/Simultaneous-Use) diatur di bagian Limit Alat.

Pengaturan di bawah ini penting untuk menghindari lebih dari satu user active dengan mac yang sama karena mendapatkan lease dhcp yang baru, sehingga dapat menimbulkan pesan “no more sessions are allowed”
#lease DHCP dibuat 30 hari
#dibuat script di onlogout di profile user
#sebaiknya gunakan jaringan kelas B
ip dhcp-server print
Flags: D - dynamic, X - disabled, I - invalid
# NAME INTERFACE RELAY ADDRESS-POOL LEASE-TIME ADD-ARP
0 dhcp1 eth1 hs-pool-1 30d 00:00:00
ip hotspot print
Flags: X - disabled, I - invalid, S - HTTPS
# NAME INTERFACE ADDRESS-POOL PROFILE IDLE-TIMEOUT
0 hs-eth1 eth1 eth1-public none
#pengaturan profil user di router
#Idle-Timeout tidak diaktifkan di server (none)
#dibuat 1 profil dengan jumlah user sebanyak-banyaknya
#mekanisme login setiap berapa hari sekali ada di Hotspot (Session-Timeout dan Idle-Timeout), sehingga kedua timeout tersebut dan keepalive-timeout di router tidak diaktifkan
#idle-timeout diisi dengan none
ip hotspot user profile print
Flags: * - default
0 * name="default" idle-timeout=none status-autorefresh=1m shared-users=10000000 add-mac-cookie=yes mac-cookie-timeout=1d insert-queue-before=bottom parent-queue=queue-user address-list="" transparent-proxy=no on-login=:local activeremoved 0\r\n:local freeuser;\r\n:set freeuser $user;\r\n:local freemacaddress;\r\n:set freemacaddress $"mac-address";\r\n:local freeaddress;\r\n:set freeaddress $address;\r\n# dapatkan informasi server yang digunakan untuk login\r\n:local freeserver [/ip hotspot active get [find user="$freeuser" and mac-address="$freemacaddress" and address="$freeaddress"] server];\r\n# apabila informasi server ada\r\nif ($freeserver!="") do={\r\n# cari apakah user dan mac ini sudah active dengan address yang berbeda pada server yang sama\r\n:local freeactiveposlist [ip hotspot active find user="$freeuser" and mac-address="$freemacaddress" and address!="$freeaddress" and server="$freeserver"];\r\n:foreach freeactivepos in=$freeactiveposlist do={\r\n# apabila posisi ditemukan, maka hapus dari active\r\nif ($freeactivepos!="") do={\r\n:set activeremoved ($activeremoved+1);\r\n/ip hotspot active remove [find .id=$freeactivepos];\r\n/log info "onlogin hotspot script: login $user, try to remove address not($freeaddress), mac $freemacaddress and server $freeserver from active user at position $freeaddresspos";\r\n}\r\n}\r\n}\r\nif ($activeremoved=0) do={\r\n/log info "onlogin hotspot script: login $user, no active user to remove for address not($freeaddress), mac $freemacaddress and server $freeserver";\r\n}\r\n\r\n#otomatis hapus active ketika ada login yang melebihi ketentuan, untuk mengantisipasi randomise mac address (khusus 1 hotspot di router dan 1 router)\r\n#:local maxactivenum;\r\n#:local freeactivenum [:len [ip hotspot active find user="$freeuser"]];\r\n#:local freeidletimeout [/ip hotspot active get [find user="$freeuser" and mac-address="$freemacaddress" and address="$freeaddress"] idle-timeout];\r\n#group menggunakan idle-timeout, samakan dengan di cek.php dan radreply\r\n#Simultanous-use di radgroupreply diset $maxactivenum + 1, misalnya pegawai hanya boleh 2 alat, maka Simultanous-use di radroupreply adalah 3\r\n#if ($freeidletimeout=[:totime 43200]) do={\r\n#:set $maxactivenum 2;\r\n#}\r\n#if ($freeidletimeout=[:totime 259200]) do={\r\n#:set $maxactivenum 2;\r\n#}\r\n#if ($freeidletimeout=[:totime 604800]) do={\r\n#:set $maxactivenum 2;\r\n#} else={\r\n#:set $maxactivenum 1;\r\n#}\r\n#if ($freeactivenum>=($maxactivenum+1)) do={\r\n#:local freeactivemaxpos [:pick [/ip hotspot active find user="$freeuser"] 0];\r\n#if ($freeactivemaxpos!="") do={\r\n#/ip hotspot active remove [find .id=$freeactivemaxpos];\r\n#/log info "onlogin hotspot script: login $user, maximum reach, remove number 0 at position $freeactivemaxpos";\r\n#}\r\n#}\r\n\r\n/log info "onlogin hotspot script: login $user. fin."; on-logout=:local addressremoved 0\r\n# implementasi yang salah\r\n# implementasi ini akan menyebabkan eksekusi /ip dhcp-server lease remove; yang artinya menghapus semua lease\r\n# :local freeaddresspos [ip dhcp-server lease find address=$address];\r\n# implementasi yang benar, variabel address dan mac-address yang diambil dari tabel active dimasukkan dulu ke local\r\n:local freeaddress;\r\n:set freeaddress $address;\r\n:local freemacaddress;\r\n:set freemacaddress $"mac-address";\r\n# cari posisi lease dhcp IP dari user yang log-out\r\n:local freeaddressposlist [ip dhcp-server lease find address="$freeaddress" and mac-address="$freemacaddress"];\r\n:foreach freeaddresspos in=$freeaddressposlist do={\r\n# apabila posisi ditemukan, maka hapus lease dhcp IP dari user yang log-out\r\nif ($freeaddresspos!="") do={\r\n:set addressremoved ($addressremoved+1);\r\n/ip dhcp-server lease remove [find .id=$freeaddresspos];\r\n/log info "onlogout hotspot script: logout $user, try to remove address $freeaddress and mac $freemacaddress from lease at position $freeaddresspos";\r\n}\r\n}\r\nif ($addressremoved=0) do={\r\n/log info "onlogout hotspot script: logout $user, no lease to remove for address $freeaddress and mac $freemacaddress";\r\n}\r\n/log info "onlogout hotspot script: logout $user. fin.";
#untuk pembatasan koneksi akan otomatis menambah active user, sesuai dengan pengaturan di hotspot, yaitu pada
#Port-Limit
#untuk pembatasan up/down akan otomatis menambah queue, sesuai dengan pengaturan di hotspot, yaitu pada
#WISPr-Bandwidth-Max-Up/WISPr-Bandwidth-Max-Down
#Contoh problem ketika Lease DHCP terlalu pendek daripada Session-Timeout atau Idle-Timeout Hotspot, sehingga potensial memunculkan "no more sessions are allowed"

Apabila diperlukan, dapat dibuat penjadwalan untuk melepas lease yang gagal dilepas otomatis ketika koneksi user putus/dihapus dari active
#hanya melepas lease dari server dhcp ini
:local dhcpserverlist {"dhcp1";"dhcp2"};
:local leaseaddress;
:local leasemacaddress;
:local activerowaddress;
:local bindingrowmacaddress;
:local leaselist;
/log info "scheduled script: release lease DHCP <> hotspot active. start."
#memproses untuk setiap server dhcp
foreach dhcpserverrow in=$dhcpserverlist do={
#daftar semua lease untuk setiap server dhcp
:set leaselist [/ip dhcp-server lease find server=$dhcpserverrow and dynamic];
#memproses untuk setiap lease
foreach leaserow in=$leaselist do={
#dapatkan alamat IP dan Mac Address di lease per baris
:set leaseaddress [/ip dhcp-server lease get $leaserow address];
:set leasemacaddress [/ip dhcp-server lease get $leaserow mac-address];
#jika terdapat alamat IP di lease
if ($leaseaddress!="") do={
#mencari apakah alamat IP di lease ada di active atau di ip-binding
:set activerowaddress [/ip hotspot active find address="$leaseaddress"];
:set bindingrowmacaddress [/ip hotspot ip-binding find mac-address="$leasemacaddress"];
#jika tidak ada, maka lease tersebut dilepas
if ($activerowaddress="" and $bindingrowmacaddress="" and $leaserow!="") do={
/ip dhcp-server lease remove [find .id=$leaserow];
}
}
#end foreach leaserow
}
#end foreach dhcpserverrow
}
/log info "scheduled script: release lease DHCP <> hotspot active. fin."
Limit Alat
- Basic
- Advanced
Matikan accounting pada Router.
Pada basis data di server radius
DELIMITER $$
USE `radius`$$
CREATE DEFINER = CURRENT_USER TRIGGER `radius`.`radgroupreply_BEFORE_INSERT` BEFORE INSERT ON `radgroupreply` FOR EACH ROW
BEGIN
IF NEW.attribute = "Port-Limit" THEN
INSERT INTO radgroupcheck (groupname, attribute, op, value) VALUES (NEW.groupname, "Simultaneous-Use", NEW.op, NEW.value) ON DUPLICATE KEY UPDATE op=NEW.op, attribute="Simultaneous-Use", value=NEW.value;
END IF;
END$$
DELIMITER ;
DROP TRIGGER IF EXISTS `radius`.`radgroupreply_BEFORE_UPDATE`;
DELIMITER $$
USE `radius`$$
CREATE DEFINER = CURRENT_USER TRIGGER `radius`.`radgroupreply_BEFORE_UPDATE` BEFORE UPDATE ON `radgroupreply` FOR EACH ROW
BEGIN
IF NEW.attribute = "Port-Limit" THEN
INSERT INTO radgroupcheck (groupname, attribute, op, value) VALUES (NEW.groupname, "Simultaneous-Use", NEW.op, NEW.value) ON DUPLICATE KEY UPDATE op=NEW.op, attribute="Simultaneous-Use", value=NEW.value;
END IF;
END$$
DELIMITER ;
DROP TRIGGER IF EXISTS `radius`.`radreply_BEFORE_INSERT`;
DELIMITER $$
USE `radius`$$
CREATE DEFINER = CURRENT_USER TRIGGER `radius`.`radreply_BEFORE_INSERT` BEFORE INSERT ON `radreply` FOR EACH ROW
BEGIN
IF NEW.attribute = "Port-Limit" THEN
INSERT INTO radcheck (username, attribute, op, value) VALUES (NEW.username, "Simultaneous-Use", NEW.op, NEW.value) ON DUPLICATE KEY UPDATE op=NEW.op, attribute="Simultaneous-Use", value=NEW.value;
END IF;
END$$
DELIMITER ;
DROP TRIGGER IF EXISTS `radius`.`radreply_BEFORE_UPDATE`;
DELIMITER $$
USE `radius`$$
CREATE DEFINER = CURRENT_USER TRIGGER `radius`.`radreply_BEFORE_UPDATE` BEFORE UPDATE ON `radreply` FOR EACH ROW
BEGIN
IF NEW.attribute = "Port-Limit" THEN
INSERT INTO radcheck (username, attribute, op, value) VALUES (NEW.username, "Simultaneous-Use", NEW.op, NEW.value) ON DUPLICATE KEY UPDATE op=NEW.op, attribute="Simultaneous-Use", value=NEW.value;
END IF;
END$$
DELIMITER ;
UPDATE `radius`.`radgroupreply` SET groupname=replace(groupname, "_UNUSED", "") where attribut='Port-Limit' and groupname='grp-tamu';
UPDATE `radius`.`radgroupreply` SET groupname=concat(groupname, '_UNUSED') where attribut='Acct-Interim-Interval' and groupname='grp-tamu';
UPDATE `radius`.`radgroupcheck` SET groupname=concat(groupname, '_UNUSED') where attribut='Simultaneous-Use' and groupname='grp-tamu';;
#1 user maksimal 1 device, jika 2 device maka tinggal mengganti value dengan batas+2
INSERT INTO `radius`.`radgroupreply (groupname, attribute, op, value) VALUES ('grp-tamu', 'Port-Limit', ':=', '3');
Pada Onlogin User Profile di Router, hilangkan komentar di bawah #otomatis hapus active ketika ada login yang melebihi ketentuan, untuk mengantisipasi randomise mac address (khusus 1 hotspot di router dan 1 router)
Hidupkan accounting pada Router.
Pengaturan accounting menggunakan attribut Acct-Interim-Interval dengan satuan detik di tabel radreply atau radgroupreply. Nilai yang direkomendasikan secara internasional adalah 60 – 600.
#bilangan prima antara 60 - 600
61 67 71 73 79 83 89 97 101 103 107 109 113 127 131 137 139 149 151 157 163 167 173 179 181 191 193 197 199 211 223 227 229 233 239 241 251 257 263 269 271 277 281 283 293 307 311 313 317 331 337 347 349 353 359 367 373 379 383 389 397 401 409 419 421 431 433 439 443 449 457 461 463 467 479 487 491 499 503 509 521 523 541 547 557 563 569 571 577 587 593 599
Nilai yang kami rekomendasikan adalah bilangan prima antara 60 – 600 untuk memperkecil kemungkinan penumpukan proses update akunting dari router ke server radius. Setiap grup sebaiknya dengan nilai Acct-Interim-Interval yang berbeda-beda, di mana grup dengan estimasi pengguna terbanyak menggunakan nilai terbesar, misalnya grp-tamu kemungkinan ada 1000 user dan grp-pegawai ada 50 user, maka grp-tamu diset 599 dan grp-pegawai diset 593.
Atur pembatasan di basis data server radius
DROP TRIGGER IF EXISTS `radius`.`radgroupreply_BEFORE_INSERT`;
DROP TRIGGER IF EXISTS `radius`.`radgroupreply_BEFORE_UPDATE`;
DROP TRIGGER IF EXISTS `radius`.`radreply_BEFORE_INSERT`;
DROP TRIGGER IF EXISTS `radius`.`radreply_BEFORE_UPDATE`;
UPDATE `radius`.`radgroupcheck` SET groupname=replace(groupname, "_UNUSED", "") where attribut='Simultaneous-Use' and groupname='grp-tamu';
UPDATE `radius`.`radgroupreply` SET groupname=replace(groupname, "_UNUSED", "") where attribut='Port-Limit' and groupname='grp-tamu';
UPDATE `radius`.`radgroupreply` SET groupname=concat(groupname, '_UNUSED') where attribut='Port-Limit' and groupname='grp-tamu';
#1 user maksimal 1 device, jika 2 device maka tinggal mengganti value dengan batas+2
INSERT INTO `radius`.`radgroupcheck` (groupname, attribute, op, value) VALUES ('grp-tamu', 'Simultaneous-Use', ':=', '3');
INSERT INTO `radius`.`radgroupreply` (groupname, attribute, op, value) VALUES ('grp-tamu', 'Acct-Interim-Interval', ':=', '600');
Buat mekanisme untuk pembersihan radacct agar proses dapat berjalan dengan cepat melalui event (pastikan event_scheduler=on di MySQL)
#pembuatan tabel radacct_log sebagai penampung data radacct yang sudah selesai (stop)
USE `radius`;
ALTER TABLE `radius`.`radacct`
CHANGE COLUMN `radacctid` `radacctid` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
ADD COLUMN `logmarker` TINYINT(3) UNSIGNED NULL DEFAULT 0 AFTER `class`,
ADD INDEX `logmarker` (`logmarker` ASC);
ALTER TABLE `radius`.`radpostauth`
CHANGE COLUMN `id` `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD COLUMN `logmarker` TINYINT(3) UNSIGNED NULL DEFAULT 0 AFTER `class`,
ADD COLUMN `callingstationid` VARCHAR(50) NOT NULL DEFAULT '' AFTER `pass`,
ADD INDEX `logmarker` (`logmarker` ASC),
ADD INDEX `authdate` (`authdate` ASC);
CREATE TABLE `radacct_log` (
`radacctid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`acctsessionid` varchar(64) NOT NULL DEFAULT '',
`acctuniqueid` varchar(32) NOT NULL DEFAULT '',
`username` varchar(64) NOT NULL DEFAULT '',
`realm` varchar(64) DEFAULT '',
`nasipaddress` varchar(15) NOT NULL DEFAULT '',
`nasportid` varchar(32) DEFAULT NULL,
`nasporttype` varchar(32) DEFAULT NULL,
`acctstarttime` datetime DEFAULT NULL,
`acctupdatetime` datetime DEFAULT NULL,
`acctstoptime` datetime DEFAULT NULL,
`acctinterval` int(11) DEFAULT NULL,
`acctsessiontime` int(10) unsigned DEFAULT NULL,
`acctauthentic` varchar(32) DEFAULT NULL,
`connectinfo_start` varchar(128) DEFAULT NULL,
`connectinfo_stop` varchar(128) DEFAULT NULL,
`acctinputoctets` bigint(20) DEFAULT NULL,
`acctoutputoctets` bigint(20) DEFAULT NULL,
`calledstationid` varchar(50) NOT NULL DEFAULT '',
`callingstationid` varchar(50) NOT NULL DEFAULT '',
`acctterminatecause` varchar(32) NOT NULL DEFAULT '',
`servicetype` varchar(32) DEFAULT NULL,
`framedprotocol` varchar(32) DEFAULT NULL,
`framedipaddress` varchar(15) NOT NULL DEFAULT '',
`framedipv6address` varchar(45) NOT NULL DEFAULT '',
`framedipv6prefix` varchar(45) NOT NULL DEFAULT '',
`framedinterfaceid` varchar(44) NOT NULL DEFAULT '',
`delegatedipv6prefix` varchar(45) NOT NULL DEFAULT '',
`class` varchar(64) DEFAULT NULL,
`logmarker` tinyint(3) unsigned DEFAULT 0,
PRIMARY KEY (`radacctid`),
KEY `username` (`username`),
KEY `acctstarttime` (`acctstarttime`),
KEY `acctstoptime` (`acctstoptime`)
) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
CREATE TABLE `radpostauth_log` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`username` varchar(64) NOT NULL DEFAULT '',
`pass` varchar(64) NOT NULL DEFAULT '',
`callingstationid` varchar(50) NOT NULL DEFAULT '',
`reply` varchar(32) NOT NULL DEFAULT '',
`authdate` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`class` varchar(64) DEFAULT NULL,
`logmarker` tinyint(1) unsigned DEFAULT 0,
PRIMARY KEY (`id`),
KEY `username` (`username`),
KEY `authdate` (`authdate`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
#optimasi dengan menambah index
ALTER TABLE `radius`.`radacct`
ADD INDEX `callingstationid` (`callingstationid` ASC);
ALTER TABLE `radius`.`radusergroup`
ADD INDEX `groupname` (`groupname` ASC);
ALTER TABLE `radius`.`radgroupreply`
ADD INDEX `attribute` (`attribute` ASC);
ALTER TABLE `radius`.`radreply`
ADD INDEX `attribute` (`attribute` ASC);
ALTER TABLE `radius`.`radgroupcheck`
ADD INDEX `attribute` (`attribute` ASC);
ALTER TABLE `radius`.`radcheck`
ADD INDEX `attribute` (`attribute` ASC);
#fasilitas untuk mengecek status user
USE `radius`;
DROP procedure IF EXISTS `radius`.`accounting_check`;
DELIMITER $$
USE `radius`$$
CREATE DEFINER=CURRENT_USER PROCEDURE `accounting_check`(username_ varchar(100))
BEGIN
declare macnumber_ int;
set macnumber_ = (SELECT COUNT(*)
FROM (SELECT DISTINCT callingstationid FROM radius.radacct
WHERE username = username_
AND acctstoptime IS NULL) mac_count);
SELECT macnumber_ as simul_count, count(radacctid) as registered_count, radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, callingstationid, framedprotocol
FROM radius.radacct
WHERE username = username_
AND acctstoptime IS NULL
GROUP BY callingstationid;
END$$
DELIMITER ;
#prosedur pembersihan
USE `radius`;
DROP procedure IF EXISTS `radacct_cleaner`;
DELIMITER $$
USE `radius`$$
CREATE DEFINER=CURRENT_USER PROCEDURE `radacct_cleaner`()
BEGIN
#paksa stop untuk yang tidak update lebih dari 3 kali interval acct-interim
UPDATE radius.radacct a, radius.radusergroup ug, radgroupreply gr
SET a.acctstoptime=NOW(), a.acctterminatecause='NAS ERROR'
WHERE ug.username=a.username AND gr.groupname=ug.groupname
AND gr.attribute='Acct-Interim-Interval' AND ISNULL(a.acctstoptime)
AND time_to_sec(timediff(now(), acctupdatetime)) > ((gr.value+1) * 3);
#pindah yang sudah stop ke log
UPDATE radacct SET logmarker=1 WHERE NOT ISNULL(acctstoptime);
INSERT INTO radacct_log SELECT * FROM radacct WHERE logmarker=1 ON DUPLICATE KEY UPDATE radacct_log.radacctid=radacct.radacctid;
DELETE FROM radacct WHERE logmarker=1;
#pindah data otentikasi yang tidak ada di acct ke log
UPDATE radpostauth SET logmarker=1 WHERE username not in (SELECT username FROM `radacct` where ISNULL(acctstoptime));
INSERT INTO radpostauth_log SELECT * FROM radpostauth WHERE logmarker=1 ON DUPLICATE KEY UPDATE radpostauth_log.id=radpostauth.id;
DELETE FROM radpostauth WHERE logmarker=1;
#bersihkan log randomise mac, randomise mac: 41 hari tidak ada aktivitas login, device mac: 151 hari tidak ada aktivitas login
DELETE FROM radrandomisemacfactor
WHERE (lastlogindate < DATE_ADD(DATE(NOW()), INTERVAL -41 DAY) AND loginnum<=5) OR
(lastlogindate < DATE_ADD(DATE(NOW()), INTERVAL -151 DAY) AND loginnum>5);
END$$
DELIMITER ;
#penjadwalan pembersihan
use radius;
CREATE EVENT `do_radacct_cleaning` ON SCHEDULE EVERY 24 HOUR STARTS '2023-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO call `radacct_cleaner`();
Aktifkan Radius Incoming agar dapat menerima permintaan untuk memutuskan koneksi

Buat mekanisme untuk menandai penghapusan di active karena melebihi batas login, sebagai antisipasi Randomise Mac
CREATE TABLE `radrandomisemacfactor` (
`username` varchar(64) NOT NULL,
`callingstationid` varchar(50) NOT NULL,
`lastlogindate` date DEFAULT NULL,
`loginnum` int(10) UNSIGNED DEFAULT '1',
PRIMARY KEY (`username`,`callingstationid`)
) DEFAULT CHARSET=latin1;
ALTER TABLE `radius`.`radrandomisemacfactor`
ADD INDEX `idx_lastlogindate` (`lastlogindate` ASC);
DELIMITER $$
USE `radius`$$
CREATE DEFINER=CURRENT_USER TRIGGER `radius`.`radrandomisemacfactor_BEFORE_INSERT` BEFORE INSERT ON `radrandomisemacfactor` FOR EACH ROW
BEGIN
SET NEW.lastlogindate = DATE(NOW());
END$$
CREATE DEFINER=CURRENT_USER TRIGGER `radius`.`radrandomisemacfactor_BEFORE_UPDATE` BEFORE UPDATE ON `radrandomisemacfactor` FOR EACH ROW
BEGIN
SET NEW.lastlogindate = DATE(NOW());
END$$
DELIMITER ;
USE `radius`;
DROP procedure IF EXISTS `mark_to_stop`;
DELIMITER $$
USE `radius`$$
CREATE PROCEDURE `mark_to_stop` (username_ VARCHAR(50), callingstationid_ VARCHAR(50))
BEGIN
DECLARE SimultaneousUse_ INT;
DECLARE ToStop_ INT;
SET SimultaneousUse_ = ifnull((SELECT value FROM radusergroup ug JOIN radgroupcheck gc ON gc.groupname=ug.groupname WHERE username=username_ AND attribute='Simultaneous-Use' LIMIT 1), 0);
#mengapa 3? karena spare login 2, yang 1 adalah yang baru saja login dan limit dimulai dari 0
set ToStop_ = SimultaneousUse_ - 3;
IF ToStop_ > 0 THEN
#tandai data di radacct yang melebihi batas untuk user
#yang dihapus adalah yang dicurigai sebagai randomise mac terlebih dahulu ifnull(loginnum, 0)>5, baru yang acctstarttime lebih awal
UPDATE radacct, (SELECT DISTINCT radacct.callingstationid FROM radacct LEFT JOIN radrandomisemacfactor on radrandomisemacfactor.username=radacct.username AND radrandomisemacfactor.callingstationid=radacct.callingstationid WHERE radacct.username=username_ AND radacct.callingstationid<>trim(callingstationid_) AND logmarker=0 AND isnull(acctstoptime) ORDER BY ifnull(loginnum, 0)>5, acctstarttime DESC LIMIT ToStop_, 100) tomark
SET radacct.logmarker=2 WHERE radacct.username=username_ AND radacct.callingstationid=tomark.callingstationid;
END IF;
END$$
DELIMITER ;
USE `radius`;
DROP procedure IF EXISTS `mark_to_stop_reset`;
DELIMITER $$
USE `radius`$$
CREATE PROCEDURE `mark_to_stop_reset` ()
BEGIN
UPDATE radacct SET logmarker=2 WHERE logmarker=3 AND ISNULL(acctstoptime);
END$$
DELIMITER ;
USE `radius`;
DROP procedure IF EXISTS `generate_pod`;
DELIMITER $$
USE `radius`$$
CREATE PROCEDURE `generate_pod`()
BEGIN
DECLARE POD_Stat_ TEXT;
DECLARE radacctid_ BIGINT;
DECLARE routerincomingport_ INT;
SET routerincomingport_ := 3799;
#format ini tidak berhasil
#SELECT radacctid, TRIM(CONCAT('echo "Acct-Session-Id=',acctsessionid,'">packet.txt; echo "User-Name=',username,'">>packet.txt; echo "NAS-IP-Address=',nasipaddress,'">>packet.txt; cat packet.txt | /usr/bin/radclient -r 1 ',nasipaddress,":",routerincomingport_," disconnect ",secret,"; rm packet.txt")) AS POD_Stat INTO radacctid_, POD_Stat_
#format ini berhasil
SELECT radacctid, CONCAT('echo User-Name=',username, ', Framed-IP-Address="', framedipaddress, '" | /usr/bin/radclient -r 1 ', nasipaddress, ':', routerincomingport_, ' disconnect ', secret) AS POD_Stat INTO radacctid_, POD_Stat_
FROM radacct a join nas n on n.nasname=nasipaddress WHERE logmarker=2 LIMIT 1;
UPDATE radacct SET logmarker=3 WHERE radacctid=radacctid_;
SELECT POD_Stat_;
END$$
DELIMITER ;
Ubah berkas konfigurasi SQL di server radius (mods-config/sql/main/mysql/queries.conf)
#simul_count_query = "\
# SELECT COUNT(*) \
# FROM ${acct_table1} \
# WHERE username = '%{SQL-User-Name}' \
# AND acctstoptime IS NULL"
simul_count_query = "\
SELECT COUNT(*) \
FROM (SELECT DISTINCT callingstationid FROM ${acct_table1} \
WHERE username = '%{SQL-User-Name}' and callingstationid<>'%{Calling-Station-Id}' \
AND acctstoptime IS NULL) mac_count"
#simul_verify_query = "\
# SELECT \
# radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, \
# callingstationid, framedprotocol \
# FROM ${acct_table1} \
# WHERE username = '%{SQL-User-Name}' \
# AND acctstoptime IS NULL"
simul_verify_query = "\
SELECT \
radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, \
callingstationid, framedprotocol \
FROM ${acct_table1} \
WHERE username = '%{SQL-User-Name}' and callingstationid<>'%{Calling-Station-Id}' \
AND acctstoptime IS NULL \
GROUP BY callingstationid \
ORDER BY if(calledstationid='%{Called-Station-Id}' AND nasportid='%{NAS-Port-ID}',1,0) DESC"
post-auth {
# Write SQL queries to a logfile. This is potentially useful for bulk inserts
# when used with the rlm_sql_null driver.
# logfile = ${logdir}/post-auth.sql
query = "\
INSERT INTO ${..postauth_table} \
(username, pass, callingstationid, reply, authdate ${..class.column_name}) \
VALUES ( \
'%{SQL-User-Name}', \
'%{%{User-Password}:-%{Chap-Password}}', \
'%{Calling-Station-Id}', \
'%{reply:Packet-Type}', \
'%S.%M' \
${..class.reply_xlat})"
}
Buat crontab setiap 1 menit di server radius agar server radius mengirimkan perintah kepada router untuk memutus koneksi yang melebihi batas (solusi untuk randomise Mac)
SHELL=/bin/bash
PATH=/bin:/usr/bin
*/1 * * * * POD_Stat="start"; /usr/bin/mysql -uuserbasisdataradius -ppasswordbasisdataradius -hhostbasisdataradius radius -sN -e "CALL mark_to_stop_reset();"; while [ "$POD_Stat" != "NULL" ]; do POD_Stat=$(/usr/bin/mysql -uuserbasisdataradius -ppasswordbasisdataradius -hhostbasisdataradius radius -sN -e "CALL generate_pod();"); if [[ "$POD_Stat" != "NULL" ]]; then eval $POD_Stat; fi; done
Gambaran hospot dan basis data akunting
Server Hotspot 1
+---------+------+--------------+-------------------+
| Server | User | Address | Mac Address |
+---------+------+--------------+-------------------+
| SrvHot1 | usr1 | 172.16.10.1 | AA:BB:CC:DD:EE:FF |
| SrvHot1 | usr1 | 172.16.15.31 | AA:BB:CC:00:11:22 |
+---------+------+--------------+-------------------+
Server Hotspot 2
+---------+------+--------------+-------------------+
| Server | User | Address | Mac Address |
+---------+------+--------------+-------------------+
| SrvHot2 | usr1 | 172.16.101.1 | AA:BB:CC:DD:EE:FF |
+---------+------+--------------+-------------------+
Akunting
+-----------+----------------+----------+-------------------+
| simul_cnt | registered_cnt | username | callingstationid |
+-----------+----------------+----------+-------------------+
| 2 | 2 | usr1 | AA:BB:CC:DD:EE:FF |
| 2 | 1 | usr1 | AA:BB:CC:00:11:22 |
+-----------+----------------+----------+-------------------+
User usr1 telah melakukan login menggunakan 1 HP (mac AA:BB:CC:DD:EE:FF) dan 1 Laptop (mac AA:BB:CC:00:11:22). Tidak lama kemudian, user usr1 pindah ke gedung lain dan login menggunakan HP (mac AA:BB:CC:DD:EE:FF) dan mendapatkan IP yang berbeda karena server hotspotnya juga berbeda.
Simultanous-use dihitung 2 karena mac yang digunakan hanya 2 setelah simul_count_query diubah. Namun, jika simul_count_query tidak diubah maka simul_count_query akan bernilai 3.
Setting Router Untuk Login Hotspot
Pada direktori HTML Directory di router, dibuat sebuah halaman login yang tertuju ke cek.php di server radius:
- Alternatif 1: form
- Alternatif 2: ajax

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>UNISA Yogyakarta</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=no">
<link href="asset/css/bootstrap.css" type="text/css" rel="stylesheet" />
<link href="asset/css/style.css" type="text/css" rel="stylesheet" />
<link rel="icon" type="image/png" href="asset/images/favicon-32x32.png" sizes="32x32">
<link rel="icon" type="image/png" href="asset/images/favicon-16x16.png" sizes="16x16">
</head>
<body>
<div class="container">
<div class="row">
<div class="card card-container">
<img class="profile-img-card img-responsive" src="asset/images/logo.png" />
<!-- pada action harus https -->
<form class="form-signin" name="login" id="login" method="post" action="https://radius.example.org/cek.php">
$(if error)<p class="error" align="center">$(error)</p>$(endif)
<div class="form-group">
<input type="hidden" name="dst" value="$(link-orig)" />
<input type="hidden" name="popup" value="true" />
<input type="text" name="username" id="username" class="form-control focus" placeholder="USERNAME"/>
</div>
<div class="form-group">
<input type="password" name="password" id="password" class="form-control" placeholder="PASSWORD"/>
</div>
<button class="btn btn-custom btn-block btn-signin" type="button" onclick="submit">login</button>
</form><!-- /form -->
</div>
</div>
<div class="row">
<center>
<p class="sign-text">© Powered by <a href="" target="_blank"></a></p>
</center>
</div>
</div>
</body>
<script src="asset/js/jquery-3.1.0.min.js"></script>
<script src="asset/js/bootstrap.min.js"></script>
<script type="text/javascript">
document.login.username.focus();
</script>
</html>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>UNISA Yogyakarta</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=no">
<link href="asset/css/bootstrap.css" type="text/css" rel="stylesheet" />
<link href="asset/css/style.css" type="text/css" rel="stylesheet" />
<link rel="icon" type="image/png" href="asset/images/favicon-32x32.png" sizes="32x32">
<link rel="icon" type="image/png" href="asset/images/favicon-16x16.png" sizes="16x16">
</head>
<body>
<div class="container">
<div class="row">
<div class="card card-container">
<img class="profile-img-card img-responsive" src="asset/images/logo.png" />
<form class="form-signin" name="login" id="login">
$(if error)<p class="error" align="center">$(error)</p>$(endif)
<div class="form-group">
<input type="hidden" name="dst" value="$(link-orig)" />
<input type="hidden" name="popup" value="true" />
<input type="text" name="username" id="username" class="form-control focus" placeholder="USERNAME"/>
</div>
<div class="form-group">
<input type="password" name="password" id="password" class="form-control" placeholder="PASSWORD"/>
</div>
<button class="btn btn-custom btn-block btn-signin" type="button" onclick="cek()">login</button>
</form><!-- /form -->
</div>
</div>
<div class="row">
<center>
<p class="sign-text">© Powered by <a href="" target="_blank"></a></p>
</center>
</div>
</div>
</body>
<script src="asset/js/jquery-3.1.0.min.js"></script>
<script src="asset/js/bootstrap.min.js"></script>
<script type="text/javascript">
document.login.username.focus();
</script>
<script>
function cek() {
//url harus https
$.ajax({
url: 'https://radius.example.org/cek.php',
type : 'post',
data: {username:$('input[name="username"]').val(), password:$('input[name="password"]').val(),via:'ajax'},
dataType : 'json',
success: function(result) {
$(location).attr('href', 'http://unisa.wifi/login?username='+$('input[name="username"]').val()+'&password='+result["password"]);
},
error: function(result){
return false;
}
});
}
</script>
</html>
Catatan:
Pada server radius.example.org harus disetting Access-Control-Allow-Origin seperti pada https://pdsi.unisayogya.ac.id/error-cors-options-dan-header-pada-jquery-javascript-ajax-dan-apache2/


Generate User
- database upass
#generate password dengan kunci enkripsi yang sama dengan yang digunakan di API
#generate 10 upass dengan expire 1 hari
CALL `databasetamu`.`generate_password`(10, "iniadalahkuncienkripsi", 1, NULL, NULL);
#generate 10 upass dengan expire 5 hari
CALL `databasetamu`.`generate_password`(10, "iniadalahkuncienkripsi", 5, NULL, NULL);
#lihat password untuk digunakan
CALL `databasetamu`.`get_data_password_tglgenerate`(date(now()), "iniadalahkuncienkripsi");
#+---------+------+-----------+-----------+--------------+-----+
#| id_tamu | hari | tgl_mulai | tgl_akhir | passwordasli | exp |
#+---------+------+-----------+-----------+--------------+-----+
#| 1 | 1 | NULL | NULL | 98c6f0ba | . |
#| 2 | 1 | NULL | NULL | 131c1a6f | . |
#| 3 | 1 | NULL | NULL | 0334da41 | . |
#| 4 | 1 | NULL | NULL | 4d2841f7 | . |
#| 5 | 1 | NULL | NULL | 6abe94b1 | . |
#| 6 | 1 | NULL | NULL | b2bd989c | . |
#| 7 | 1 | NULL | NULL | b9cdc56b | . |
#| 8 | 1 | NULL | NULL | fe92917c | . |
#| 9 | 1 | NULL | NULL | c3d6a8c7 | . |
#| 10 | 1 | NULL | NULL | 6fd395d6 | . |
#| 11 | 5 | NULL | NULL | 3defc389 | . |
#| 12 | 5 | NULL | NULL | 5038fa64 | . |
#| 13 | 5 | NULL | NULL | 74fb7dd3 | . |
#| 14 | 5 | NULL | NULL | 502acd8f | . |
#| 15 | 5 | NULL | NULL | 0a94467d | . |
#| 16 | 5 | NULL | NULL | 3a877665 | . |
#| 17 | 5 | NULL | NULL | 44b51eea | . |
#| 18 | 5 | NULL | NULL | be32faf3 | . |
#| 19 | 5 | NULL | NULL | 41fd6e15 | . |
#| 20 | 5 | NULL | NULL | ada6c878 | . |
#+---------+------+-----------+-----------+--------------+-----+
#10 rows in set (0.00 sec)
#Query OK, 0 rows affected (0.04 sec)
#lihat password yang tersimpan di tabel asli
SELECT id_tamu, password, hari FROM databasetamu.tamu;
#+---------+--------------------------+------+
#| id_tamu | password | hari |
#+---------+--------------------------+------+
#| 1 | ghttAzi/9P05tXarpBsHww== | 1 |
#| 2 | xHBlg6kfLS+0yTchmUZZ3A== | 1 |
#| 3 | mNTHx34SMVnKwqA2c1tJEg== | 1 |
#| 4 | 9h2JgpVC8j70RuCreiGarw== | 1 |
#| 5 | 5mpcPbAdTIGQGghoEGL+9A== | 1 |
#| 6 | 9oLmNVEYCVte4QGIT/ZDCw== | 1 |
#| 7 | Uz+pEAKAv4AlxWLO5P0d5Q== | 1 |
#| 8 | F7BaXYzhEVsWtio3VYNpSA== | 1 |
#| 9 | u7bbyP/FwLNrv8hl5Syf9A== | 1 |
#| 10 | ecVxvQeMXu3S7DDmxP6FmQ== | 1 |
#| 11 | Tgo+eZRxtRIi94Ixw+uASw== | 5 |
#| 12 | U1PIw7i2P/qcFbEAQvB+gA== | 5 |
#| 13 | IG/iLeG4yl7z2kE/BcWt+w== | 5 |
#| 14 | mxdVWAoCuQ7hkrPZmnrd4Q== | 5 |
#| 15 | FcVHu/ccldzpwoAQo4b+GQ== | 5 |
#| 16 | hWen8YmKvMCj1WA8Cg4U6g== | 5 |
#| 17 | Kp/DjVs8nvjp2PpAirfD+A== | 5 |
#| 18 | VqU2JMnR6NlrbmOk0/CYRg== | 5 |
#| 19 | RrG1cEcwFtGyEnP3254nfA== | 5 |
#| 20 | pklmWEp/fWDC7Qr3hn6QJw== | 5 |
#+---------+--------------------------+------+
#generate password dengan kunci enkripsi yang sama dengan yang digunakan di API
#generate 10 upass dengan expire 1 hari, dengan kode panggil kepanitiaanX
CALL `databasetamu`.`generate_password`(10, "iniadalahkuncienkripsi", 1, "kepanitiaanX", NULL);
#generate 10 upass dengan expire 1 hari dan maksimal digunakan tanggal 5 Januari 2023, dengan kode panggil kepanitiaanY
CALL `databasetamu`.`generate_password`(10, "iniadalahkuncienkripsi", 1, "kepanitiaanY", "2023-01-05");
#lihat password untuk digunakan, di mana password ini maksimal digunakan untuk login pada tanggal 5 Januari 2023
CALL `databasetamu`.`get_data_password_panggil`("kepanitiaanY", "iniadalahkuncienkripsi");
#+---------+------+-----------+------------+--------------+--------------------------------------+
#| id_tamu | hari | tgl_mulai | tgl_akhir | passwordasli | exp |
#+---------+------+-----------+------------+--------------+--------------------------------------+
#| 31 | 1 | NULL | 2023-01-05 | e56366c3 | , maksimal digunakan pada 2023-01-05 |
#| 32 | 1 | NULL | 2023-01-05 | f7a93168 | , maksimal digunakan pada 2023-01-05 |
#| 33 | 1 | NULL | 2023-01-05 | 05f943a7 | , maksimal digunakan pada 2023-01-05 |
#| 34 | 1 | NULL | 2023-01-05 | 83b49d80 | , maksimal digunakan pada 2023-01-05 |
#| 35 | 1 | NULL | 2023-01-05 | eaf6fa8a | , maksimal digunakan pada 2023-01-05 |
#| 36 | 1 | NULL | 2023-01-05 | 5fc3a39d | , maksimal digunakan pada 2023-01-05 |
#| 37 | 1 | NULL | 2023-01-05 | 82d62786 | , maksimal digunakan pada 2023-01-05 |
#| 38 | 1 | NULL | 2023-01-05 | ff8754ed | , maksimal digunakan pada 2023-01-05 |
#| 39 | 1 | NULL | 2023-01-05 | 9abc0e95 | , maksimal digunakan pada 2023-01-05 |
#| 40 | 1 | NULL | 2023-01-05 | f323b636 | , maksimal digunakan pada 2023-01-05 |
#+---------+------+-----------+------------+--------------+--------------------------------------+
#10 rows in set (0.00 sec)
#Query OK, 0 rows affected (0.04 sec)
upass yang sudah di-generate tersebut dapat dicetak untuk dapat dibagikan kepada tamu, baik melalui security, sekretaris, resepsionis dan melalui mekanisme lain; baik melalui sistem informasi maupun dapat menggunakan mail merge.

Demikian, semoga bermanfaat. [bst]
Sumber
- https://wiki.mikrotik.com/wiki/Category:Hotspot
- https://wiki.mikrotik.com/wiki/Manual:RADIUS_Client
- https://wiki.freeradius.org/config/Configuration-files
- https://wiki.freeradius.org/protocol/Disconnect%20Messages
- https://wiki.freeradius.org/guide/Data-Usage-Reporting
- https://github.com/lirantal/daloradius/issues/31
- https://forum.mikrotik.com/viewtopic.php?p=339124