Kusprayitna Blogs

Hidup untuk berbagi ilmu :: IT Linux Windows Database Oracle PHP OpenSource

Integrasi manajemen akses hotspot dengan radius

Posted by kusprayitna on August 2nd, 2010

Sudah lama dokumen ini tercatat di notebook dan belum sempat ditulis ulang di blogs.

Alhamdulillah kemarin ada yang tanya dan saya jadi ingat untuk menulisnya.

Tahapan terakhir dalam instalasi hotspot dengan mikrotik yaitu integrasi manajeman hotspot dengan database radius, dimana database radius sudah dibahas di topik sebelumnya.

Tahapan yang dilakukan yaitu :

  1. Pastikan user hotspot sudah bisa otentifikasi dengan LDAP server maupun MySQL Server bahkan jika diperlukan berdasarkan file. (lihat di setup-freeradius-agar-bisa-otentifikasi-dengan-ldap)
  2. Arahkan output radius berdasarkan setup yang sudah dibuat yaitu di MySQL Server, yaitu dengan cara edit file '/etc/raddb/users'

    DEFAULT Auth-Type := LDAP
    .....Exec-Program-Wait = "/etc/raddb/uiiauth %{User-Name} %{Calling-Station-Id}"

    dari sebelumnya

    DEFAULT Auth-Type := LDAP
    .....Fall-Through = 1

  3. Pastikan bahwa sebelum Exec-Program-Wait diatas adalah 1 buah karakter TAB bukan spasi
  4. Buat script /etc/raddb/uiiauth (saya pilih perl) yang memiliki fitur sebagai berikut :
    - Input ada 2 parameter, yaitu user-name dan mac-address
    - output adalah parameter-parameter yang dikirim dan dimengerti radius

Fungsi yang dibuat yaitu :

===========================================================================

Script sebagian di Edit untuk kerahasiaan data.

Keterangan script yaitu :

  1. Data user ada di LDAP (untuk civitas akademik UII dan di MySQL untuk tamu)
  2. Data di LDAP di kelompokkan berdasar fakultas/prodi berdasarkan data di GidNumber
  3. Periode dibuat per 6 bulan
  4. Setiap user mempunyai profile berdasarkan fakultas/prodi
  5. Profile setiap fakultas/prodi setiap periode dapat berbeda
  6. Profile antara mahasiswa dan staff dibedakan

===========================================================================

#!/usr/bin/perl
# Otentifikasi tambahan untuk radius
#

$dbname = "dbi:mysql:radius_db:localhost:3306"; # radius database name
$dbuser = "radius";             # name of radius user
$dbpsw = "radius";              # password of radius user

$ldaphost = "127.0.0.1";
$ldapbind = "cn=Manager,dc=uii,dc=ac,dc=id";
$ldappsw  = "PasswordLDAP";
$ldapgroup = "gidNumber";
$ldapbase = "dc=uii,dc=ac,dc=id";
$LDAP = 0;

use DBI;
use Time::Local;
use Net::LDAP;

CekArgumen();
openDB();
GetCurrentPeriod();
GetTglPeriod();
GetUserGroup();
GetUserSuspend();
if ( $LDAP eq 1 )
   {
        GetUserProfileLDAP();
   }
if ( $LDAP eq 0 )
   {
        GetUserProfileMySQL();
   }
GetProfileData();
GetVoucherDataBW();
GetVoucherDataHours();
GetVoucherDataDate();
GetAccountingData();
closeDB();
SetReplay();

#----------------------------------
# Fungsi fungsi
#----------------------------------
sub openDB
{
   $dbh = DBI->connect($dbname, $dbuser, $dbpsw)
      or die("Couldn't connect");
}

sub closeDB
{
   $dbh->disconnect;
}
sub CekArgumen
{
    my $argc = $#ARGV + 1;

    if ($argc == 0)
    {
       print "uiiauth [mac]\n";
       print "uiiauth [username] [mac]\n";
       exit 1;
    }
    $uname = $ARGV[0];
    $debug = $ARGV[2];
}

sub Debug
{
         if ($debug) {print "$_[0]\n";}
}

sub GetCurrentPeriod
{
    my $cursor = $dbh->prepare(" SELECT periodid from radcurrent limit 1")
                or die("Couldn't prepare");
    $cursor->execute;
        @row = $cursor->fetchrow;
        $periodid = @row[0];
    $cursor->finish;
    Debug("Periode $periodid");
}

sub GetTglPeriod
{
    my $cursor = $dbh->prepare("SELECT periodstart, periodend from radperiod where periodid ='$periodid' limit 1")
                or die("Couldn't prepare");
    $cursor->execute;
        @row = $cursor->fetchrow;
        $periodstart = @row[0];
        $periodend   = @row[1];
    $cursor->finish;
    Debug("Periode Start $periodstart \nPeriode End $periodend");
}

sub GetUserGroup
{
   # tentukan user ini terdaftar di MySQL atau LDAP
   my $cursor = $dbh->prepare("SELECT GroupName from usergroup where UserName='$uname' limit 1")
                or die("Couldn't prepare");
   $cursor->execute;
        if ($cursor->rows == 1)
        {
                @row = $cursor->fetchrow;
                $ugroup = $row[0];
        }
   $cursor->finish;

   #jika tidak ada di MySQL, ambil dari LDAP
   if ($ugroup)
   {
        $LDAP = 0;
        Debug("MySQL User");
   }
   else
   {
        Debug("LDAP User");
       $LDAP = 1 ;
        # cari group user dari LDAP server
        # misal BSI = 1123  --> 1 awalan staff (4:mahasiswa, 5:alumni) 1 rektorat 2 badan 3 bsi
        my $ldap = Net::LDAP->new($ldaphost);
        $ldap->bind($ldapbind, password=>$ldappsw);
        my $mesg = $ldap->search(filter=>"(uid=$uname)", base=>$ldapbase);
        @entries = $mesg->entries;

        foreach $entry (@entries) {
                $ugroup = $entry->get_value($ldapgroup);
                }
   }
    Debug("User group $ugroup");
}

sub GetUserSuspend
{
   # mencatat database user yang disuspend
   my $cursor = $dbh->prepare("SELECT username, alasan, selesai from radsuspend where username='$uname' and CURDATE()+CURTIME()")
                or die("Coludn'y prepare");
   $cursor->execute;
        if ( $cursor->rows >= 1 )
        {
                @row = $cursor->fetchrow;
                print "Reply-Message=\"Account suspended, sebab $row[1]\"";
                exit 1;
        }
   $cursor->finish;
}

#-------
# fungsi untuk cari profile berdasarkan periode
#-------

sub GetProfileByPeriodeGroup
{

    my $cursor = $dbh->prepare("SELECT profid from radgroupperiode where periodid='$periodid' and groupid='$_[0]' ")
                or die("Couldn't prepare");
    $cursor->execute;
        @row = $cursor->fetchrow;
        my $g = @row[0];
    return $g;
    $cursor->finish;
}

#-------
# fungsi untuk cari profile berdasarkan group
#-------

sub GetProfileByGroup
{
    #print "Get data profile group $_[0]";
    my $cursor = $dbh->prepare("SELECT profid from radgroup where groupid='$_[0]' ")
                or die("Couldn't prepare");
    $cursor->execute;
        @row = $cursor->fetchrow;
        my $g = @row[0];
    return $g;
    $cursor->finish;
}
#-------
# fungsi untuk mencari profilenya
#-------

sub GetUserProfileLDAP
{
    # cari user profile berdasar groupnya
    my $group1 = substr ($ugroup,1,1) . "00";
    my $group2 = substr ($ugroup,1,2) . "0";
    my $group3 = substr ($ugroup,1,3);

    # cari profile berdasar group dan periode
    $uprofile = GetProfileByPeriodeGroup($group3);
    if ($uprofile eq "")
    {
      $uprofile = GetProfileByPeriodeGroup($group2);
      if ($uprofile eq "")
      {
        $uprofile = GetProfileByPeriodeGroup($group1);
        if ($uprofile eq "")
        {
        #cek default profile saja
            $uprofile = GetProfileByGroup($group3);
            if ($uprofile eq "")
            {
              $uprofile = GetProfileByGroup($group2);
              if ($uprofile eq "")
              {
                $uprofile = GetProfileByGroup($group1);
                if ($uprofile eq "")
                {
                  if (length($uname) == 8) { $uprofile = 'default siswa';}
                  elsif (length($uname) == 9) { $uprofile = 'default';}
                  else
                  {
                      print "Reply-Message=\"Group anda tidak diketahui\"";
                      exit 1;
                 }
               i}
              }
            }
        }
      }
    }

    # jika mahasiswa, maka mendapatkan bandwidth mahasiswa
    if ( substr($ugroup,0,1 ) == 4 ) {
        $uprofile = $uprofile . " siswa";
    }
    if (  substr($ugroup,0,1 ) == 5  ) {
        print "Reply-Message=\"Your account has expired\"";
        exit 1;
    }
    Debug("Profile $uprofile");

}

sub GetUserProfileMySQL
{
   # profile mysql sama dengan nama group
   $uprofile = $ugroup;
}

#-------
# fungsi untuk mengambil data profile
#-------

sub GetProfileData
{
    my $cursor = $dbh->prepare("SELECT downrate, uprate, limittraffic, uplimit,downlimit, limitexpiration, expiration,
                         limituptime, uptime , poolname
                         FROM radprofile
                         WHERE profid='$uprofile'")
                 or die("Couldn't prepare");
    $cursor->execute;
    if ($cursor->rows == 1)
    {
        @row = $cursor->fetchrow;
        $profdownrate        = $row[0];
        $profuprate          = $row[1];
        $proflimittraffic    = $row[2];
        $uplimit             = $row[3];
        $downlimit           = $row[4];
        $proflimitexpiration = $row[5];
        $expiration          = $row[6];
        $proflimituptime     = $row[7];
        $uptimelimit         = $row[8];
        $poolname            = $row[9];
    }
    else
    {
        print "Reply-Message=\"Profile data not found\"";
        exit 1;
    }
    $cursor->finish;

   Debug("Data rate $profdownrate / $profuprate quota $downlimit / $uplimit expiration $proflimitexpiration $expiration");
}

#------------------
# Voucher untuk menambah bandwidth rate dan quota bandwidth saja
#------------------

sub GetVoucherDataBW
{
   # voucher bersifat menambah paket yang semestinya di dapat
   my $cursor = $dbh->prepare("SELECT bytesdown, bytesup,downlimit, uplimit
                         FROM radpayment
                         WHERE periodid = '$periodid' and username='$uname' and hours=0 and expiration ='0000-00-00 00:00:00'
                         and validated='Y'")
                or die("Couldn't prepare");
   $cursor->execute;
   my   $jml = $cursor->rows;
        if ( $jml >= 1 )
        {
            while (@row = $cursor->fetchrow)
            {
                $profdownrate   = $profdownrate + $row[0];
                $profuprate     = $profuprate   + $row[1];
                $uplimit        = $uplimit      + $row[3];
                $downlimit      = $downlimit    + $row[2];
                Debug("Voucher BW: Periode $periodid Uplimit $row[3] -> $uplimit  Downlimit $row[2] -> $downlimit");
           }
        }
        else {Debug("No Voucher bandwidth");}
   $cursor->finish;
}

#------------------------
# Voucher untuk menambah jam akses, jika profilenya dibatasi jam aksesnya
#------------------------

sub GetVoucherDataHours
{
   # voucher bersifat menambah paket yang semestinya di dapat
   my $cursor = $dbh->prepare("SELECT bytesdown, bytesup,downlimit, uplimit, hours
                         FROM radpayment
                         WHERE periodid = '$periodid' and username='$uname' and hours > 0 and expiration = '0000-00-00 00:00:00'
                         and validated='Y'")
                or die("Couldn't prepare");
   $cursor->execute;
   my   $jml = $cursor->rows;
        if ( $jml >= 1 )
        {
            while (@row = $cursor->fetchrow)
            {
                $profdownrate   = $profdownrate + $row[0];
                $profuprate     = $profuprate   + $row[1];
                $uplimit        = $uplimit      + $row[3];
                $downlimit      = $downlimit    + $row[2];
                $uptimelimit    = $uptimelimit  + ($row[4]*60*60); # jam diubah dalam detik
                $proflimituptime = 1;
                Debug("Voucher jam: Periode $periodid Uplimit $row[3] -> $uplimit  Downlimit $row[2] -> $downlimit jam $row[4] -> $uptimelimit ");
           }
        }
        else {Debug("No Voucher jam");}
   $cursor->finish;
}
#------------------------
# Voucher untuk menambah tanggal ijin akses, jika profilenya dibatasi tanggal akses
#------------------------

sub GetVoucherDataDate
{
   # voucher bersifat menambah paket yang semestinya di dapat
   my $cursor = $dbh->prepare("SELECT bytesdown, bytesup,downlimit, uplimit, expiration
                         FROM radpayment
                         WHERE periodid = '$periodid' and username='$uname' and hours = 0 and expiration <> '0000-00-00 00:00:00'
                         and validated ='Y'
                         order by expiration")
                or die("Couldn't prepare");
   $cursor->execute;
   my   $jml = $cursor->rows;
        if ( $jml >= 1 )
        {
            while (@row = $cursor->fetchrow)
            {
                $profdownrate   = $profdownrate + $row[0];
                $profuprate     = $profuprate   + $row[1];
                $uplimit        = $uplimit      + $row[3];
                $downlimit      = $downlimit    + $row[2];
                $expiration     = $row[4];
                $proflimitexpiration = 1;
                Debug("Voucher tanggal: Periode $periodid Uplimit $row[3] -> $uplimit  Downlimit $row[2] -> $downlimit");
           }
        }
        else {Debug("No Voucher tanggal");}
   $cursor->finish;
}

sub GetAccountingData()
{
   my $cursor = $dbh->prepare("SELECT SUM(AcctInputOctets), SUM(AcctOutputOctets), SUM(AcctSessionTime)
                         FROM radacct
                         WHERE
                                (AcctStartTime >= '$periodstart' and AcctStopTime <= '$periodend')
                                and username='$uname'")
                or die("Couldn't prepare");
   $cursor->execute;
        @row = $cursor->fetchrow;
        $mt_recv_limit = $uplimit - $row[0];
        $mt_xmit_limit = $downlimit - $row[1];
        $acctsesstime  = $uptimelimit - $row[2];
        Debug("Accounting data down $downlimit - $row[1] -> $mt_xmit_limit   up $uplimit - $row[0] -> $mt_recv_limit time $uptimelimit - $row[2] -> $acctsesstime  ");
   $cursor->finish;
}

sub SetReplay
{

   $mt_timeout = 0;

   # check expiration limit

   if ($proflimitexpiration == 1)
   {
        $year = substr $expiration, 0, 4;
        $month = substr $expiration, 5, 2;
        $day = substr $expiration, 8, 2;
        $expiresecs = timelocal(0, 0, 0, $day, $month-1, $year);
        $mt_timeout = $expiresecs - time;

        # check for session timeout limit

        if ($mt_timeout <= 0)
        {
                print "Reply-Message=\"Your account has expired\"";
                exit 1;
        }
        else
        {
                print "Session-Timeout=" . $mt_timeout . ", ";
        }
   }

   # check uptime limit

   if ($proflimituptime == 1)
   {
        $mt_timeout = $acctsesstime;
        if ($mt_timeout <= 0)
        {
                print "Reply-Message=\"You have no more online time left\"";
                exit 1;
        }
        else
        {
                print "Session-Timeout=" . $mt_timeout . ", ";
        }
   }

   # check traffic limits

   if ($proflimittraffic == 1)
   {
        # check download limit
        if ($mt_xmit_limit <= 0)
        {
                print "Reply-Message=\"Download limit reached\"";
                exit 1;
        }
        else
        {
                print "Mikrotik-Xmit-Limit=" . $mt_xmit_limit . ", ";
        }
        # check upload limit
        if ($mt_recv_limit <= 0)
        {
                print "Reply-Message=\"Upload limit reached\"";
                exit 1;
        }
        else
        {
                print "Mikrotik-Recv-Limit=" . $mt_recv_limit . ", ";
        }
   }

   # set datarate

   print "Ascend-Data-Rate=" . $profuprate . ", ";
   print "Ascend-Xmit-Rate=" . $profdownrate . ", ";

   # select dhcp pool

   if ($poolname)
   {
        print "Framed-Pool=" . $poolname . ", ";
   }

   exit 0;
}

Contoh jika scrip dijalankan yaitu :

# ./uiiauth 011002xxx   ==> 1 argumen

Mikrotik-Xmit-Limit=3472594842, Mikrotik-Recv-Limit=1733399421, Ascend-Data-Rate=40960, Ascend-Xmit-Rate=90112,

# ./uiiauth 011002xxx 1   ==> 2 argumen (dengan mac)

Mikrotik-Xmit-Limit=3472594842, Mikrotik-Recv-Limit=1733399421, Ascend-Data-Rate=40960, Ascend-Xmit-Rate=90112,

# ./uiiauth 011002xxx 1 1  ==> 3 argumen (debug)

Periode 20092
Periode Start 2009-08-01 00:00:00
Periode End 2010-07-31 23:59:59
LDAP User
User group
Profile default
Data rate 90112 / 40960 quota 5368709120 / 1073741824 expiration 0 0000-00-00 00:00:00
Voucher BW: Periode 20092 Uplimit 1073741824 -> 2147483648  Downlimit 1073741824 -> 6442450944
No Voucher jam
No Voucher tanggal
Accounting data down 6442450944 - 2969856102 -> 3472594842   up 2147483648 - 414084227 -> 1733399421 time 0 - 1697285 -> -1697285
Mikrotik-Xmit-Limit=3472594842, Mikrotik-Recv-Limit=1733399421, Ascend-Data-Rate=40960, Ascend-Xmit-Rate=90112,

Demikian , semoga bermanfaat

2 Responses to “Integrasi manajemen akses hotspot dengan radius”

  1. Membuat jaringan hotspot dengan Mikrotik | Kusprayitna Blogs Says:

    [...] Integrasi manajemen akses hotspot dengan radius | Kusprayitna Blogs on Setup freeradius agar bisa otentifikasi dengan LDAP dhika6046 on MUI Akhirnya Keluarkan Fatwa [...]

  2. Hakeems Says:

    Muantaapppp banget mas... Smoga ilmu yg diberikan menjadi berkah y mas..

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>