ippool-dhcp and Oracle
Бен Томпсон
b.thompson at latera.ru
Sun Mar 24 21:29:47 CET 2013
2013/3/24 Alan DeKok <aland at deployingradius.com>:
> Бен Томпсон wrote:
>> I began work on testing with Oracle but I have come across a couple of
>> issues. Firstly one of my SQL statements is throwing up an error, and
>> secondly the server is sending a unicast reply when I need a
>> broadcast. I should be able to fix the dodgy SQL but I wondered if
>> anyone could help me fix the broadcast/unicast problem. Here is the
>> full degug :-
>
> The debug log
>
>> Received DHCP-Discover of id 64b2e216 from 0.0.0.0:68 to 0.0.0.0:67
>> DHCP-Opcode = Client-Message
>> DHCP-Hardware-Type = Ethernet
>> DHCP-Hardware-Address-Length = 6
>> DHCP-Hop-Count = 0
>> DHCP-Transaction-Id = 1689444886
>> DHCP-Number-of-Seconds = 0
>> DHCP-Flags = 0
>
> The broadcast flag isn't set. So the client is asking for a unicast
> response.
>
>> (0) dhcp_sqlippool : expand: 'START TRANSACTION' -> 'START TRANSACTION'
>> rlm_sql (sql): Executing query: 'START TRANSACTION'
>> rlm_sql_oracle: execute query failed in sql_query: ORA-00900: invalid
>> SQL statement
>
> That needs to be fixed. I don't know much about Oracle, and I don't
> have an Oracle system running to test it.
>
>> (0) DHCP: Reply will be sent unicast to your-ip-address
>> Sending DHCP-Offer of id 64b2e216 to 10.99.0.11:68
>
> You should be able to fix this by doing:
>
> update reply {
> DHCP-Flags = Broadcast
> }
>
> Which will force the server to send a broadcast reply.
>
> Alan DeKok.
Hi Alan
Many thanks for the quick reply.
The SQL statement "START TRANSACTION" looks to be hard coded into
rlm_sqlippool.c but I don't know enough about Oracle etiher to say why
it is flagged as an error.
However from looking at the code I assume that it is supposed to
signify the begining of a batch of SQL statements which after
execution will be be either committed or rolled back.
My guess would be that it is a redundant command as according this page:
http://stackoverflow.com/questions/1366851/how-do-i-find-out-if-an-oracle-database-is-set-to-autocommit
- commit/rollback, is a purely client side thing.
So if a client says to Oracle "here are some statements which I will
not commit straight away" I guess the server reply's with "why are you
telling me?".
If I am right then, I guess we can just remove the "START
TRANSACTION" statement for Oracle, but unfoturnately I don't know
enough myself to be sure.
I do have access to and Oracle database though, so I am happy to do
any testing, if someone else with more Oracle knowledge can advise
what we should do.
The broadcast flag did the trick thanks. Here is the DHCP discover
section I am using :-
dhcp DHCP-Discover {
update control {
Pool-Name := test_ip_pool
}
dhcp_sqlippool
update reply {
DHCP-Subnet-Mask = 255.255.255.0
DHCP-Domain-Name-Server = 192.168.12.1
DHCP-Router-Address = 10.99.0.1
DHCP-IP-Address-Lease-Time = 300
DHCP-DHCP-Server-Identifier = 10.99.0.100
}
if (DHCP-Gateway-IP-Address == 0.0.0.0) {
update reply {
DHCP-Flags = Broadcast
}
}
}
However, it seems that none of the options are added to the reply, and
for some reason an empty packet is sent to the client :-
Received DHCP-Discover of id 3f1a9769 from 0.0.0.0:68 to 0.0.0.0:67
DHCP-Opcode = Client-Message
DHCP-Hardware-Type = Ethernet
DHCP-Hardware-Address-Length = 6
DHCP-Hop-Count = 0
DHCP-Transaction-Id = 1058707305
DHCP-Number-of-Seconds = 0
DHCP-Flags = 0
DHCP-Client-IP-Address = 0.0.0.0
DHCP-Your-IP-Address = 0.0.0.0
DHCP-Server-IP-Address = 0.0.0.0
DHCP-Gateway-IP-Address = 0.0.0.0
DHCP-Client-Hardware-Address = 00:0c:29:a6:a0:e7
DHCP-Message-Type += DHCP-Discover
DHCP-Parameter-Request-List += DHCP-Subnet-Mask
DHCP-Parameter-Request-List += DHCP-Broadcast-Address
DHCP-Parameter-Request-List += DHCP-Time-Offset
DHCP-Parameter-Request-List += DHCP-Router-Address
DHCP-Parameter-Request-List += DHCP-Domain-Name
DHCP-Parameter-Request-List += DHCP-Domain-Name-Server
DHCP-Parameter-Request-List += DHCP-Domain-Search
DHCP-Parameter-Request-List += DHCP-Hostname
DHCP-Parameter-Request-List += DHCP-NETBIOS-Name-Servers
DHCP-Parameter-Request-List += DHCP-NETBIOS
DHCP-Parameter-Request-List += DHCP-Interface-MTU-Size
DHCP-Parameter-Request-List += DHCP-Classless-Static-Route
DHCP-Parameter-Request-List += DHCP-NTP-Servers
Trying sub-section dhcp DHCP-Discover {...}
(0) group DHCP-Discover {
(0) - entering group DHCP-Discover {...}
(0) update control {
(0) } # update control = noop
(0) policy dhcp_sqlippool.post-auth {
(0) - entering policy dhcp_sqlippool.post-auth {...}
(0) update request {
(0) expand: 'DHCP-%{DHCP-Client-Hardware-Address}' -> 'DHCP-00:0c:29:a6:a0:e7'
(0) expand: '%{DHCP-Client-Hardware-Address}' -> '00:0c:29:a6:a0:e7'
(0) expand: '%{DHCP-Gateway-IP-Address}' -> '0.0.0.0'
(0) expand: '%{%{DHCP-Gateway-IP-Address}:-127.0.0.1}' -> '0.0.0.0'
(0) } # update request = noop
rlm_sql (sql): Reserved connection (4)
(0) dhcp_sqlippool : expand: '%{User-Name}' -> 'DHCP-00:0c:29:a6:a0:e7'
(0) dhcp_sqlippool : SQL-User-Name updated
(0) dhcp_sqlippool : expand: 'START TRANSACTION' -> 'START TRANSACTION'
rlm_sql (sql): Executing query: 'START TRANSACTION'
rlm_sql_oracle: execute query failed in sql_query: ORA-00900: invalid
SQL statement
rlm_sql_oracle: OCI_SERVER_NORMAL
rlm_sql (sql): Database query error: 'ORA-00900: invalid SQL statement '
sqlippool_command: database query error in: 'START TRANSACTION'
(0) dhcp_sqlippool : expand: 'UPDATE radippool SET nasipaddress =
'', pool_key = '0', callingstationid = '', username = '',
expiry_time = current_timestamp - INTERVAL '1' second(1) WHERE
expiry_time <= current_timestamp - INTERVAL '1' second(1)' -> 'UPDATE
radippool SET nasipaddress = '', pool_key = '0', callingstationid =
'', username = '', expiry_time = current_timestamp - INTERVAL '1'
second(1) WHERE expiry_time <= current_timestamp - INTERVAL '1'
second(1)'
rlm_sql (sql): Executing query: 'UPDATE radippool SET nasipaddress =
'', pool_key = '0', callingstationid = '', username = '',
expiry_time = current_timestamp - INTERVAL '1' second(1) WHERE
expiry_time <= current_timestamp - INTERVAL '1' second(1)'
(0) dhcp_sqlippool : escape: 'test_ip_pool' -> 'test_ip_pool'
(0) dhcp_sqlippool : escape: 'DHCP-00:0c:29:a6:a0:e7' ->
'DHCP-00:0c:29:a6:a0:e7'
(0) dhcp_sqlippool : escape: '00:0c:29:a6:a0:e7' -> '00:0c:29:a6:a0:e7'
(0) dhcp_sqlippool : expand: 'SELECT framedipaddress FROM radippool
WHERE pool_name = '%{control:Pool-Name}' AND expiry_time <
current_timestamp AND rownum <= 1 ORDER BY CASE WHEN username =
'%{User-Name}' THEN 0 ELSE 1 END, CASE WHEN callingstationid =
'%{Calling-Station-Id}' THEN 0 ELSE 1 END, expiry_time FOR UPDATE'
-> 'SELECT framedipaddress FROM radippool WHERE pool_name =
'test_ip_pool' AND expiry_time < current_timestamp AND rownum <= 1
ORDER BY CASE WHEN username = 'DHCP-00:0c:29:a6:a0:e7' THEN 0
ELSE 1 END, CASE WHEN callingstationid = '00:0c:29:a6:a0:e7' THEN
0 ELSE 1 END, expiry_time FOR UPDATE'
rlm_sql (sql): Executing query: 'SELECT framedipaddress FROM radippool
WHERE pool_name = 'test_ip_pool' AND expiry_time < current_timestamp
AND rownum <= 1 ORDER BY CASE WHEN username =
'DHCP-00:0c:29:a6:a0:e7' THEN 0 ELSE 1 END, CASE WHEN
callingstationid = '00:0c:29:a6:a0:e7' THEN 0 ELSE 1 END,
expiry_time FOR UPDATE'
(0) dhcp_sqlippool : escape: '0.0.0.0' -> '0.0.0.0'
(0) dhcp_sqlippool : escape: '00:0c:29:a6:a0:e7' -> '00:0c:29:a6:a0:e7'
(0) dhcp_sqlippool : escape: '00:0c:29:a6:a0:e7' -> '00:0c:29:a6:a0:e7'
(0) dhcp_sqlippool : escape: 'DHCP-00:0c:29:a6:a0:e7' ->
'DHCP-00:0c:29:a6:a0:e7'
(0) dhcp_sqlippool : expand: 'UPDATE radippool SET nasipaddress =
'%{NAS-IP-Address}', pool_key = '%{DHCP-Client-Hardware-Address}',
callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}',
expiry_time = current_timestamp + INTERVAL '300' SECOND(1) WHERE
framedipaddress = '10.99.0.11' AND expiry_time IS NULL' -> 'UPDATE
radippool SET nasipaddress = '0.0.0.0', pool_key =
'00:0c:29:a6:a0:e7', callingstationid = '00:0c:29:a6:a0:e7', username
= 'DHCP-00:0c:29:a6:a0:e7', expiry_time = current_timestamp +
INTERVAL '300' SECOND(1) WHERE framedipaddress = '10.99.0.11' AND
expiry_time IS NULL'
rlm_sql (sql): Executing query: 'UPDATE radippool SET nasipaddress =
'0.0.0.0', pool_key = '00:0c:29:a6:a0:e7', callingstationid =
'00:0c:29:a6:a0:e7', username = 'DHCP-00:0c:29:a6:a0:e7', expiry_time
= current_timestamp + INTERVAL '300' SECOND(1) WHERE framedipaddress
= '10.99.0.11' AND expiry_time IS NULL'
(0) dhcp_sqlippool : Allocated IP 10.99.0.11 [0b00630a]
(0) dhcp_sqlippool : expand: 'COMMIT' -> 'COMMIT'
rlm_sql (sql): Executing query: 'COMMIT'
rlm_sql (sql): Released connection (4)
rlm_sql (sql): Closing connection (0): Too many free connections (5 > 3)
rlm_sql_mysql: Socket destructor called, closing socket
(0) dhcp_sqlippool : expand: 'DHCP: Allocated IP:
%{reply:Framed-IP-Address} from %{control:Pool-Name} (did
%{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user
%{User-Name})' -> 'DHCP: Allocated IP: 10.99.0.11 from test_ip_pool
(did cli 00:0c:29:a6:a0:e7 port user DHCP-00:0c:29:a6:a0:e7)'
DHCP: Allocated IP: 10.99.0.11 from test_ip_pool (did cli
00:0c:29:a6:a0:e7 port user DHCP-00:0c:29:a6:a0:e7)
(0) [dhcp_sqlippool] = ok
(0) ? if (ok)
(0) ? Evaluating (ok) -> TRUE
(0) ? if (ok) -> TRUE
(0) if (ok) {
(0) - entering if (ok) {...}
(0) update reply {
(0) expand: '%{reply:Framed-IP-Address}' -> '10.99.0.11'
(0) } # update reply = ok
(0) - if (ok) returns ok
(0) - policy dhcp_sqlippool.post-auth returns ok
(0) update reply {
(0) } # update reply = ok
(0) ? if (DHCP-Gateway-IP-Address == 0.0.0.0)
(0) ? Evaluating (DHCP-Gateway-IP-Address == 0.0.0.0) -> TRUE
(0) ? if (DHCP-Gateway-IP-Address == 0.0.0.0) -> TRUE
(0) if (DHCP-Gateway-IP-Address == 0.0.0.0) {
(0) - entering if (DHCP-Gateway-IP-Address == 0.0.0.0) {...}
(0) update reply {
(0) } # update reply = ok
(0) - if (DHCP-Gateway-IP-Address == 0.0.0.0) returns ok
(0) DHCP: Reply will be broadcast
Sending DHCP-Offer of id 3f1a9769 to 255.255.255.255:68
(0) Finished request 0.
Waking up in 0.2 seconds.
Waking up in 4.7 seconds.
(0) Cleaning up request packet ID 1058707305 with timestamp +9
Ready to process requests.
Signalled to terminate
Exiting normally.
More information about the Freeradius-Users
mailing list