Problem using Freeradius with Oracle
Hi all, My setup is FreeBSD 6.2, with Freeradius 1.1.7 installed using Ports (I had to hack the configure so it would be built with Oracle, as there is no option in the Ports make, but I digress...). I have everything working now, but when the accounting stop message is received, the SQL call causes Oracle to balk. The call (with non-relevant portions removed) is: UPDATE radacct SET ... AcctInputOctets = '0' << 32 | '0', AcctOutputOctets = '0' << 32 | '0', ... WHERE ... Now, Acc[input/output]Octets as I see in oraclesql.conf is derived like this: AcctInputOctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}' So it seems (AFAIK) that it's attempting to bit-shift the Gigawords and Octets received from the NAS. My problem is that if this syntax is used, Oracle complains with 'ORA-00933: SQL command not properly ended'. If I remove the <<, and add an extra pipe (|| is concatenate in Oracle), it seems to work, but not as it should. Has anyone got this working in their setup? Cheers, Mike
Mother wrote: ...
Now, Acc[input/output]Octets as I see in oraclesql.conf is derived like this:
AcctInputOctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}'
So it seems (AFAIK) that it's attempting to bit-shift the Gigawords and Octets received from the NAS.
My problem is that if this syntax is used, Oracle complains with 'ORA-00933: SQL command not properly ended'.
See the Oracle documentation for how to do bit-shifting, and how to use 64-bit numbers. Alan DeKok.
Hi Mike, In message <47642AC8.7010403@netstumbler.com>, Mother <mother@netstumbler.com> writes
My setup is FreeBSD 6.2, with Freeradius 1.1.7 installed using Ports (I had to hack the configure so it would be built with Oracle, as there is no option in the Ports make, but I digress...).
I maintain the FreeBSD net/freeradius port. I've not added support for Oracle because nobody has asked for it and I have no way of testing it. If you send me a diff for the port's Makefile, I'll include it in the port. This assumes that FreeRADIUS works with the Oracle support in FreeBSD ports - if not, then you'll either have to sort out a fix to the Oracle support in ports, or continue to hack away! Best wishes, David -- David Wood david@wood2.org.uk
Hi David, David Wood wrote:
Hi Mike,
In message <47642AC8.7010403@netstumbler.com>, Mother <mother@netstumbler.com> writes
My setup is FreeBSD 6.2, with Freeradius 1.1.7 installed using Ports (I had to hack the configure so it would be built with Oracle, as there is no option in the Ports make, but I digress...).
I maintain the FreeBSD net/freeradius port. I've not added support for Oracle because nobody has asked for it and I have no way of testing it. If you send me a diff for the port's Makefile, I'll include it in the port.
No problem, once I'm sure the method I'm following is stable, and write a mini-HOWTO, I will send you the diff. There are basically two places to tweak, one is the actual Makefile (to include Oracle in the menu), and the other is the configure script for rlm_sql_oracle, which seems to basically ignore --with-oracle-home-dir. I ended up hard-coding ORACLE_HOME in the configure script, and copying it just as make finished extracting everything from the tarball (feel free to flame me for being lazy!).
This assumes that FreeRADIUS works with the Oracle support in FreeBSD ports - if not, then you'll either have to sort out a fix to the Oracle support in ports, or continue to hack away!
I have FreeRADIUS working perfectly against a remote Oracle server, using the 10.2.0.3 instantclient and oracle8-client ports. The only difficulty is that there doesn't seem to be an .sql script included with FreeRADIUS to create the Oracle table structure, triggers, etc. so I had to rely on an old version I found. Using this, I was only missing one row from the radacct table, but otherwise I was all set. The next hurdle is to figure out how to combine the Gigaword and octets in Oracle (thanks Alan, will dig through the docs) to make the accounting queries work - they do if I just leave the octets in. Will keep you posted, best regards, Mike
The only difficulty is that there doesn't seem to be an .sql script included with FreeRADIUS to create the Oracle table structure, triggers, etc. so I had to rely on an old version I found.
If it's of any help now: http://wiki.freeradius.org/Oracle_DDL_script Ivan Kalik Kalik Informatika ISP
Hi Ivan, tnt@kalik.co.yu wrote:
The only difficulty is that there doesn't seem to be an .sql script included with FreeRADIUS to create the Oracle table structure, triggers, etc. so I had to rely on an old version I found.
If it's of any help now:
Thanks for that, it's the old version I was mentioning - basically, it is missing the XAscendSessionSvrKey column in the RADACCT table. This is used by the current FreeRADIUS, maybe worth editing into the wiki. Best regards, Mike
-----Original Message----- From: freeradius-users-bounces+frank.ranner=defence.gov.au@lists.fre eradius.org [mailto:freeradius-users-bounces+frank.ranner=defence.gov.au@l ists.freeradius.org] On Behalf Of Mother Sent: Sunday, 16 December 2007 06:28 To: freeradius-users@lists.freeradius.org Subject: Problem using Freeradius with Oracle
Hi all,
My setup is FreeBSD 6.2, with Freeradius 1.1.7 installed using Ports (I had to hack the configure so it would be built with Oracle, as there is no option in the Ports make, but I digress...).
I have everything working now, but when the accounting stop message is received, the SQL call causes Oracle to balk. The call (with non-relevant portions removed) is:
UPDATE radacct SET ... AcctInputOctets = '0' << 32 | '0', AcctOutputOctets = '0' << 32 | '0', ... WHERE ...
Now, Acc[input/output]Octets as I see in oraclesql.conf is derived like this:
AcctInputOctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}'
So it seems (AFAIK) that it's attempting to bit-shift the Gigawords and Octets received from the NAS.
Oracle doesn't seem to do bit shift. You can multiply by 2^32 instead. AcctInputOctets = '%{%{Acct-Input-Gigawords}:-0}' * 4294967296 + '%{%{Acct-Input-Octets}:-0}' Oracle supports up to 38 digit numbers. Regards Frank Ranner
Hi Frank, Ranner, Frank MR wrote: <SNIP>
Oracle doesn't seem to do bit shift. You can multiply by 2^32 instead.
AcctInputOctets = '%{%{Acct-Input-Gigawords}:-0}' * 4294967296 + '%{%{Acct-Input-Octets}:-0}'
Oracle supports up to 38 digit numbers. </SNIP>
Thanks so much for that suggestion, I will give it a go! Best regards, Mike
participants (5)
-
Alan DeKok -
David Wood -
Mother -
Ranner, Frank MR -
tnt＠kalik.co.yu