Categories
Tutorial, Manual, Tips dan Trik

Hotspot Dengan Waktu Koneksi Internet Yang Bervariasi Untuk Tamu

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
https://pdsi.unisayogya.ac.id/hotspot-dengan-waktu-koneksi-internet-yang-bervariasi-untuk-tamu/ 2023-01-03 16:40:37

Ide

mekanisme login hotspot
  • 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
Alur login Hotspot-API

Timeout

TimeoutFungsi
keepalive-timeoutberapa lama waktu tunggu untuk perangkat terhubung kembali secara fisik
idle-timeoutberapa lama waktu tunggu untuk perangkat kembali mengirimkan paket
session-timeoutberapa lama waktu yang diberikan untuk pengguna
leaseberapa lama waktu yang dibutuhkan oleh DHCP untuk melepaskan alamat dinamis dan bisa diperbaharui di tengah-tengah waktu
Perbedaan timeout

Membuat Tabel dan Mekanisme Penyimpanan/Pengambilan Password

#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 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

Freeradius menimpa profil di router

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 Hotspot

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."; 
Menghapus Lease DHCP Saat User Logout

#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

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

Mengaktifkan Radius Incoming

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:

Halaman login disimpan pada direktori HTML Directory
<!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">&copy; 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">&copy; 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

#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.

mail merge upass hotspot

Demikian, semoga bermanfaat. [bst]

Sumber

By basit

Biro Pengembangan Teknologi Dan Sistem Informasi

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.