(RADIATOR) might be a AuthPLSQL.pm issue

[Pavel A Crasotin]

Hello Mishari -

I'll try to make some tests on our Radiator.

MAF> Dear All,

MAF> We're using Radiator to query an Oracle DB for authentication, and using AuthPLSQL.pm.

MAF> <AuthBy PLSQL>
MAF>         Identifier dslauthplsql
MAF>         NoDefault
MAF>         DBSource        dbi:Oracle:oracledb 
MAF>         DBUsername      xxx
MAF>         DBAuth          xxx

MAF>         DefaultSimultaneousUse 1

MAF>         AuthBlock       BEGIN
MAF> aaa.is_active('%U',:passwd,:check_item,:reply_item,'%{NAS-Identifier}','%{NAS-Port}','%P',1,'%{NAS-Port-Type}','%{NAS-IP-Address}',:bandwidth,:static_ip,:framed_route); END;
MAF>         AuthParamDef    :passwd,        User-Password,  check
MAF>         AuthParamDef    :check_item,    GENERIC,        check
MAF>         AuthParamDef    :reply_item,    Session-Timeout,reply
MAF>         AuthParamDef    :bandwidth,     GENERIC,        reply 
MAF>         AuthParamDef    :static_ip,     Framed-IP-Address,reply
MAF>         AuthParamDef    :framed_route,  Framed-Route,reply
MAF>         SQLRecoveryFile %L/qualitynet/sqlfailures.%m.%d
MAF> </AuthBy>

MAF> We're noticing some weird delay in getting the response to the
MAF> query, here's some snipets from our trace 4 microsecond log. 


MAF> Wed Mar 29 00:00:00 2006 147441: DEBUG: Packet dump:
MAF> *** Received from 213.189.95.4 port 1102 ....
MAF> Code:       Access-Request
MAF> Identifier: 9
MAF> Authentic:  4<12>A<178><218><212><242>}<174>H0<159><176>nTB 
MAF> Attributes:
MAF>         NAS-IP-Address = 62.150.1.10
MAF>         NAS-Port = 167
MAF>         NAS-Port-Type = Async
MAF>         User-Name = "1400069509"
MAF>         Called-Station-Id = "811811" 
MAF>         Calling-Station-Id = "5658640"
MAF>         User-Password =
MAF> "><132><145><128><238><207><173><225><229>W<195>c<224>\ <223>" 
MAF>         Service-Type = Framed-User
MAF>         Framed-Protocol = PPP
MAF>         Framed-IP-Address = 62.150.181.89
MAF>         NAS-Identifier = "ASK10"
MAF>         Cisco-NAS-Port = "Async167" 

MAF> Wed Mar 29 00:00:00 2006 148685: DEBUG: Handling request with
MAF> Handler 'Called-Station-Id=811811'
MAF> Wed Mar 29 00:00:00 2006 149036: DEBUG: Rewrote user name to 1400069509
MAF> Wed Mar 29 00:00:00 2006 149307: DEBUG: Rewrote user name to 1400069509
MAF> Wed Mar 29 00:00:00 2006 149624: DEBUG: qualitynet-online
MAF> Deleting session for 1400069509, 62.150.1.10, 167
MAF> Wed Mar 29 00:00:00 2006 149973: DEBUG: do query is: 'delete
MAF> from RADONLINE where NASIDENTIFIER=' 62.150.1.10' and NASPORT=0167':

MAF> Wed Mar 29 00:00:00 2006 151092: DEBUG: Handling with Radius::AuthPLSQL
MAF> Wed Mar 29 00:00:00 2006 151385: DEBUG: Handling with
MAF> Radius::AuthPLSQL: dslauthplsql 
MAF> Wed Mar 29 00:00:00 2006 151846: DEBUG: Query is: BEGIN
MAF> aaa.is_active('1400069509',:passwd,:check_item,:reply_item,'ASK10','167','xxxxxx',1,'Async',' 62.150
MAF> .1.10',:bandwidth,:static_ip,:framed_route); END;

MAF>  Wed Mar 29 00:00:00 2006 222949: DEBUG: Radius::AuthPLSQL looks for match with 1400069509
MAF> Wed Mar 29 00:00:00 2006 223482: DEBUG: Query is: 'select
MAF> NASIDENTIFIER, NASPORT, ACCTSESSIONID, FRAMEDIPADDRESS from
MAF> RADONLINE where USERNAME='1400069509'':


MAF> Wed Mar 29 00:00:00 2006 224183: DEBUG: Radius::AuthPLSQL ACCEPT: 
MAF> Wed Mar 29 00:00:00 2006 224540: DEBUG: Access accepted for 1400069509
MAF> Wed Mar 29 00:00:00 2006 233989: DEBUG: do query is: 'insert
MAF> into QUALITYNET_AUTH_LOG
MAF> values('1143579600','OK','1400069509','811811','5658640',NULL,'76491251
MAF> ','ASK10')':

MAF> Wed Mar 29 00:00:00 2006 234980: DEBUG: Packet dump:
MAF> *** Sending to 213.189.95.4 port 1102 ....
MAF> Code:       Access-Accept
MAF> Identifier: 9
MAF> Authentic:  4<12>A<178><218><212><242>}<174>H0<159><176>nTB 
MAF> Attributes:



MAF> As you can see, the time spent doing the Oracle query is about
MAF> 70ms. Upon inspection of the whole log, these times are almost
MAF> hovering between 60-100ms. However, using tnsping we saw that the
MAF> network delay is almost always 0ms. Also, we tried connecting to
MAF> the DB using the SQLPLUS interface and executed the query from
MAF> there (from the remore client not the DB iteself) and we get times of no more than 4-6ms.

MAF> Does that suggest there's a problem in the DBD::Oracle or
MAF> AuthPLSQL.pm? I'm clueless there.

MAF> Thanks for any help
MAF>   

--
With respect,
Pavel A Crasotin
OJSC SeverTransCom
Tel: +7 (4852) 58-41-03, 58-01-01
Fax: +7 (4852) 58-01-01


--
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.