[RADIATOR] Using SQL statements inside a PostAuthHook for COA
Thomas Kurian
thomas at kccg.com
Thu Apr 4 14:35:51 CDT 2013
Dear Friends,
I am modifed the script we were discussing, to perform COA and assign
new bandwidth (speed2) ,to the user who exceeds allocated quota
(maxquota) . I have already tested COA manually via radpwtst command, it
was successful and was acknowledged by the NAS. I want the below perl
script to perform COA ,when certain conditions are met. I have the
following question, for which i would really appreciate your guidance
and advice.
1. Can you check if the COA part in the below script is configured the
right way ,advice me if there is anything extra that needs to be added .
2. I also require to get ($Radius::Radius::? dictionary definitions
of) Acct-Session-Id and Framed-IP-Address from the Accounting packet
just like how it is done for User-Name (my
$username=$p->getAttrByNum($Radius::Radius::USER_NAME); please
provide me with the similar script line for Acct-Session-Id and
Framed-IP-Address like
my $acctsessionid= $p->getAttrByNum($Radius::Radius::___________)
and my $framedipaddress =
$p->getAttrByNum($Radius::Radius::____________).
3. Please check if the below hook file as a whole and kindly see &
advice if it meets the requirements for the COA.
Script
-------------------------------------------------------------------------
#! /usr/bin/perl -w
use strict;
use warnings;
use diagnostics;
sub {
# OBJECT REF
my $p = ${$_[0]};
my $r = ${$_[1]};
# RETURN VOID
return unless ($p->code() eq 'Accounting-Request')
&& (${$_[2]} == $main::ACCEPT);
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 $sess_handle = Radius::SessGeneric::find($identifier);
my $query = undef;
&main::log($main::LOG_DEBUG, "Running PostAuthHook sql query
check for :
$username");
$query = "select username from quotasubscribers where
switched = 0 and type = 'Q' and monthlycounter >= maxquota ";
my $sth = $sess_handle->prepareAndExecute($query);
my @row = $sess_handle->getOneRow($sth);
$sth->finish;
my $db_user_name = $row[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");
$query = "select speed2 from quotasubscribers where
switched = 0 and type = 'Q' and monthlycounter >= maxquota ";
my $sth = $sess_handle->prepareAndExecute($query);
my @row = $sess_handle->getOneRow($sth);
$sth->finish;
my $speed2 = $row[0];
main::log($main::LOG_DEBUG, 'Starting COA execution '); \
my $user_name = $p->get_attr('User-Name'); \
my $sess_id = $p->get_attr('Acct-Session-Id'); \ # ########This is
where i require the Radius::Radius::_____ definition
my $framed_ipaddress = $p->get_attr('Framed-IP-Address'); \ #
######## This is where i require the Radius::Radius::_____ definition
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);\
push @cmd_args, ("-trace", "4", "-bind_address", "0.0.0.0",
"-auth_port", "3799", "-secret", "XXXXX", "-s", "10.20.1.25"); \
my @cmd = ("perl", "radpwtst"); \
main::log($main::LOG_DEBUG, "Running command: @cmd @cmd_args
@coa_attrs"); \
system (@cmd, @cmd_args, @coa_attrs); \
&main::log($main::LOG_DEBUG, "The user $db_user_name has exceeded
allocated quota and is been limited to $speed2");
}else
&main::log($main::LOG_DEBUG, "The user $username either has not yet
exceeded allocated quota or isnt a quota based user");
}
-------------------------------------------------------------------
Requesting your kind help & coooperation,
Thomas Kurian
IT Security Engineer (B.Tech. – Electrical)
Kuwaiti Canadian Consulting Group (www.kccg.com)
T: +965 22435566
F: +965 22415149
E:thomas at kccg.com
On 3/30/2013 11:37 AM, Heikki Vatiainen wrote:
> On 03/29/2013 07:29 PM, Thomas Kurian wrote:
>
>> I appended the additional config lines you sent me in your reply. But i
>> still did not get the required result. I have included the error logged
>> in the log file. Please advice me on how to resolve this error based on
>> the updated script.
> Here $self is undefined. See the line where you have $self=undef;
> my @row = $self->getOneRow($sth);
>
> you could try this:
> my @row = $sess_handle->getOneRow($sth);
>
>> I also commented out some config lines in the script file, can you
>> please check if these are just unwanted statements which is not required
>> for my purpose with the hook.
> I see a couple of lines related to generating random numbers. All I can
> say is they seem not to be used by your script.
>
>> I just want to check if the user crossed his allocated quota , if yes
>> then i need to call the web link containing username.
>>
>> Also i could not find any prepared statements or quote() to get
>> User-Name from a SQL query in the sqlDb.pm file . Can you please point
>> out these prepared statements from the file.
> Instead of doing this:
>
> my $username = "whatever";
> my $query = "select something from users where username='$username'";
> prepareAndExecute($query);
>
> You can keep the $query constant and call it with the variables like this:
>
> my $username = "whatever";
> my @bind_variables;
> push @bind_variables, $username;
> my $query = "select something from users where username=?";
> prepareAndExecute($query, @bind_variables);
>
> In this way the query can be processed only once by the SQL layer and
> executed multiple times with the different contents of bind_variables.
>
> See section "5.4 Bind Variables" in the reference manual for more.
>
>
>> Also tell me the syntax for adding additional log() calls._
> You already call main::log once. Just add more of these to keep track
> what your hook is doing. That will help with development.
>
> Thanks,
> Heikki
>
>
>> error log_
>> Thu Mar 28 09:36:40 2013: DEBUG: AuthBy SQL result: ACCEPT,
>> Thu Mar 28 09:36:40 2013: DEBUG: Running PostAuthHook: Using Identifier
>>
>> Thu Mar 28 09:36:40 2013: DEBUG: Query to 'dbi:ODBC:IRONMAN': 'select
>> username from quotasubscribers where switched = 0 and type = 'Q' and
>> monthlycounter >= maxquota '$
>> Thu Mar 28 09:36:40 2013: ERR: Error in PostAuthHook(): Can't call
>> method "getOneRow" on an undefined value at (eval 53) line 50.
>> _
>> __Updated Script_
>> #! /usr/bin/perl -w
>> use strict;
>> use warnings;
>> use diagnostics;
>> use URI::Escape ('uri_escape');
>> require LWP::UserAgent;
>> sub {
>>
>> # OBJECT REF
>>
>> my $p = ${$_[0]};
>> my $r = ${$_[1]};
>>
>> # RETURN VOID
>>
>> return unless ($p->code() eq 'Accounting-Request')
>> && (${$_[2]} == $main::ACCEPT);
>>
>> 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 $sess_handle = Radius::SessGeneric::find($identifier);
>>
>> my $query = undef;
>> my $self = undef;
>>
>> #my $rt = $p->{RecvTime};
>>
>> # my $lower = 1000;
>>
>> # my $upper = 2000000;
>>
>> # my $random = int(rand( $upper-$lower+1 ) ) + $lower;
>>
>> #my $reallyrandom = $random.$rt;
>>
>>
>> $query = "select username from quotasubscribers where
>> switched = 0 and type = 'Q' and monthlycounter >= maxquota ";
>> my $sth = $sess_handle->prepareAndExecute($query);
>> my @row = $self->getOneRow($sth);
>> $sth->finish;
>> my $db_user_name = $row[0];
>>
>> my $ua = LWP::UserAgent->new;
>> if ( $db_user_name eq $username )
>> {my $response = $ua->get('http://94.187.187.8:880/changespeed.aspx?uname=' .
>> uri_escape($username) .
>> '&pwd=peter'
>> );
>> if ($response->is_success)
>> {
>> print $response->content; # or whatever
>> }
>> else
>> {
>> die $response->status_line;
>> }
>> }
>>
>> }
>>
>>
>> --
>> Requesting your kind help & cooperation ,
>>
>> Thomas Kurian
>> IT Security Engineer (B.Tech. – Electrical)
>> 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/20130404/ae485776/attachment.html
More information about the radiator
mailing list