diff options
Diffstat (limited to 'raddb/mods-config/sql/ippool-dhcp/oracle')
| -rw-r--r-- | raddb/mods-config/sql/ippool-dhcp/oracle/procedure.sql | 217 | ||||
| -rw-r--r-- | raddb/mods-config/sql/ippool-dhcp/oracle/queries.conf | 200 | ||||
| -rw-r--r-- | raddb/mods-config/sql/ippool-dhcp/oracle/schema.sql | 28 |
3 files changed, 445 insertions, 0 deletions
diff --git a/raddb/mods-config/sql/ippool-dhcp/oracle/procedure.sql b/raddb/mods-config/sql/ippool-dhcp/oracle/procedure.sql new file mode 100644 index 0000000..84b4596 --- /dev/null +++ b/raddb/mods-config/sql/ippool-dhcp/oracle/procedure.sql @@ -0,0 +1,217 @@ +-- +-- 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_dhcp_allocate_previous_or_new_framedipaddress( \ +-- '%{control:${pool_name}}', \ +-- '%{DHCP-Gateway-IP-Address}', \ +-- '${pool_key}', \ +-- ${lease_duration}, \ +-- '%{%{${req_attribute_name}}:-0.0.0.0}' \ +-- ) FROM dual" +-- allocate_update = "" +-- allocate_commit = "" +-- + +CREATE OR REPLACE FUNCTION fr_dhcp_allocate_previous_or_new_framedipaddress ( + v_pool_name IN VARCHAR2, + v_gateway IN VARCHAR2, + v_pool_key IN VARCHAR2, + v_lease_duration IN INTEGER, + v_requested_address IN VARCHAR2 +) +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 dhcpippool WHERE id IN ( + SELECT id FROM ( + SELECT * + FROM dhcpippool + JOIN dhcpstatus + ON dhcpstatus.status_id = dhcpippool.status_id + WHERE pool_name = v_pool_name + AND expiry_time > current_timestamp + AND pool_key = v_pool_key + AND dhcpstatus.status IN ('dynamic', 'static') + ) WHERE ROWNUM <= 1 + ) FOR UPDATE SKIP LOCKED; + EXCEPTION + WHEN NO_DATA_FOUND THEN + r_address := NULL; + END; + + -- Oracle >= 12c version of the above query + -- + -- BEGIN + -- SELECT framedipaddress INTO r_address FROM dhcpippool WHERE id IN ( + -- SELECT id FROM dhcpippool + -- JOIN dhcpstatus + -- ON dhcpstatus.status_id = dhcpippool.status_id + -- WHERE pool_name = v_pool_name + -- AND expiry_time > current_timestamp + -- AND pool_key = v_pool_key + -- AND dhcpstatus.status IN ('dynamic', 'static') + -- FETCH FIRST 1 ROWS ONLY + -- ) 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 dhcpippool WHERE id IN ( + -- SELECT id FROM ( + -- SELECT * + -- FROM dhcpippool + -- JOIN dhcpstatus + -- ON dhcpstatus.status_id = dhcpippool.status_id + -- WHERE pool_name = v_pool_name + -- AND pool_key = v_pool_key + -- AND dhcpstatus.status IN ('dynamic', 'static') + -- ) WHERE ROWNUM <= 1 + -- ) FOR UPDATE SKIP LOCKED; + -- EXCEPTION + -- WHEN NO_DATA_FOUND THEN + -- r_address := NULL; + -- END; + + -- Oracle >= 12c version of the above query + -- + -- BEGIN + -- SELECT framedipaddress INTO r_address FROM dhcpippool WHERE id IN ( + -- SELECT id FROM dhcpippool + -- JOIN dhcpstatus + -- ON dhcpstatus.status_id = dhcpippool.status_id + -- WHERE pool_name = v_pool_name + -- AND pool_key = v_pool_key + -- AND dhcpstatus.status IN ('dynamic', 'static') + -- FETCH FIRST 1 ROWS ONLY + -- ) FOR UPDATE SKIP LOCKED; + -- EXCEPTION + -- WHEN NO_DATA_FOUND THEN + -- r_address := NULL; + -- END; + + + + -- Issue the requested IP address if it is available + -- + IF r_address IS NULL AND v_requested_address <> '0.0.0.0' THEN + BEGIN + SELECT framedipaddress INTO r_address FROM dhcpippool WHERE id IN ( + SELECT id FROM ( + SELECT * + FROM dhcpippool + JOIN dhcpstatus + ON dhcpstatus.status_id = dhcpippool.status_id + WHERE pool_name = v_pool_name + AND framedipaddress = v_requested_address + AND dhcpstatus.status = 'dynamic' + AND expiry_time < CURRENT_TIMESTAMP + ) WHERE ROWNUM <= 1 + ) FOR UPDATE SKIP LOCKED; + EXCEPTION + WHEN NO_DATA_FOUND THEN + r_address := NULL; + END; + END IF; + + -- Oracle >= 12c version of the above query + -- + -- IF r_address IS NULL AND v_requested_address <> '0.0.0.0' THEN + -- BEGIN + -- SELECT framedipaddress INTO r_address FROM dhcpippool WHERE id IN ( + -- SELECT id FROM dhcpippool + -- JOIN dhcpstatus + -- ON dhcpstatus.status_id = dhcpippool.status_id + -- WHERE pool_name = v_pool_name + -- AND framedipaddress = v_requested_address + -- AND dhcpstatus.status = 'dynamic' + -- AND expiry_time < CURRENT_TIMESTAMP + -- FETCH FIRST 1 ROWS ONLY + -- ) FOR UPDATE SKIP LOCKED; + -- EXCEPTION + -- WHEN NO_DATA_FOUND THEN + -- r_address := NULL; + -- END; + -- END IF; + + + + -- 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 + DECLARE + l_cursor sys_refcursor; + BEGIN + OPEN l_cursor FOR + SELECT framedipaddress + FROM dhcpippool + JOIN dhcpstatus + ON dhcpstatus.status_id = dhcpippool.status_id + WHERE pool_name = v_pool_name + AND expiry_time < CURRENT_TIMESTAMP + AND dhcpstatus.status = 'dynamic' + ORDER BY expiry_time + FOR UPDATE SKIP LOCKED; + FETCH l_cursor INTO r_address; + CLOSE l_cursor; + 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 dhcpippool + SET + gateway = v_gateway, + pool_key = v_pool_key, + 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-dhcp/oracle/queries.conf b/raddb/mods-config/sql/ippool-dhcp/oracle/queries.conf new file mode 100644 index 0000000..0fcffc3 --- /dev/null +++ b/raddb/mods-config/sql/ippool-dhcp/oracle/queries.conf @@ -0,0 +1,200 @@ +# -*- text -*- +# +# ippool-dhcp/oracle/queries.conf -- Oracle queries for rlm_sqlippool +# +# $Id$ + +start_begin = "commit" +alive_begin = "commit" +stop_begin = "commit" +on_begin = "commit" +off_begin = "commit" + + +# ***************** +# * DHCP DISCOVER * +# ***************** + +# +# Use a stored procedure to find AND allocate the address. Read and customise +# `procedure.sql` in this directory to determine the optimal configuration. +# Oracle's locking mechanism limitations prevents the use of single queries +# that can either find a client's existing address or the first available one. +# +allocate_begin = "" +allocate_find = "\ + SELECT fr_dhcp_allocate_previous_or_new_framedipaddress( \ + '%{control:${pool_name}}', \ + '%{DHCP-Gateway-IP-Address}', \ + '${pool_key}', \ + '${offer_duration}', \ + '%{%{${req_attribute_name}}:-0.0.0.0}' \ + ) FROM dual" +allocate_update = "" +allocate_commit = "" + + +# +# 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} \ +# JOIN dhcpstatus \ +# ON ${ippool_table}.status_id = dhcpstatus.status_id \ +# WHERE pool_name = '%{control:${pool_name}}' \ +# AND expiry_time < current_timestamp \ +# AND dhcpstatus.status = 'dynamic' \ +# ORDER BY DBMS_RANDOM.VALUE \ +# ) WHERE ROWNUM <= 1 \ +# ) FOR UPDATE" + +# +# The above query again, but with SKIP LOCKED. This requires Oracle > 11g. +# It may work in 9i and 10g, but is not documented, so YMMV. +# +#allocate_find = "\ +# SELECT framedipaddress FROM ${ippool_table} WHERE id IN ( \ +# SELECT id FROM (\ +# SELECT * \ +# FROM ${ippool_table} \ +# JOIN dhcpstatus \ +# ON ${ippool_table}.status_id = dhcpstatus.status_id \ +# WHERE pool_name = '%{control:${pool_name}}' \ +# AND expiry_time < current_timestamp \ +# AND dhcpstatus.status = 'dynamic' \ +# ORDER BY DBMS_RANDOM.VALUE \ +# ) WHERE ROWNUM <= 1 \ +# ) FOR UPDATE SKIP LOCKED" + +# +# A tidier version that needs Oracle >= 12c +# +#allocate_find = "\ +# SELECT framedipaddress FROM ${ippool_table} WHERE id IN ( +# SELECT id FROM ${ippool_table} \ +# JOIN dhcpstatus \ +# ON ${ippool_table}.status_id = dhcpstatus.status_id \ +# WHERE pool_name = '%{control:${pool_name}}' \ +# AND expiry_time < current_timestamp \ +# AND dhcpstatus.status = 'dynamic' \ +# ORDER BY DBMS_RANDOM.VALUE \ +# FETCH FIRST 1 ROWS ONLY +# ) 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 "offer_duration" after which time it may be reused. +# Only needed if allocate_find is not using the stored procedure and therefore +# not updating the lease +# +#allocate_update = "\ +# UPDATE ${ippool_table} \ +# SET \ +# gateway = '%{DHCP-Gateway-IP-Address}', \ +# pool_key = '${pool_key}', \ +# expiry_time = current_timestamp + INTERVAL '${offer_duration}' second(1) \ +# WHERE framedipaddress = '%I'" + + +# **************** +# * DHCP REQUEST * +# **************** + +# +# This query revokes any active offers for addresses that a client is not +# requesting when a DHCP REQUEST packet arrives +# +start_update = "\ + UPDATE ${ippool_table} \ + SET \ + gateway = '', \ + pool_key = '0', \ + expiry_time = current_timestamp - INTERVAL '1' second(1) \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND pool_key = '${pool_key}' \ + AND framedipaddress <> '%{DHCP-Requested-IP-Address}' \ + AND expiry_time > current_timestamp \ + AND ${ippool_table}.status_id IN \ + (SELECT status_id FROM dhcpstatus WHERE status = 'dynamic')" +start_commit = "COMMIT" + +# +# This query extends an existing lease (or offer) when a DHCP REQUEST packet +# arrives. This query must update a row when a lease is succesfully requested +# - queries that update no rows will result in a "notfound" response to +# the module which by default will give a DHCP-NAK reply. In this example +# incrementing "counter" is used to achieve this. +# +alive_update = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = current_timestamp + INTERVAL '${lease_duration}' second(1), \ + counter = counter + 1 \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND pool_key = '${pool_key}' \ + AND framedipaddress = '%{%{DHCP-Requested-IP-Address}:-%{DHCP-Client-IP-Address}}'" +alive_commit = "COMMIT" + + +# **************** +# * DHCP RELEASE * +# **************** + +# +# This query frees an IP address when a DHCP RELEASE packet arrives +# +stop_clear = "\ + UPDATE ${ippool_table} \ + SET \ + gateway = '', \ + pool_key = '0', \ + expiry_time = current_timestamp - INTERVAL '1' second(1) \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND pool_key = '${pool_key}' \ + AND framedipaddress = '%{DHCP-Client-IP-Address}' \ + AND ${ippool_table}.status_id IN \ + (SELECT status_id FROM dhcpstatus WHERE status = 'dynamic')" +stop_commit = "COMMIT" + + +# +# This query is not applicable to DHCP +# +on_clear = "" + + +# **************** +# * DHCP DECLINE * +# **************** + +# +# This query marks an IP address as declined when a DHCP Decline +# packet arrives +# +off_clear = "\ + UPDATE ${ippool_table} \ + SET status_id = (SELECT status_id FROM dhcpstatus WHERE status = 'declined') \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND pool_key = '${pool_key}' \ + AND framedipaddress = '%{DHCP-Requested-IP-Address}'" +off_commit = "COMMIT" + diff --git a/raddb/mods-config/sql/ippool-dhcp/oracle/schema.sql b/raddb/mods-config/sql/ippool-dhcp/oracle/schema.sql new file mode 100644 index 0000000..32d28bb --- /dev/null +++ b/raddb/mods-config/sql/ippool-dhcp/oracle/schema.sql @@ -0,0 +1,28 @@ +CREATE TABLE dhcpstatus ( + status_id INT PRIMARY KEY, + status VARCHAR(10) NOT NULL +); + +INSERT INTO dhcpstatus (status_id, status) VALUES (1, 'dynamic'); +INSERT INTO dhcpstatus (status_id, status) VALUES (2, 'static'); +INSERT INTO dhcpstatus (status_id, status) VALUES (3, 'declined'); +INSERT INTO dhcpstatus (status_id, status) VALUES (4, 'disabled'); + +CREATE SEQUENCE dhcpippool_seq START WITH 1 INCREMENT BY 1; + +CREATE TABLE dhcpippool ( + id INT DEFAULT ON NULL dhcpippool_seq.NEXTVAL PRIMARY KEY, + pool_name VARCHAR(30) NOT NULL, + framedipaddress VARCHAR(15) NOT NULL, + pool_key VARCHAR(30) DEFAULT '', + gateway VARCHAR(15) DEFAULT '', + expiry_time timestamp(0) DEFAULT CURRENT_TIMESTAMP, + status_id INT DEFAULT 1, + counter INT DEFAULT 0, + FOREIGN KEY (status_id) REFERENCES dhcpstatus(status_id) +); + +CREATE INDEX dhcpippool_poolname_expire ON dhcpippool (pool_name, expiry_time); +CREATE INDEX dhcpippool_framedipaddress ON dhcpippool (framedipaddress); +CREATE INDEX dhcpippool_poolname_poolkey_ipaddress ON dhcpippool (pool_name, pool_key, framedipaddress); + |