diff options
Diffstat (limited to 'raddb/mods-config/sql/ippool/oracle')
| -rw-r--r-- | raddb/mods-config/sql/ippool/oracle/procedure.sql | 129 | ||||
| -rw-r--r-- | raddb/mods-config/sql/ippool/oracle/queries.conf | 172 | ||||
| -rw-r--r-- | raddb/mods-config/sql/ippool/oracle/schema.sql | 27 |
3 files changed, 328 insertions, 0 deletions
diff --git a/raddb/mods-config/sql/ippool/oracle/procedure.sql b/raddb/mods-config/sql/ippool/oracle/procedure.sql new file mode 100644 index 0000000..e483236 --- /dev/null +++ b/raddb/mods-config/sql/ippool/oracle/procedure.sql @@ -0,0 +1,129 @@ +-- +-- A stored procedure to reallocate a user's previous address, otherwise +-- provide a free address. +-- +-- Using this SP reduces the usual set dialogue of queries to a single +-- query: +-- +-- BEGIN; SELECT FOR UPDATE; UPDATE; COMMIT; -> SELECT sp() FROM dual +-- +-- The stored procedure is executed on an database instance within a single +-- round trip which often leads to reduced deadlocking and significant +-- performance improvements especially on multi-master clusters, perhaps even +-- by an order of magnitude or more. +-- +-- To use this stored procedure the corresponding queries.conf statements must +-- be configured as follows: +-- +-- allocate_begin = "" +-- allocate_find = "\ +-- SELECT fr_allocate_previous_or_new_framedipaddress( \ +-- '%{control:${pool_name}}', \ +-- '%{User-Name}', \ +-- '%{%{Calling-Station-Id}:-0}', \ +-- '%{NAS-IP-Address}', \ +-- '${pool_key}', \ +-- ${lease_duration} \ +-- ) FROM dual" +-- allocate_update = "" +-- allocate_commit = "" +-- + +CREATE OR REPLACE FUNCTION fr_allocate_previous_or_new_framedipaddress ( + v_pool_name IN VARCHAR2, + v_username IN VARCHAR2, + v_callingstationid IN VARCHAR2, + v_nasipaddress IN VARCHAR2, + v_pool_key IN VARCHAR2, + v_lease_duration IN INTEGER +) +RETURN varchar2 IS + PRAGMA AUTONOMOUS_TRANSACTION; + r_address varchar2(15); +BEGIN + + -- Reissue an existing IP address lease when re-authenticating a session + -- + BEGIN + SELECT framedipaddress INTO r_address FROM radippool WHERE id IN ( + SELECT id FROM ( + SELECT * + FROM radippool + WHERE pool_name = v_pool_name + AND expiry_time > current_timestamp + AND username = v_username + AND callingstationid = v_callingstationid + ) WHERE ROWNUM <= 1 + ) FOR UPDATE SKIP LOCKED; + EXCEPTION + WHEN NO_DATA_FOUND THEN + r_address := NULL; + END; + + -- Reissue an user's previous IP address, provided that the lease is + -- available (i.e. enable sticky IPs) + -- + -- When using this SELECT you should delete the one above. You must also + -- set allocate_clear = "" in queries.conf to persist the associations + -- for expired leases. + -- + -- BEGIN + -- SELECT framedipaddress INTO r_address FROM radippool WHERE id IN ( + -- SELECT id FROM ( + -- SELECT * + -- FROM radippool + -- WHERE pool_name = v_pool_name + -- AND username = v_username + -- AND callingstationid = v_callingstationid + -- ) WHERE ROWNUM <= 1 + -- ) FOR UPDATE SKIP LOCKED; + -- EXCEPTION + -- WHEN NO_DATA_FOUND THEN + -- r_address := NULL; + -- END; + + -- If we didn't reallocate a previous address then pick the least + -- recently used address from the pool which maximises the likelihood + -- of re-assigning the other addresses to their recent user + -- + IF r_address IS NULL THEN + BEGIN + SELECT framedipaddress INTO r_address FROM radippool WHERE id IN ( + SELECT id FROM ( + SELECT * + FROM radippool + WHERE pool_name = v_pool_name + AND expiry_time < CURRENT_TIMESTAMP + ORDER BY expiry_time + ) WHERE ROWNUM <= 1 + ) FOR UPDATE SKIP LOCKED; + EXCEPTION + WHEN NO_DATA_FOUND THEN + r_address := NULL; + END; + END IF; + + -- Return nothing if we failed to allocated an address + -- + IF r_address IS NULL THEN + COMMIT; + RETURN r_address; + END IF; + + -- Update the pool having allocated an IP address + -- + UPDATE radippool + SET + nasipaddress = v_nasipaddress, + pool_key = v_pool_key, + callingstationid = v_callingstationid, + username = v_username, + expiry_time = CURRENT_TIMESTAMP + v_lease_duration * INTERVAL '1' SECOND(1) + WHERE framedipaddress = r_address; + + -- Return the address that we allocated + COMMIT; + RETURN r_address; + +END; +/ diff --git a/raddb/mods-config/sql/ippool/oracle/queries.conf b/raddb/mods-config/sql/ippool/oracle/queries.conf new file mode 100644 index 0000000..1a64b28 --- /dev/null +++ b/raddb/mods-config/sql/ippool/oracle/queries.conf @@ -0,0 +1,172 @@ +# -*- text -*- +# +# ippool/oracle/queries.conf -- Oracle queries for rlm_sqlippool +# +# $Id$ + +# Using SKIP LOCKED speeds up selection queries +# However, it requires Oracle > 11g. It MAY work in 9i and 10g +# but is not documented. Uncomment the following if you are +# running a suitable version of Oracle +# +#skip_locked = "SKIP LOCKED" +skip_locked = "" + +allocate_begin = "commit" +start_begin = "commit" +alive_begin = "commit" +stop_begin = "commit" +on_begin = "commit" +off_begin = "commit" + +# +# Attempt to allocate the address a client previously had. This is based on pool_key +# and nasipaddress. Change the criteria if the identifier for "stickyness" is different. +# If different criteria are used, check the indexes on the IP pool table to ensure the fields +# are appropriately indexed. To disable stickyness comment out this query. +# +allocate_existing = "\ + SELECT framedipaddress FROM ${ippool_table} WHERE id IN ( \ + SELECT id FROM ( \ + SELECT * \ + FROM ${ippool_table} \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool_key}' \ + ) \ + ORDER BY expiry_time DESC \ + ) WHERE ROWNUM <= 1 \ + ) FOR UPDATE ${skip_locked}" + +# +# Find a free IP address from the pool, choosing the oldest expired one. +# +allocate_find = "\ + SELECT framedipaddress FROM ${ippool_table} WHERE id IN ( \ + SELECT id FROM ( \ + SELECT * \ + FROM ${ippool_table} \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND expiry_time < current_timestamp \ + ) \ + ORDER BY expiry_time \ + ) WHERE ROWNUM <= 1 \ + ) FOR UPDATE ${skip_locked}" + +# +# If you prefer to allocate a random IP address every time, use this query instead +# Note: This is very slow if you have a lot of free IPs. +# +#allocate_find = "\ +# SELECT framedipaddress FROM ${ippool_table} WHERE id IN ( \ +# SELECT id FROM ( \ +# SELECT * \ +# FROM ${ippool_table} \ +# WHERE pool_name = '%{control:${pool_name}}' \ +# AND expiry_time < current_timestamp \ +# ORDER BY DBMS_RANDOM.VALUE \ +# ) WHERE ROWNUM <= 1 \ +# ) FOR UPDATE ${skip_locked}" + +# +# 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}:-0}', \ + username = '%{SQL-User-Name}', \ + expiry_time = current_timestamp + INTERVAL '${lease_duration}' second(1) \ + WHERE framedipaddress = '%I'" + +# +# Use a stored procedure to find AND allocate the address. Read and customise +# `procedure.sql` in this directory to determine the optimal configuration. +# +#allocate_begin = "" +#allocate_find = "\ +# SELECT fr_allocate_previous_or_new_framedipaddress( \ +# '%{control:${pool_name}}', \ +# '%{SQL-User-Name}', \ +# '%{%{Calling-Station-Id}:-0}', \ +# '%{NAS-IP-Address}', \ +# '${pool_key}', \ +# '${lease_duration}' \ +# )" +#allocate_update = "" +#allocate_commit = "" + +# +# 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_key = '${pool_key}'" + +# +# This query expires an IP address when an accounting STOP record arrives +# +stop_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = current_timestamp - INTERVAL '1' second(1) \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \ + AND pool_key = '${pool_key}' \ + AND username = '%{SQL-User-Name}' \ + AND callingstationid = '%{%{Calling-Station-Id}:-0}' \ + AND framedipaddress = '%{${attribute_name}}'" + +# +# 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 nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \ + AND pool_key = '${pool_key}' \ + AND framedipaddress = '%{${attribute_name}}' \ + AND username = '%{SQL-User-Name}' \ + AND callingstationid = '%{%{Calling-Station-Id}:-0}'" + +# +# This query expires 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}:-%{Nas-IPv6-Address}}'" + +# +# This query expires 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}:-%{Nas-IPv6-Address}}'" diff --git a/raddb/mods-config/sql/ippool/oracle/schema.sql b/raddb/mods-config/sql/ippool/oracle/schema.sql new file mode 100644 index 0000000..adf1419 --- /dev/null +++ b/raddb/mods-config/sql/ippool/oracle/schema.sql @@ -0,0 +1,27 @@ +CREATE TABLE radippool ( + id INT PRIMARY KEY, + pool_name VARCHAR(30) NOT NULL, + framedipaddress VARCHAR(15) NOT NULL, + nasipaddress VARCHAR(15) NOT NULL, + pool_key VARCHAR(30) DEFAULT '', + CalledStationId VARCHAR(64) DEFAULT '', + CallingStationId VARCHAR(64) DEFAULT '', + expiry_time timestamp(0) DEFAULT CURRENT_TIMESTAMP, + username VARCHAR(64) DEFAULT '' +); + +CREATE INDEX radippool_poolname_expire ON radippool (pool_name, expiry_time); +CREATE INDEX radippool_framedipaddress ON radippool (framedipaddress); +CREATE INDEX radippool_nasip_poolkey_ipaddress ON radippool (nasipaddress, pool_key, framedipaddress); + +CREATE SEQUENCE radippool_seq START WITH 1 INCREMENT BY 1; + +CREATE OR REPLACE TRIGGER radippool_serialnumber + BEFORE INSERT OR UPDATE OF id ON radippool + FOR EACH ROW + BEGIN + if ( :new.id = 0 or :new.id is null ) then + SELECT radippool_seq.nextval into :new.id from dual; + end if; + END; +/ |