3 ## ippool.conf -- Oracle queries for rlm_sqlippool
7 allocate-begin = "commit"
14 ## This query allocates an IP address from the Pool
15 ## The ORDER BY clause of this query tries to allocate the same IP-address
16 ## to the user that they had last session...
17 allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
18 WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < current_timestamp \
20 ORDER BY (username <> '%{SQL-User-Name}'), \
21 (callingstationid <> '%{Calling-Station-Id}'), expiry_time \
24 ## This function is available if you want to use multiple pools
25 # allocate-find = "select msqlippool('%{SQL-User-Name}','%{control:Pool-Name}') from dual"
27 ## If you prefer to allocate a random IP address every time, use this query instead
28 #allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
29 # WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < current_timestamp \
35 ## If an IP could not be allocated, check to see whether the pool exists or not
36 ## This allows the module to differentiate between a full pool and no pool
37 ## Note: If you are not running redundant pool modules this query may be commented
38 ## out to save running this query every time an ip is not allocated.
39 pool-check = "SELECT id FROM (SELECT id FROM ${ippool_table} \
40 WHERE pool_name='%{control:Pool-Name}') WHERE ROWNUM = 1"
43 ## This query marks the IP address handed out by "allocate-find" as used
44 ## for the period of "lease-duration" after which time it may be reused.
45 allocate-update = "UPDATE ${ippool_table} \
46 SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
47 callingstationid = '%{Calling-Station-Id}', username = '%{SQL-User-Name}', \
48 expiry_time = current_timestamp + INTERVAL '${lease-duration}' second(1) \
49 WHERE framedipaddress = '%I'"
52 ## This query frees the IP address assigned to "pool-key" when a new request
53 ## comes in for the same "pool-key". This means that either you are losing
54 ## accounting Stop records or you use Calling-Station-Id instead of NAS-Port
55 ## as your "pool-key" and your users are able to reconnect before your NAS
56 ## has timed out their previous session. (Generally on wireless networks)
57 ## (Note: If your pool-key is set to Calling-Station-Id and not NAS-Port
58 ## then you may wish to delete the "AND nasipaddress = '%{Nas-IP-Address}'
59 ## from the WHERE clause)
60 allocate-clear = "UPDATE ${ippool_table} \
61 SET nasipaddress = '', pool_key = 0, callingstationid = '', \
62 expiry_time = current_timestamp - INTERVAL '1' second(1) \
63 WHERE pool_key = '${pool-key}'"
66 ## This query extends an IP address lease by "lease-duration" when an accounting
67 ## START record arrives
68 start-update = "UPDATE ${ippool_table} \
69 SET expiry_time = current_timestamp + INTERVAL '${lease-duration}' second(1) \
70 WHERE nasipaddress = '%{NAS-IP-Address}' \
71 AND pool_key = '${pool-key}'"
74 ## This query frees an IP address when an accounting
75 ## STOP record arrives
76 stop-clear = "UPDATE ${ippool_table} \
77 SET nasipaddress = '', pool_key = 0, callingstationid = '', \
78 expiry_time = current_timestamp - INTERVAL '1' second(1) \
79 WHERE nasipaddress = '%{Nas-IP-Address}' \
80 AND pool_key = '${pool-key}' \
81 AND username = '%{SQL-User-Name}' \
82 AND callingstationid = '%{Calling-Station-Id}'"
85 ## This query extends an IP address lease by "lease-duration" when an accounting
86 ## ALIVE record arrives
87 alive-update = "UPDATE ${ippool_table} \
88 SET expiry_time = current_timestamp + INTERVAL '${lease-duration}' second(1) \
89 WHERE nasipaddress = '%{Nas-IP-Address}' \
90 AND pool_key = '${pool-key}' \
91 AND framedipaddress = '%{Framed-IP-Address}' \
92 AND username = '%{SQL-User-Name}' \
93 AND callingstationid = '%{Calling-Station-Id}'"
96 ## This query frees all IP addresses allocated to a NAS when an
97 ## accounting ON record arrives from that NAS
98 on-clear = "UPDATE ${ippool_table} \
99 SET nasipaddress = '', pool_key = 0, callingstationid = '', \
100 expiry_time = current_timestamp - INTERVAL '1' second(1) \
101 WHERE nasipaddress = '%{Nas-IP-Address}'"
104 ## This query frees all IP addresses allocated to a NAS when an
105 ## accounting OFF record arrives from that NAS
106 off-clear = "UPDATE ${ippool_table} \
107 SET nasipaddress = '', pool_key = 0, callingstationid = '', \
108 expiry_time = current_timestamp - INTERVAL '1' second(1) \
109 WHERE nasipaddress = '%{Nas-IP-Address}'"