Re: (RADIATOR) Some problems of using Oracle as an external session database
Hi Hugh,
The syntax is as follow:
AddQueryBEGIN radius.sessiondb ('%n', '%N', %{NAS-Port}, %{Timestamp},
'%{NAS-Port-Type}'); END;
It works.
You can also refer to the DBI faq at
http://www.ibc.wustl.edu/perl5/other/site/DBI/FAQ.html
Thank you for your suggestion
Hugh Irvine wrote:
Hello Anthony -
On Fri, 27 Aug 1999, Anthony Chan wrote:
For the second problem, I try to use store procedure for the Oracle session
database. I create a procedure using the following sql:
CREATE OR REPLACE PROCEDURE radius.sessiondb (NAME IN VARCHAR2 , NASID IN VARCHAR2,
PORT IN NUMBER, TIME IN NUMBER, PORTTYPE IN VARCHAR2)
AS BEGIN
insert into RADONLINE (USERNAME, NASIDENTIFIER, NASPORT, TIME_STAMP,
FRAMEDIPADDRESS, NASPORTTYPE) values (NAME, NASID, PORT, TIME, PORTTYPE);
END;
I use sqlplus to create the above procedure. Then, I exeute the procedure, at the
sql prompt:
EXEC radius.sessiondb ('c200030', '203.63.154.1', 1234, 935668514, 'Async');
The execute is success. Then, I put the following config into the Radiator config
file:
AddQueryexec radius.sessiondb ('%n', '%N', %{NAS-Port}, %{Timestamp},
'%{NAS-Port-Type}')
However, I get the following error message:
Thu Aug 26 19:55:15 1999: ERR: do failed for 'EXECUTE radius.sessiondb ('c200030',
'203.63.154.1', 1234, 935668514, 'Async')': ORA-00900: invalid SQL statement (DBD
ERROR: OCIStmtExecute)
The error shown above is an Oracle error (ORA-00900), but you might try
experimenting with the exec syntax - perhaps remove the parentheses "()"?
AddQuery exec radius.sessiondb '%n', '%N', %{NAS-Port}, %{Timestamp},
'%{NAS-Port-Type}'
I don't find any further descriptions inside the Radiator reference manual
about
it. Can you give me more details about using the store procedures?
Radiator employs the DBD/DBI libraries to avoid database dependencies, so there
is nothing database specific in the code.
BTW - when you get the stored procedures working, please let us know what you
did and we will put an entry in the FAQ describing what is necessary.
thanks
Hugh
--
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald,
Platypus, Freeside, TACACS+, PAM, external, etc etc on Unix, Win95/8,
NT, Rhapsody
===
Archive at http://www.thesite.com.au/~radiator/
To unsubscribe, email '[EMAIL PROTECTED]' with
'unsubscribe radiator' in the body of the message.
--
Cheers,
Anthony Chan
Sun Professional Services Hong Kong
22/F Shui On Centre,
8 Harbour Road, Wanchai,
Hong Kong
Phone: (852) 2820-0674
Fax : (852) 2802-8655
===
Archive at http://www.thesite.com.au/~radiator/
To unsubscribe, email '[EMAIL PROTECTED]' with
'unsubscribe radiator' in the body of the message.
Re: (RADIATOR) Some problems of using Oracle as an external session database
Hi Hugh,
Thank you for your reply.
For the first problem, after searching for the logfile, I can find that there are
at most three null values: ACCTSESSIONID, FRAMEDIPADDRESS, and SERVICETYPE. I
changed the config to be the following:
AddQuery insert into RADONLINE (USERNAME, NASIDENTIFIER, NASPORT, TIME_STAMP,
NASPORTTYPE) values ('%n', '%N', %{NAS-Port}, %{Timestamp}, '%{NAS-Port-Type}')
CountQuery select NASIDENTIFIER, NASPORT from RADONLINE where USERNAME='%n'
Then, I run some testing. From the logfile, I can see the following message:
Fri Aug 26 16:58:54 1999: DEBUG: do query is: insert into RADONLINE (USERNAME,
NASIDENTIFIER, NASPORT, TIME_STAMP, NASPORTTYPE) values ('c25', '203.63.154.1',
1234, 935667533, 'Async')
Fri Aug 26 17:01:02 1999: DEBUG: do query is: select NASIDENTIFIER, NASPORT from
RADONLINE where USERNAME='c25'
Is that it would avoid null values to be inserted into the session database.
For the second problem, I try to use store procedure for the Oracle session
database. I create a procedure using the following sql:
CREATE OR REPLACE PROCEDURE radius.sessiondb (NAME IN VARCHAR2 , NASID IN VARCHAR2,
PORT IN NUMBER, TIME IN NUMBER, PORTTYPE IN VARCHAR2)
AS BEGIN
insert into RADONLINE (USERNAME, NASIDENTIFIER, NASPORT, TIME_STAMP,
FRAMEDIPADDRESS, NASPORTTYPE) values (NAME, NASID, PORT, TIME, PORTTYPE);
END;
I use sqlplus to create the above procedure. Then, I exeute the procedure, at the
sql prompt:
EXEC radius.sessiondb ('c200030', '203.63.154.1', 1234, 935668514, 'Async');
The execute is success. Then, I put the following config into the Radiator config
file:
AddQueryexec radius.sessiondb ('%n', '%N', %{NAS-Port}, %{Timestamp},
'%{NAS-Port-Type}')
However, I get the following error message:
Thu Aug 26 19:55:15 1999: ERR: do failed for 'EXECUTE radius.sessiondb ('c200030',
'203.63.154.1', 1234, 935668514, 'Async')': ORA-00900: invalid SQL statement (DBD
ERROR: OCIStmtExecute)
I don't find any further descriptions inside the Radiator reference manual about
it. Can you give me more details about using the store procedures?
Hugh Irvine wrote:
I encountered two problems of using Oracle 8. The first one is Oracle
core dump and the second one is about the Oracle efficiency.
For the first problem, after I use the Oracle as session database, the
Oracle server always core dump. From the reply of Oracle support, there
is a bug in Oracle 8 that the core dump is due to the existence of null
variables inside the SQL statements. However, they don't have a patch
for our server. So, we have to avoid null variables. From the logfile, I
found that there is always a null variable, servicetype, inside the
insert sql statement, i.e. insert into RADONLINE (USERNAME,
NASIDENTIFIER, NASPORT, ACCTSESSIONID, TIME_STAMP, FRAMEDIPADDRESS,
NASPORTTYPE,SERVICETYPE) values ('lawkei', '207.176.112.154', 20109,
'295908866', 935519065,
'208.151.66.123', 'Async', '').
The workaround for this problem is to replace all null variables using a
space. Can you do me a favour to tell me how to change the source code
for the make it work?
You don't need to change the source code - all you need to do is change the
AddQuery definition in the SessionDatabase declaration:
SessionDatabase SQL
DBSource
DBUsername
DBAuth
AddQuery ...
.
/SessionDatabase
See Section 6.6 in the Radiator 2.14.1 reference manual for further details.
The second problem is that, from the DBA of the Oracle, the Radiator is
now using hardcored SQL statement to the session database. It would
decrease the efficiency of the Oracle. If the frequency of delete and
insert statements are very frequent, it would cause memory leak or even
ora error: 4031. The dba suggest to use variable binding of the SQL
statement.
For the variable binding of SQL statements, it would consist of
ora_login, ora_open, ora_bind. I checked the source code, SessSQL.pm
and SqlDb.pm, that they don't exist. How's your suggestion about it? Is
that possible you can provide us with the variable binding SQL
statements?
Because we use the DBI/DBD libraries, you will not see the Oracle-specific
routines in the Radiator source code. For the above problem you might be better
served with a Stored-Procedure inside Oracle, and use the SessionDatabase
AddQuery statement above to call it.
--
Cheers,
Anthony Chan
Sun Professional Services Hong Kong
22/F Shui On Centre,
8 Harbour Road, Wanchai,
Hong Kong
Phone: (852) 2820-0674
Fax : (852) 2802-8655
===
Archive at http://www.thesite.com.au/~radiator/
To unsubscribe, email '[EMAIL PROTECTED]' with
'unsubscribe radiator' in the body of the message.
(RADIATOR) Some problems of using Oracle as an external session database
Hi Mike,
I encountered two problems of using Oracle 8. The first one is Oracle
core dump and the second one is about the Oracle efficiency.
For the first problem, after I use the Oracle as session database, the
Oracle server always core dump. From the reply of Oracle support, there
is a bug in Oracle 8 that the core dump is due to the existence of null
variables inside the SQL statements. However, they don't have a patch
for our server. So, we have to avoid null variables. From the logfile, I
found that there is always a null variable, servicetype, inside the
insert sql statement, i.e. insert into RADONLINE (USERNAME,
NASIDENTIFIER, NASPORT, ACCTSESSIONID, TIME_STAMP, FRAMEDIPADDRESS,
NASPORTTYPE,SERVICETYPE) values ('lawkei', '207.176.112.154', 20109,
'295908866', 935519065,
'208.151.66.123', 'Async', '').
The workaround for this problem is to replace all null variables using a
space. Can you do me a favour to tell me how to change the source code
for the make it work?
The second problem is that, from the DBA of the Oracle, the Radiator is
now using hardcored SQL statement to the session database. It would
decrease the efficiency of the Oracle. If the frequency of delete and
insert statements are very frequent, it would cause memory leak or even
ora error: 4031. The dba suggest to use variable binding of the SQL
statement.
For the variable binding of SQL statements, it would consist of
ora_login, ora_open, ora_bind. I checked the source code, SessSQL.pm
and SqlDb.pm, that they don't exist. How's your suggestion about it? Is
that possible you can provide us with the variable binding SQL
statements?
Thank you for your attention.
--
Cheers,
Anthony Chan
Sun Professional Services Hong Kong
22/F Shui On Centre,
8 Harbour Road, Wanchai,
Hong Kong
Phone: (852) 2820-0674
Fax : (852) 2802-8655
===
Archive at http://www.thesite.com.au/~radiator/
To unsubscribe, email '[EMAIL PROTECTED]' with
'unsubscribe radiator' in the body of the message.
(RADIATOR) SessionDatabase problem using Oracle with Fork
ct-Session-Id = "1" Acct-Status-Type = Stop Framed-IP-Address = 206.161.55.24 Acct-Delay-Time = 0 Acct-Session-Time = 1000 Acct-Input-Octets = 2 Acct-Output-Octets = 3 Fri Jul 9 13:53:58 1999: DEBUG: Handling request with Handler 'Realm=DEFAULT' Fri Jul 9 13:53:58 1999: DEBUG: Handling with Radius::AuthFILE Fri Jul 9 13:53:58 1999: DEBUG: Accounting accepted Fri Jul 9 13:53:58 1999: DEBUG: Packet dump: *** Sending to 206.161.55.24 port 33100 Code: Accounting-Response Identifier: 185 Authentic: 22200241178140*14531741752301470235224226 Attributes: Fri Jul 9 13:53:58 1999: DEBUG: Child reaped Fri Jul 9 13:53:58 1999: DEBUG: Deleting session for t1c, 206.161.55.24, 1234 Fri Jul 9 13:53:58 1999: DEBUG: do query is: delete from RADONLINE where NASIDENTIFIER='206.161.55.24' and NASPORT=1234 Fri Jul 9 13:53:58 1999: DEBUG: Child reaped Fri Jul 9 13:53:58 1999: ERR: do failed for 'delete from RADONLINE where NASIDENTIFIER='206.161.55.24' and NASPORT=1234': ORA-03113: end-of-file on communication channel (DBD ERROR: OCIStmtExecute) Fri Jul 9 13:53:58 1999: DEBUG: Child reaped Fri Jul 9 13:53:58 1999: DEBUG: Child reaped Fri Jul 9 13:53:58 1999: DEBUG: Child reaped Fri Jul 9 13:59:11 1999: DEBUG: Reading users file /usr/local/etc/config Fri Jul 9 13:59:12 1999: INFO: Server started Fri Jul 9 13:59:30 1999: DEBUG: Packet dump: Then, the Radiator stop to enforce Simultaneous-Use limits. When I start the Sqlplus and query the database table radonline, nothing is inside the table even a user still login. -- Cheers, Anthony Chan Sun Professional Services Hong Kong 22/F Shui On Centre, 8 Harbour Road, Wanchai, Hong Kong Phone: (852) 2820-0674 Fax : (852) 2802-8655 === Archive at http://www.thesite.com.au/~radiator/ To unsubscribe, email '[EMAIL PROTECTED]' with 'unsubscribe radiator' in the body of the message.
(RADIATOR) Configuration for Unix group users
Hello Radiators, My system is Solaris 2.6 with NIS+. I am trying to configure the Radiator to authenticate Unix users by groups. I have a workable configuration as follow: Realm DEFAULT AuthBy FILE Filename ./config /AuthBy AcctLogFileName %L/detail /Realm Realm dummyrealm AuthBy SYSTEM UseGetspnam Identifier System /AuthBy /Realm Inside the file ./config, I have the following lines for the Unix group, g2, with two NAS: DEFAULT Auth-Type=System,Group=g2,NAS-IP-Address=206.161.55.24,Simultaneous-Use=1 DEFAULT Auth-Type=System,Group=g2,NAS-IP-Address=207.176.112.152,Simultaneous-Use=1 Then, if I have many Unix groups and NAS, I must have to define many lines inside the file ./config. For example, if I have ten Unix groups with a hundred NAS, I have to define a thousand lines. The number of such lines would be increased significantly with more Unix groups and NAS. It would be unmanageable and very difficult to maintain. Is there other methods to do the same thing and ease to support? Cheers, Anthony Chan Sun Professional Services Hong Kong 22/F Shui On Centre, 8 Harbour Road, Wanchai, Hong Kong Phone: (852) 2820-0674 Fax : (852) 2802-8655 === Archive at http://www.thesite.com.au/~radiator/ To unsubscribe, email '[EMAIL PROTECTED]' with 'unsubscribe radiator' in the body of the message.
