/etc/freeradius/3.0/mods-config/sql/ippool-dhcp/oracle/queries.conf is in freeradius-config 3.0.12+dfsg-5+deb9u1.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 | # -*- text -*-
#
# ippool-dhcp/oracle/queries.conf -- Oracle queries for dhcp-ippool
#
# $id: 416d59802a1321c16b936bb5e63c288ca3634bcd $
#
# "START TRANSACTION" not required with Oracle
#
allocate_begin = ""
start_begin = ""
alive_begin = ""
stop_begin = ""
on_begin = ""
off_begin = ""
#
# This query allocates an IP address from the Pool
# It query tries to allocate to the user
# either the same IP-address that they had last session
# or the IP which has been unused for the longest period of time
#
allocate_find = "\
WITH POOLS AS (\
SELECT * \
FROM ${ippool_table} \
WHERE pool_name = '%{control:Pool-Name}' \
AND (\
pool_key = '${pool_key}' \
OR expiry_time = (\
SELECT MIN(expiry_time) \
FROM ${ippool_table} \
WHERE pool_name = '%{control:Pool-Name}' \
AND expiry_time < CURRENT_TIMESTAMP AND pool_key != '${pool_key}'\
)\
)\
) \
SELECT framedipaddress \
FROM (\
SELECT framedipaddress \
FROM POOLS \
WHERE pool_key = '${pool_key}' \
OR (\
NOT EXISTS (\
SELECT 1 \
FROM POOLS \
WHERE pool_key = '${pool_key}'\
)\
)\
) WHERE ROWNUM = 1 FOR UPDATE"
#
# This function is available if you want to use multiple pools
#
#allocate_find = "\
SELECT msqlippool('%{SQL-User-Name}','%{control:Pool-Name}') \
FROM dual"
#
# If you prefer to allocate a random IP address every time, use this query instead
#
#allocate_find = "\
# SELECT framedipaddress \
# FROM ${ippool_table}\
# WHERE framedipaddress = (\
# SELECT framedipaddress \
# FROM (\
# SELECT framedipaddress \
# FROM ${ippool_table} \
# WHERE pool_name = '%{control:Pool-Name}' \
# AND expiry_time < CURRENT_TIMESTAMP \
# ORDER BY DBMS_RANDOM.VALUE\
# ) \
# WHERE ROWNUM = 1\
# ) \
# FOR UPDATE"
#
# If an IP could not be allocated, check to see whether the pool exists or not
# This allows the module to differentiate between a full pool and no pool
# Note: If you are not running redundant pool modules this query may be commented
# out to save running this query every time an ip is not allocated.
#
#pool_check = "\
# SELECT id \
# FROM (\
# SELECT id \
# FROM ${ippool_table} \
# WHERE pool_name = '%{control:Pool-Name}'\
# ) WHERE ROWNUM = 1"
#
# This query marks the IP address handed out by "allocate_find" as used
# for the period of "lease_duration" after which time it may be reused.
#
allocate_update = "\
UPDATE ${ippool_table} \
SET \
nasipaddress = '%{NAS-IP-Address}', \
pool_key = '${pool_key}', \
callingstationid = '%{Calling-Station-id}', \
username = '%{SQL-User-Name}', \
expiry_time = CURRENT_TIMESTAMP + INTERVAL '${lease_duration}' SECOND(1) \
WHERE framedipaddress = '%I'"
#
# This query frees the IP address assigned to "pool_key" when a new request
# comes in for the same "pool_key". This means that either you are losing
# accounting Stop records or you use Calling-Station-id instead of NAS-Port
# as your "pool_key" and your users are able to reconnect before your NAS
# has timed out their previous session. (Generally on wireless networks)
# (Note: If your pool_key is set to Calling-Station-id and not NAS-Port
# then you may wish to delete the "AND nasipaddress = '%{NAS-IP-Address}'
# from the WHERE clause)
#
allocate_clear = "\
UPDATE ${ippool_table} \
SET \
expiry_time = CURRENT_TIMESTAMP - INTERVAL '1' SECOND(1) \
WHERE pool_key = '${pool_key}'"
#
# This query extends an IP address lease by "lease_duration" when an accounting
# START record arrives
#
start_update = "\
UPDATE ${ippool_table} \
SET \
expiry_time = CURRENT_TIMESTAMP + INTERVAL '${lease_duration}' SECOND(1) \
WHERE nasipaddress = '%{NAS-IP-Address}' \
AND pool_name = '%{control:Pool-Name}' \
AND pool_key = '${pool_key}' \
AND framedipaddress = '%{Framed-IP-Address}'"
#
# This query frees an IP address when an accounting
# STOP record arrives
#
stop_clear = "\
UPDATE ${ippool_table} \
SET \
expiry_time = CURRENT_TIMESTAMP - INTERVAL '1' SECOND(1) \
WHERE pool_key = '${pool_key}'"
#
# This query extends an IP address lease by "lease_duration" when an accounting
# ALIVE record arrives
#
alive_update = "\
UPDATE ${ippool_table} \
SET \
expiry_time = CURRENT_TIMESTAMP + INTERVAL '${lease_duration}' SECOND(1) \
WHERE pool_key = '${pool_key}' \
AND pool_name = '%{control:Pool-Name}' \
AND framedipaddress = '%{Framed-IP-Address}'"
#
# This query frees all IP addresses allocated to a NAS when an
# accounting ON record arrives from that NAS
#
on_clear = "\
UPDATE ${ippool_table} \
SET \
expiry_time = CURRENT_TIMESTAMP - INTERVAL '1' SECOND(1) \
WHERE nasipaddress = '%{NAS-IP-Address}'"
#
# This query frees all IP addresses allocated to a NAS when an
# accounting OFF record arrives from that NAS
#
off_clear = "\
UPDATE ${ippool_table} \
SET \
expiry_time = CURRENT_TIMESTAMP - INTERVAL '1' SECOND(1) \
WHERE nasipaddress = '%{NAS-IP-Address}'"
|