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 \
19
# ORDER BY (username <> '%{SQL-User-Name}'), \
20
# (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}'"
added
removed
raddb/sql/oracle/ippool.conf
