help need with mysql statement in freeradius
Bishal
bishal at baayu.com.np
Fri May 16 05:19:27 CEST 2008
debug output of the radius
Module: Instantiated detail (reply_log)
Listening on authentication *:1645
Listening on accounting *:1646
Ready to process requests.
rad_recv: Access-Request packet from host 202.xx.xx.xx:52743, id=81,
length=151
NAS-Identifier = "pppoe-test.lumbininet.com.np"
NAS-Port = 12
NAS-Port-Type = Ethernet
Service-Type = Framed-User
Framed-Protocol = PPP
Calling-Station-Id = "001a4daf4ead"
Called-Station-Id = "WIFITEST"
User-Name = "mobile"
CHAP-Password = 0x0102e814e5d756effb7319a534e354dcd2
CHAP-Challenge =
0xbb1e687616119cbcd0156169c9b45cb65bd4ce0daf99b5788e
Processing the authorize section of radiusd.conf
modcall: entering group authorize for request 0
modcall[authorize]: module "preprocess" returns ok for request 0
radius_xlat: '/var/log/radacct/202.xx.xx.xx/auth-detail-20080516'
rlm_detail: /var/log/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d
expands to /var/log/radacct/202.xx.xx.xx/auth-detail-20080516
modcall[authorize]: module "auth_log" returns ok for request 0
rlm_chap: Setting 'Auth-Type := CHAP'
modcall[authorize]: module "chap" returns ok for request 0
rlm_realm: No '@' in User-Name = "mobile", looking up realm NULL
rlm_realm: No such realm "NULL"
modcall[authorize]: module "suffix" returns noop for request 0
modcall[authorize]: module "files" returns notfound for request 0
radius_xlat: 'mobile'
rlm_sql (sql): sql_set_user escaped user --> 'mobile'
radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM
radcheck WHERE Username = 'mobile' ORDER BY id'
rlm_sql (sql): Reserving sql socket id: 28
radius_xlat: 'SELECT
radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op
FROM radgroupcheck,usergroup WHERE usergroup.Username = 'mobile' AND
usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id'
radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM
radreply WHERE Username = 'mobile' ORDER BY id'
radius_xlat: 'SELECT
radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op
FROM radgroupreply,usergroup WHERE usergroup.Username = 'mobile' AND
usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id'
rlm_sql (sql): Released sql socket id: 28
modcall[authorize]: module "sql" returns ok for request 0
rlm_checkval: Item Name: Calling-Station-Id, Value: 001a4daf4ead
rlm_checkval: Could not find attribute named Calling-Station-Id in check
pairs
modcall[authorize]: module "checkval" returns notfound for request 0
rlm_sqlcounter: Entering module authorize code
rlm_sqlcounter: Could not find Check item value pair
modcall[authorize]: module "dailycounter" returns noop for request 0
rlm_sqlcounter: Entering module authorize code
rlm_sqlcounter: Could not find Check item value pair
modcall[authorize]: module "monthlycounter" returns noop for request 0
rlm_sqlcounter: Entering module authorize code
sqlcounter_expand: 'SELECT SUM(AcctSessionTime) FROM radacct WHERE
UserName='%{User-Name}''
radius_xlat: 'SELECT SUM(AcctSessionTime) FROM radacct WHERE
UserName='mobile''
sqlcounter_expand: '%{sql:SELECT SUM(AcctSessionTime) FROM radacct
WHERE UserName='mobile'}'
radius_xlat: Running registered xlat function of module sql for string
'SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='mobile''
rlm_sql (sql): - sql_xlat
radius_xlat: 'mobile'
rlm_sql (sql): sql_set_user escaped user --> 'mobile'
radius_xlat: 'SELECT SUM(AcctSessionTime) FROM radacct WHERE
UserName='mobile''
rlm_sql (sql): Reserving sql socket id: 27
rlm_sql (sql): - sql_xlat finished
rlm_sql (sql): Released sql socket id: 27
radius_xlat: '284499'
rlm_sqlcounter: (Check item - counter) is greater than zero
rlm_sqlcounter: Authorized user mobile, check_item=420000, counter=284499
rlm_sqlcounter: Sent Reply-Item for user mobile, Type=Session-Timeout,
value=135501
modcall[authorize]: module "noresetcounter" returns ok for request 0
Using perl at 0x82220c0
rlm_perl: Added pair Reply-Message = MAC Auth not Enabled
rlm_perl: Added pair Session-Timeout = 135501
rlm_perl: Added pair Filter-Id = 36/28
rlm_perl: Added pair mpd-limit = in#1=flt1 shape 256000 pass
rlm_perl: Added pair mpd-limit = in#2=all shape 48000
rlm_perl: Added pair mpd-limit = out#1=flt2 shape 512000 pass
rlm_perl: Added pair mpd-limit = out#2=all shape 48000
rlm_perl: Added pair Framed-Protocol = PPP
rlm_perl: Added pair Idle-Timeout = 200
rlm_perl: Added pair mpd-filter = 1#1=match dst 202.xx.xx.xx
rlm_perl: Added pair mpd-filter = 2#1=match src 202.xx.xx.xx
rlm_perl: Added pair Framed-Compression = Van-Jacobson-TCP-IP
rlm_perl: Added pair Expiration = Jul 3 2008 00:00:00 NPT
rlm_perl: Added pair Max-All-Session = 420000
rlm_perl: Added pair User-Password = computer
rlm_perl: Added pair Simultaneous-Use = 2
rlm_perl: Added pair Auth-Type = CHAP
modcall[authorize]: module "perl" returns reject for request 0
modcall: leaving group authorize (returns reject) for request 0
Invalid user: [mobile] (from client pppoe-test port 12 cli 001a4daf4ead)
====================
In radiusd.conf
perl {
module = /usr/local/etc/raddb/mac_check.pl
# List of functions in the module to call.
# Comment out and change if you want to use other
# function names than the defaults.
#
#func_authenticate = authenticate
func_authorize = authorize
#func_preacct = preacct
#func_accounting = accounting
#func_checksimul = checksimul
#func_pre_proxy = pre_proxy
#func_post_proxy = post_proxy
#func_post_auth = post_auth
#func_xlat = xlat
#func_detach = detach
#func_start_accounting = accounting_start
#func_stop_accounting = accounting_stop
}
authorize {
checkval
dailycounter
monthlycounter
noresetcounter
perl
}
=========
sniplet of mac_check.pl
==========
#!/usr/bin/perl
use vars qw(%RAD_REQUEST %RAD_REPLY %RAD_CHECK);
$username = $RAD_REQUEST{'User-Name'};
$callerid = $RAD_REQUEST{'Calling-Station-Id'};
use DBI;
$database = "radius";
$user = "freeradius";
$password = "blabar2";
$option = "localhost";
$dsn = "DBI:mysql:$database";
$dsn = "DBI:mysql:database=$database;$option";
$dbh = DBI->connect($dsn, $user, $password);
my $sql = $dbh->prepare( "SELECT Usemac FROM radcheck WHERE
UserName='$username' AND Attribute='Expiration' ");
my $sql2 = $dbh->prepare( "SELECT Value FROM radcheck WHERE
Attribute='Calling-Station-Id' AND UserName='tori' ");
my $sql3 = $dbh->prepare( "INSERT INTO radcheck
(id,UserName,Attribute,op,Value)
VALUES('','$username','Calling-Station-Id','+=','$cal
lerid' ");
$rowcount = $sql->execute
or die "Cannot execute SQL statement: $DBI::errstr\n";
my @row;
while ( @row = $sql->fetchrow_array() ) {
$mac = $row[0];
chomp($mac);
}
sub authorize {
# Auto assign MAC on first login if MAC is enabled
if ($mac == 1 ){
$rowcount = $sql3->execute
or die "Cannot execute SQL Statement: $DBI::errstr\n";
return RLM_MODULE_OK;
}else {
# Log MAC Auth not enabled in radius log
$RAD_REPLY{'Reply-Message'} = "MAC Auth not Enabled";
return RLM_MODULE_OK;
}
}
$sql->finish;
$dbh->disconnect()
or warn "Disconnection failed: $DBI::errstr\n";
=====
Any suggestion?
Thank you
On 5/15/2008, "Ivan Kalik" <tnt at kalik.net> wrote:
>PS. You should run your script in authorize.
>
>Ivan Kalik
>Kalik Informatika ISP
>
>
>Dana 15/5/2008, "Bishal" <bishal at baayu.com.np> pi¹e:
>
>>
>>Hello Ivan,
>>
>> I came up with this scripts but looks like it;s not working. In
>>radiusd.conf
>>
>>perl{
>> modules = /usr/local/etc/raddb/mac_check.pl
>> }
>>
>>Instantiate {
>> exec
>> expr
>> dailycounter
>> noresetcounter
>> perl
>>}
>>
>>radius debug shows perl modules loaded. But my script is not working. How
>>can I assign variables username and callingStationid in my script during
>>authentication process. rlm_perl doc show %RAD_REQUEST{'User-name'}
>>but it's not helping?
>>
>>
>>
>>
>>
>>
>>#!/usr/bin/perl
>># Check for MAC Authentication is enable or not
>>
>>
>>#$username = $ARGV[4];
>>
>>#$username = %RAD_REQUEST{'User-Name'};
>>#$callerid = %RAD_REQUEST{'Calling-Station-Id'};
>>
>>
>>use DBI;
>>
>>
>>
>> $database = "radius";
>> $user = "freeradius";
>> $password = "blaba2r";
>> $option = "localhost";
>>
>> $dsn = "DBI:mysql:$database";
>> $dsn = "DBI:mysql:database=$database;$option";
>> $dbh = DBI->connect($dsn, $user, $password);
>>
>> my $sql = $dbh->prepare( "SELECT Usemac FROM radcheck WHERE
>>UserName='$RAD_REQUEST{'User-Name'}' AND Attribute='Expiration'
>>");
>> my $sql2 = $dbh->prepare( "SELECT Value FROM radcheck WHERE
>>Attribute='Calling-Station-Id' AND UserName='tori' ");
>> my $sql3 = $dbh->prepare( "INSERT INTO radcheck
>>(id,UserName,Attribute,op,Value)
>>VALUES('','$RAD_REQUEST{'User-Name'}','Calling-Statio
>>n-Id','+=','$RAD_REQUEST{'Calling-Station-Id'}' ");
>>
>>
>> $rowcount = $sql->execute
>> or die "Cannot execute SQL statement: $DBI::errstr\n";
>>
>> my @row;
>> while ( @row = $sql->fetchrow_array() ) {
>> $mac = $row[0];
>> chomp($mac);
>> }
>># Check if MAC authentication is enabled or not if enabled then insert
>>the mac
>> if ($mac == 1 ){
>>
>> $rowcount = $sql3->execute
>> or die "Cannot execute SQL Statement: $DBI::errstr\n";
>>
>>
>>}else {
>>exit;
>>}
>>$sql->finish;
>>$dbh->disconnect()
>>or warn "Disconnection failed: $DBI::errstr\n";
>>
>>
>>On 5/14/2008, "Bishal" <bishal at baayu.com.np> wrote:
>>
>>>
>>>Any sample scripts IVAN?
>>>
>>>
>>>
>>>On 5/14/2008, "Ivan Kalik" <tnt at kalik.net> wrote:
>>>
>>>>>
>>>>> I am using sql for AAA.
>>>>
>>>>I have news for you - you are not. You are using it to store attributes.
>>>>
>>>>>Can u give me some exaples how can I do that
>>>>>withl Rlm_perl modules.?
>>>>>
>>>>
>>>>Do Google: mysql perl tutorial. If it's not MySQL, replace that with the
>>>>name of your sql server.
>>>>
>>>>Ivan Kalik
>>>>Kalik Informatika ISP
>>>>
>>>>-
>>>>List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>>>>
>>>>
>>>
>>>-
>>>List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users..html
>>>
>>>
>>
>>-
>>List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>>
>>
>
>-
>List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
>
More information about the Freeradius-Users
mailing list