(RADIATOR) Stored Procedure (I m stuck)!!!

Tue Aug 26 03:33:15 CDT 2003

Hi Hakim -

h> hi Pavel!!!
h> Thanx for the reply. But if you dig the radiator mailing list, in one of the emails Mike has replied that Radiator expects a recordset to be returned.
I think Mike means `recordset` in PERL context not ORACLE.
Hi Mike :)) Correct me if I'm wrong.

Anyway you should declare IN or OUT parameters in stored procedure.
AuthPLSQL cannot handle ORACLE cursors.

h> Also the method you are suggesting if done that way, the user is always AUTHENTICATED even if the username and/or password is wrong.
I dont think so. Because if there is no username in
authenticationtable_000 table the NO_DATA_FOUND exception will be
raised, and user will be rejected. And you will see the error message in
the Radiator log.

But you can process this situation by adding check_item OUT parameter
to procedure and set it to 'Auth-Type=Reject' explicitly.
For example, sp_auth2 can look like this:

procedure sp_Auth2(sUsername in varchar2,sPassword out varchar2,check_item OUT varchar2)
   is
begin
   select password
      into sPassword
      from authenticationtable_000 where
      username=sUsername;
exception
    when NO_DATA_FOUND then
       check_item:='Auth-Type=Reject:No such user';
end sp_Auth2;

And in radius config:

<AuthBy PLSQL>
         NoDefault
         DBSource dbi:Oracle:tas
         DBUsername %{GlobalVar:dbUsername}
         DBAuth %{GlobalVar:dbAuth}
         AuthBlock  begin \
                tasradius.sp_Auth2('%n',:sReturnValue,:check_item); \
                end;
         AuthParamDef :sReturnValue,User-Password,Check
         AuthParamDef :check, GENERIC, check
</AuthBy>

P.S. I've wrote AuthPLSQL to control user access by multiple billing
parameters which I can get only by calling stored procedure. (it's
demand of our billing system)
But if you use one table to store users' credentials try to use
AuthSQL. It is simplier and faster.

h> I feel what I am doing wrong is binding of the variables OUT parameter (Cursor). 
h> Any feedback/help would be appreciated!!!
h> Regards
h> Hakim
h> ---------- Original Message ----------------------------------
h> From: Pavel A Crasotin <pavel at ctk.ru>
h> Reply-To: Pavel A Crasotin <pavel at ctk.ru>
h> Date:  Tue, 26 Aug 2003 08:57:41 +0400

>>Hello Hakim -
>>
>>There is no need to use cursors (it is possible but I dont think about
>>it :)). 
>>
>>Just create sp_auth2 as
>>
>>procedure sp_Auth2(sUsername in varchar2,sPassword out varchar2)
>>   is
>>begin
>>   select password
>>      into sPassword
>>      from authenticationtable_000 where
>>      username=sUsername;
>>exception
>>    when others then
>>                 null;
>>end sp_Auth2;
>>
>>There is plsql.cfg it the goodies/ dir in Radiator distribution.
>>Try to read it. Hope it helps.
>>
>>
>>
>>h> hello!!!
>>h> What I am doing wrong here??? Any help would be appreciated!!!
>>
>>
>>h> ----SAMPLE CONFIG FILE----
>>h> Trace 4
>>h> AuthPort 1812
>>h> AcctPort 1813
>>h> LogDir c:\Radiator3.6\dump
>>h> DictionaryFile ./dictionary
>>h> UsernameCharset a-zA-Z0-9\._ at -
>>h> LogFile %L/%Y-%m-2logfile.log
>>
>>h> DefineFormattedGlobalVar dbSourceODBC tas_sys
>>h> DefineFormattedGlobalVar dbSourceOracle tas
>>h> DefineFormattedGlobalVar dbUsername xxx
>>h> DefineFormattedGlobalVar dbAuth xxx
>>
>>h> <Client localhost>
>>h> Secret mysecret
>>h> DupInterval 300
>>h> </Client>
>>
>>h> <SessionDatabase SQL>
>>h> DBSource dbi:Oracle:tas3
>>h> DBUsername xxx
>>h> DBAuth xxx
>>h> AddQuery        insert into RADONLINE (username,code) values ('%n','000')
>>h> </SessionDatabase>
>>
>>h> <Handler>
>>h> <AuthBy PLSQL>
>>h>         NoDefault
>>h>         DBSource dbi:Oracle:tas
>>h>         DBUsername %{GlobalVar:dbUsername}
>>h>         DBAuth %{GlobalVar:dbAuth}
>>h>         AuthBlock  begin \
>>h>                tasradius.sp_Auth2('%n',:sReturnValue); \
>>h>                end;
>>h>         AuthParamDef :sReturnValue,User-Password,Check
>>h> </AuthBy>
>>h> </Handler>
>>
>>h> ----END OF SAMPLE CONFIG FILE ---
>>
>>h> -----STORED PROCEDURE ---------
>>h> CREATE OR REPLACE package tasradius
>>h> is
>>h> type Cursors is ref cursor return radreturn%rowtype;
>>h> procedure sp_Auth2(sUsername in varchar2,sReturn out tasradius.Cursors);
>>h> end tasradius;
>>
>>h> procedure sp_Auth2(sUsername in varchar2,sReturn out tasradius.Cursors)
>>h> is
>>h> begin
>>h> open sReturn for select password from authenticationtable_000 where username='test';
>>h> exception
>>h>     when others then
>>h>                  null;
>>h> end sp_Auth2;
>>h> end tasradius;
>>h> ----- END OF STORED PROCEDURE ---------
>>
>>h> Regards
>>h> Hakim
>>h> ===
>>h> Archive at http://www.open.com.au/archives/radiator/
>>h> Announcements on radiator-announce at open.com.au
>>h> To unsubscribe, email 'majordomo at open.com.au' with
>>h> 'unsubscribe radiator' in the body of the message.
>>
>>
>>With respect,
>>Pavel A Crasotin
>>____________________________________
>>OJSC SeverTransCom
>>Tel/Fax: +7 (0852) 58-01-00, 72-17-28
>>
>>===
>>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.
>>
h> ===
h> Archive at http://www.open.com.au/archives/radiator/
h> Announcements on radiator-announce at open.com.au
h> To unsubscribe, email 'majordomo at open.com.au' with
h> 'unsubscribe radiator' in the body of the message.

With respect,
Pavel A Crasotin
____________________________________
OJSC SeverTransCom
Tel/Fax: +7 (0852) 58-01-00, 72-17-28

===
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.