Add IPPOOL setup for Oracle (Untested by me but reported to work)

diff --git a/raddb/sql/oracle/ippool.conf b/raddb/sql/oracle/ippool.conf
new file mode 100644 (file)
index 0000000..4f01879
--- /dev/null
+++ b/raddb/sql/oracle/ippool.conf
@@ -0,0 +1,108 @@
+#  $Id$\r
+#\r
+# FreeRADIUS rlm_sqlippool SQL Queries for the Oracle Dialect\r
+\r
+allocate-begin = "commit"\r
+start-begin = "commit"\r
+alive-begin = "commit"\r
+stop-begin = "commit"\r
+on-begin = "commit"\r
+off-begin = "commit"\r
+\r
+ ## This query allocates an IP address from the Pool\r
+ ## The ORDER BY clause of this query tries to allocate the same IP-address\r
+ ## to the user that they had last session...\r
+ #allocate-find = "SELECT framedipaddress FROM ${ippool_table} \\r
+ # WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < current_timestamp \\r
+ # ORDER BY (username <> '%{SQL-User-Name}'), \\r
+ # (callingstationid <> '%{Calling-Station-Id}'), expiry_time \\r
+ # LIMIT 1 \\r
+ # FOR UPDATE"\r
+\r
+ ## This function is available if you want to use multiple pools\r
+ allocate-find = "select msqlippool('%{SQL-User-Name}','%{control:Pool-Name}') from dual"\r
+\r
+ ## If you prefer to allocate a random IP address every time, use this query instead\r
+ #allocate-find = "SELECT framedipaddress FROM ${ippool_table} \\r
+ # WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < current_timestamp \\r
+ # ORDER BY RANDOM() \\r
+ # LIMIT 1 \\r
+ # FOR UPDATE"\r
+\r
+\r
+ ## If an IP could not be allocated, check to see whether the pool exists or not\r
+ ## This allows the module to differentiate between a full pool and no pool\r
+ ## Note: If you are not running redundant pool modules this query may be commented\r
+ ## out to save running this query every time an ip is not allocated.\r
+ pool-check = "SELECT id FROM (SELECT id FROM ${ippool_table} \\r
+  WHERE pool_name='%{control:Pool-Name}') WHERE ROWNUM = 1"\r
+\r
+ \r
+ ## This query marks the IP address handed out by "allocate-find" as used\r
+ ## for the period of "lease-duration" after which time it may be reused.\r
+ allocate-update = "UPDATE ${ippool_table} \\r
+  SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \\r
+  callingstationid = '%{Calling-Station-Id}', username = '%{SQL-User-Name}', \\r
+  expiry_time = current_timestamp + INTERVAL '${lease-duration}' second(1) \\r
+  WHERE framedipaddress = '%I'"\r
+\r
+\r
+ ## This query frees the IP address assigned to "pool-key" when a new request\r
+ ## comes in for the same "pool-key". This means that either you are losing\r
+ ## accounting Stop records or you use Calling-Station-Id instead of NAS-Port\r
+ ## as your "pool-key" and your users are able to reconnect before your NAS\r
+ ## has timed out their previous session. (Generally on wireless networks)\r
+ ## (Note: If your pool-key is set to Calling-Station-Id and not NAS-Port\r
+ ## then you may wish to delete the "AND nasipaddress = '%{Nas-IP-Address}'\r
+ ## from the WHERE clause)\r
+ allocate-clear = "UPDATE ${ippool_table} \\r
+  SET nasipaddress = '', pool_key = 0, callingstationid = '', \\r
+  expiry_time = current_timestamp - INTERVAL '1' second(1) \\r
+  WHERE pool_key = '${pool-key}'"\r
+\r
+\r
+ ## This query extends an IP address lease by "lease-duration" when an accounting\r
+ ## START record arrives\r
+ start-update = "UPDATE ${ippool_table} \\r
+  SET expiry_time = current_timestamp + INTERVAL '${lease-duration}' second(1) \\r
+  WHERE nasipaddress = '%{NAS-IP-Address}' \\r
+  AND pool_key = '${pool-key}'"\r
+\r
+\r
+ ## This query frees an IP address when an accounting\r
+ ## STOP record arrives\r
+ stop-clear = "UPDATE ${ippool_table} \\r
+  SET nasipaddress = '', pool_key = 0, callingstationid = '', \\r
+  expiry_time = current_timestamp - INTERVAL '1' second(1) \\r
+  WHERE nasipaddress = '%{Nas-IP-Address}' \\r
+  AND pool_key = '${pool-key}' \\r
+  AND username = '%{SQL-User-Name}' \\r
+  AND callingstationid = '%{Calling-Station-Id}'"\r
+\r
+\r
+ ## This query extends an IP address lease by "lease-duration" when an accounting\r
+ ## ALIVE record arrives\r
+ alive-update = "UPDATE ${ippool_table} \\r
+  SET expiry_time = current_timestamp + INTERVAL '${lease-duration}' second(1) \\r
+  WHERE nasipaddress = '%{Nas-IP-Address}' \\r
+  AND pool_key = '${pool-key}' \\r
+  AND framedipaddress = '%{Framed-IP-Address}' \\r
+  AND username = '%{SQL-User-Name}' \\r
+  AND callingstationid = '%{Calling-Station-Id}'" \r
+\r
+\r
+ ## This query frees all IP addresses allocated to a NAS when an\r
+ ## accounting ON record arrives from that NAS\r
+ on-clear = "UPDATE ${ippool_table} \\r
+  SET nasipaddress = '', pool_key = 0, callingstationid = '', \\r
+  expiry_time = current_timestamp - INTERVAL '1' second(1) \\r
+  WHERE nasipaddress = '%{Nas-IP-Address}'"\r
+\r
+\r
+ ## This query frees all IP addresses allocated to a NAS when an\r
+ ## accounting OFF record arrives from that NAS\r
+ off-clear = "UPDATE ${ippool_table} \\r
+  SET nasipaddress = '', pool_key = 0, callingstationid = '', \\r
+  expiry_time = current_timestamp - INTERVAL '1' second(1) \\r
+  WHERE nasipaddress = '%{Nas-IP-Address}'"\r
+\r

diff --git a/raddb/sql/oracle/msqlippool.txt b/raddb/sql/oracle/msqlippool.txt
new file mode 100644 (file)
index 0000000..3a5758a
--- /dev/null
+++ b/raddb/sql/oracle/msqlippool.txt
@@ -0,0 +1,41 @@
+CREATE OR REPLACE FUNCTION msqlippool(user varchar2, pool varchar2)\r
+RETURN varchar2 IS\r
+\r
+       PRAGMA AUTONOMOUS_TRANSACTION;\r
+       ip_temp varchar2(20);\r
+BEGIN\r
+\r
+    -- If the user's pool is dynamic, get an ipaddress (oldest one) from the corresponding pool\r
+\r
+    if pool = 'Dynamic' then\r
+       select framedipaddress into ip_temp from (select framedipaddress from radippool where expiry_time < current_timestamp and pool_name = pool ORDER BY expiry_time) where rownum = 1;\r
+       return (ip_temp);\r
+\r
+    -- Else, then get the static ipaddress for that user from the corresponding pool\r
+\r
+    else\r
+       select framedipaddress into ip_temp from radippool where username = user and pool_name = pool;\r
+       return (ip_temp);\r
+    end if;\r
+\r
+exception\r
+\r
+ -- This block is executed if there's no free ipaddresses or no static ip assigned to the user\r
+\r
+ when NO_DATA_FOUND then\r
+       if pool = 'Dynamic' then\r
+               return(''); -- so sqlippool can log it on radius.log\r
+       end if;\r
+\r
+       -- Else, grabs a free IP from the static pool and saves it in radippool so the user will always get the same IP the next time\r
+\r
+       select framedipaddress into ip_temp from (select framedipaddress from radippool where expiry_time < current_timestamp and username is null and pool_name = pool) where rownum = 1;\r
+       UPDATE radippool SET username = user where framedipaddress = ip_temp;\r
+       commit;\r
+       return (ip_temp);\r
+\r
+ when others\r
+  then return('Oracle Exception');\r
+\r
+END;\r
+/\r