[RADIATOR] Using SQL statements inside a PostAuthHook for COA
Thomas Kurian
thomas at kccg.com
Sun Jul 28 15:36:01 CDT 2013
Hello All,
I have a perl program which is called from my radiator config file as a
PostAuthhook . This below program is meant for COA, when user exceeds
his quota (he should be limited to speed2) and also for the monthly
reassigning of original subscribed speed to (the user is reassigned to
speed1) . I had a similar program which had worked earlier but it was
for only switching the user when he exceeds his quota.
Can you please check the following program and advice me on how to
modify this program to make it working for the above mentioned. I kindly
request your help get this program to function properly as I am new to
perl programs.
_PostAuthhook Program_
# /usr/bin/perl -w
use POSIX qw(strftime);
sub {
# OBJECT REF
my $p = ${$_[0]};
my $r = ${$_[1]};
# RETURN VOID
return unless ($p->code() eq 'Accounting-Request');
my $statustype = $p->get_attr('Acct-Status-Type');
return unless ($statustype eq 'Alive');
my $handler = $p->{Handler};
my $identifier = $handler->{thomas};
&main::log($main::LOG_DEBUG, "Running PostAuthHook: Using
Identifier
$identifier");
my $username =
$p->getAttrByNum($Radius::Radius::USER_NAME);
my $user_name = $p->get_attr('User-Name');
my $sess_id = $p->get_attr('Acct-Session-Id');
my $framed_ipaddress = $p->get_attr('Framed-IP-Address');
my $sess_handle = Radius::SessGeneric::find($identifier);
my $query1 = undef;
my $query2 = undef;
my $query3 = undef;
my $query4 = undef;
my $query21 = undef;
my $query22 = undef;
my $query23 = undef;
my $query24 = undef;
my $currentmonth = strftime("%m", localtime(time));
&main::log($main::LOG_DEBUG, "Running PostAuthHook sql query
check for :
$username");
$query1 = "select username from quotasubscribers where
switched = 0 and type = 'Q' and monthlycounter >= maxquota ";
my $sth = $sess_handle->prepareAndExecute($query1);
my @row = $sess_handle->getOneRow($sth);
$sth->finish;
my $db_user_name = $row[0];
&main::log($main::LOG_DEBUG, "Running PostAuthHook sql query
check for speed switched user");
$query21 = "select username from quotasubscribers
where switched = 1 and type = 'Q' ";
my $skh = $sess_handle->prepareAndExecute($query21);
my @rkw = $sess_handle->getOneRow($skh);
$skh->finish;
my $db_user = $rkw[0];
if ( $db_user_name eq $username )
{ &main::log($main::LOG_DEBUG, "Running PostAuthHook sql query check for
speed2 ,the speed assigned after user exceeds allocated qouta");
$query2 = "select speed2 from quotasubscribers where
switched = 0 and type = 'Q' and monthlycounter >= maxquota ";
my $tth = $sess_handle->prepareAndExecute($query2);
my @tow = $sess_handle->getOneRow($tth);
$tth->finish;
my $speed2 = $tow[0];
main::log($main::LOG_DEBUG, 'Update database field switched as true
value 1 ');
$query3 = "update quotasubscribers set switched = 1
where username = ? ";
my $kth =
$sess_handle->prepareAndExecute($query3,$user_name);
main::log($main::LOG_DEBUG, 'Starting COA execution ');
my @coa_attrs = ("User-Name=$user_name", "Acct-Session-Id=$sess_id",
"Framed-IP-Address=$framed_ipaddress", "cisco-Policy-Up=$speed2",
"cisco-Policy-Down=$speed2");
my @cmd_args = ("-noacct", "-noauth", "-time","-code",
"Change-Filter-Request");
push @cmd_args, ("-trace", "4", "-bind_address", "0.0.0.0",
"-auth_port", "3799", "-secret", "ciscowimd85", "-s", "10.50.1.4");
my @cmd = ("radpwtst");
main::log($main::LOG_DEBUG, "Running command: @cmd @cmd_args
@coa_attrs");
system (@cmd, @cmd_args, @coa_attrs);
&main::log($main::LOG_DEBUG, "Successful COA : The user
$db_user_name has exceeded allocated quota and is switched to $speed2");
main::log($main::LOG_DEBUG, 'Update database field resetmonth value
to current month value $currentmonth ');
$query4 = "update quotasubscribers set resetmonth = ?
where username = ? ";
my $ktk =
$sess_handle->prepareAndExecute($query4,$currentmonth,$db_user_name);
}
else if ( $db_user eq $username )
{
$query22 = "select resetmonth from quotasubscribers
where username = ? ";
my $xxx =
$sess_handle->prepareAndExecute($query22,$db_user);
my @xxk = $sess_handle->getOneRow($xxk);
$xxx->finish;
my $resetmonth = $xxk[0];
while ( $resetmonth ne $currentmonth )
{
main::log($main::LOG_DEBUG, 'Query for speed1 original speed');
$query23 = "select speed1 from quotasubscribers where
switched = 1 and type = 'Q' and monthlycounter = 0 ";
my $ttk = $sess_handle->prepareAndExecute($query23);
my @tok = $sess_handle->getOneRow($ttk);
$ttk->finish;
my $speed1 = $tok[0];
main::log($main::LOG_DEBUG, 'Starting reverse COA execution
engineering');
my @doa_attrs = ("User-Name=$user_name",
"Acct-Session-Id=$sess_id", "Framed-IP-Address=$framed_ipaddress",
"cisco-Policy-Up=$speed1", "cisco-Policy-Down=$speed1");
my @dmd_args = ("-noacct", "-noauth", "-time","-code",
"Change-Filter-Request");
push @dmd_args, ("-trace", "4", "-bind_address", "0.0.0.0",
"-auth_port", "3799", "-secret", "ciscowimd85", "-s", "10.50.1.4");
my @dmd = ("radpwtst");
main::log($main::LOG_DEBUG, "Running command: @dmd @dmd_args
@doa_attrs");
system (@dmd, @dmd_args, @doa_attrs);
&main::log($main::LOG_DEBUG, "Successful Reverse COA : The user
$db_user has been switched back to original speed $speed1");
&main::log($main::LOG_DEBUG, 'Resetting database field switched to
false value 0 ');
$query24 = "update quotasubscribers set switched = 0
where username = ? ";
my $ztk =
$sess_handle->prepareAndExecute($query24,$db_user);
}
}
else
{
&main::log($main::LOG_DEBUG, "The quota based user $username is not
eligible for any speed switch ");
}
}
--
Thanks & Best Regards,
Thomas Kurian
IT Security Engineer
Kuwaiti Canadian Consulting Group (www.kccg.com)
T: +965 22435566
F: +965 22415149
E: thomas at kccg.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.open.com.au/pipermail/radiator/attachments/20130728/6086988a/attachment.html
More information about the radiator
mailing list