[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