Hi,
I fixed few minor mistake and a little improvement in the oracle SQL file (oraclesql.conf) and modified the SQL commands to create the NAS table (db_oracle.sql). I attached at the end the two patches, and following there is the description of changes for oraclesql.conf.
1) fixed a typo in the description of accounting_stop_query_alt query
2) fixed a wrong SQL query ( ' = IS NULL' is not correct)
3) modified radius_db to use the previous entered parameters. I added the variable 'sid'. I don't know if it is correct to put it only in the sql.conf file or if I must declare it in the source code. This change works for me.
I hope this will be useful to you.
Andrea
--- raddb/oraclesql.conf.orig 2003-11-22 18:54:38.440337000 +0100 +++ raddb/oraclesql.conf 2003-11-22 19:00:43.120332000 +0100 @@ -16,11 +16,13 @@
# Connect info
server = "localhost"
+ port = "1521"
+ sid = "YOUR_SID"
login = "root"
password = "rootpass"# Database table configuration
- radius_db = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=your_sid)))"
+ radius_db = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=${server})(PORT=${port}))(CONNECT_DATA=(SID=${sid})))"
# If you want both stop and start records logged to the
# same SQL table, leave this as is. If you want them in
@@ -137,7 +139,7 @@
# accounting_start_query_alt - query for Accounting start packets
# (alternate in case first query fails)
# accounting_stop_query - query for Accounting stop packets
- # accounting_stop_query_alt - query for Accounting start packets
+ # accounting_stop_query_alt - query for Accounting stop packets
# (alternate in case first query doesn't
# affect any existing rows in the table)
#######################################################################
@@ -147,7 +149,7 @@
accounting_start_query = "INSERT into ${acct_table1} (RadAcctId, AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('', '%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port-Id}', '%{NAS-Port-Type}', TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), NULL, '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0')"
- accounting_start_query_alt = "UPDATE ${acct_table1} SET AcctStartTime = TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), AcctStartDelay = '%{Acct-Delay-Time}', ConnectInfo_start = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime = IS NULL"
+ accounting_start_query_alt = "UPDATE ${acct_table1} SET AcctStartTime = TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), AcctStartDelay = '%{Acct-Delay-Time}', ConnectInfo_start = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
accounting_stop_query = "UPDATE ${acct_table2} SET AcctStopTime = TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets = '%{Acct-Input-Octets}', AcctOutputOctets = '%{Acct-Output-Octets}', AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time}', ConnectInfo_stop = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
--- src/modules/rlm_sql/drivers/rlm_sql_oracle/db_oracle.sql.orig 2004-04-24 17:07:59.022777000 +0100
+++ src/modules/rlm_sql/drivers/rlm_sql_oracle/db_oracle.sql 2004-04-24 17:21:18.652732000 +0100
@@ -27,16 +27,14 @@
*/
CREATE TABLE nas (
- id INT PRIMARY KEY,
- nasname VARCHAR(128),
+ id INT,
+ nasname VARCHAR(128) NOT NULL PRIMARY KEY,
shortname VARCHAR(32),
- ipaddr VARCHAR(15),
- type VARCHAR(30),
+ type VARCHAR(30) DEFAULT 'other',
ports INT,
- secret VARCHAR(60),
+ secret VARCHAR(60) DEFAULT 'secret' NOT NULL,
community VARCHAR(50),
- snmp VARCHAR(10),
- naslocation VARCHAR(10)
+ description VARCHAR(200) DEFAULT 'RADIUS Client'
);
CREATE SEQUENCE nas_seq START WITH 1 INCREMENT BY 1;
--------------------------------------- The advantage of fast computers is that they make errors in no time. --------------------------------------- Ing. Andrea Gabellini Email: [EMAIL PROTECTED] Tel: 0549 886111 (Italy) Tel. +378 0549 886111 (International)
Intelcom San Marino S.p.A. Strada degli Angariari, 3 47891 Rovereta Repubblic of San Marino
http://www.omniway.sm http://www.intelcom.sm
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
