diff options
Diffstat (limited to 'raddb/mods-config/sql/ippool-dhcp/mssql')
| -rw-r--r-- | raddb/mods-config/sql/ippool-dhcp/mssql/procedure.sql | 159 | ||||
| -rw-r--r-- | raddb/mods-config/sql/ippool-dhcp/mssql/queries.conf | 257 | ||||
| -rw-r--r-- | raddb/mods-config/sql/ippool-dhcp/mssql/schema.sql | 40 |
3 files changed, 456 insertions, 0 deletions
diff --git a/raddb/mods-config/sql/ippool-dhcp/mssql/procedure.sql b/raddb/mods-config/sql/ippool-dhcp/mssql/procedure.sql new file mode 100644 index 0000000..4cfbe1c --- /dev/null +++ b/raddb/mods-config/sql/ippool-dhcp/mssql/procedure.sql @@ -0,0 +1,159 @@ +-- +-- 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 TRAN; "SELECT FOR UPDATE"; UPDATE; COMMIT TRAN; -> EXEC sp +-- +-- 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 = "\ +-- EXEC fr_dhcp_allocate_previous_or_new_framedipaddress \ +-- @v_pool_name = '%{control:${pool_name}}', \ +-- @v_gateway = '%{DHCP-Gateway-IP-Address}', \ +-- @v_pool_key = '${pool_key}', \ +-- @v_lease_duration = ${lease_duration}, \ +-- @v_requested_address = '%{%{${req_attribute_name}}:-0.0.0.0}' \ +-- " +-- allocate_update = "" +-- allocate_commit = "" +-- + +CREATE OR ALTER PROCEDURE fr_dhcp_allocate_previous_or_new_framedipaddress + @v_pool_name VARCHAR(64), + @v_gateway VARCHAR(15), + @v_pool_key VARCHAR(64), + @v_lease_duration INT, + @v_requested_address VARCHAR(15) +AS + BEGIN + + -- MS SQL lacks a "SELECT FOR UPDATE" statement, and its table + -- hints do not provide a direct means to implement the row-level + -- read lock needed to guarentee that concurrent queries do not + -- select the same Framed-IP-Address for allocation to distinct + -- users. + -- + -- The "WITH cte AS ( SELECT ... ) UPDATE cte ... OUTPUT INTO" + -- patterns in this procedure body compensate by wrapping + -- the SELECT in a synthetic UPDATE which locks the row. + + DECLARE @r_address_tab TABLE(id VARCHAR(15)); + DECLARE @r_address VARCHAR(15); + + BEGIN TRAN; + + -- Reissue an existing IP address lease when re-authenticating a session + -- + WITH cte AS ( + SELECT TOP(1) FramedIPAddress + FROM dhcpippool WITH (rowlock, readpast) + 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') + ) + UPDATE cte + SET FramedIPAddress = FramedIPAddress + OUTPUT INSERTED.FramedIPAddress INTO @r_address_tab; + SELECT @r_address = id FROM @r_address_tab; + + -- 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. + -- + -- WITH cte AS ( + -- SELECT TOP(1) FramedIPAddress + -- FROM dhcpippool WITH (rowlock, readpast) + -- 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') + -- ) + -- UPDATE cte + -- SET FramedIPAddress = FramedIPAddress + -- OUTPUT INSERTED.FramedIPAddress INTO @r_address_tab; + -- SELECT @r_address = id FROM @r_address_tab; + + -- Issue the requested IP address if it is available + -- + IF @r_address IS NULL AND @v_requested_address <> '0.0.0.0' + BEGIN + WITH cte AS ( + SELECT TOP(1) FramedIPAddress + FROM dhcpippool WITH (rowlock, readpast) + 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 ( pool_key = @v_pool_name OR expiry_time < CURRENT_TIMESTAMP ) + ) + UPDATE cte + SET FramedIPAddress = FramedIPAddress + OUTPUT INSERTED.FramedIPAddress INTO @r_address_tab; + SELECT @r_address = id FROM @r_address_tab; + 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 + BEGIN + WITH cte AS ( + SELECT TOP(1) FramedIPAddress + FROM dhcpippool WITH (rowlock, readpast) + 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 + ) + UPDATE cte + SET FramedIPAddress = FramedIPAddress + OUTPUT INSERTED.FramedIPAddress INTO @r_address_tab; + SELECT @r_address = id FROM @r_address_tab; + END + + -- Return nothing if we failed to allocated an address + -- + IF @r_address IS NULL + BEGIN + COMMIT TRAN; + RETURN; + END + + -- Update the pool having allocated an IP address + -- + UPDATE dhcpippool + SET + gateway = @v_gateway, + pool_key = @v_pool_key, + expiry_time = DATEADD(SECOND,@v_lease_duration,CURRENT_TIMESTAMP) + WHERE framedipaddress = @r_address; + + COMMIT TRAN; + + -- Return the address that we allocated + SELECT @r_address; + + END +GO diff --git a/raddb/mods-config/sql/ippool-dhcp/mssql/queries.conf b/raddb/mods-config/sql/ippool-dhcp/mssql/queries.conf new file mode 100644 index 0000000..c919e2d --- /dev/null +++ b/raddb/mods-config/sql/ippool-dhcp/mssql/queries.conf @@ -0,0 +1,257 @@ +# -*- text -*- +# +# ippool-dhcp/mssql/queries.conf -- MSSQL queries for rlm_sqlippool +# +# $Id$ + +# ***************** +# * DHCP DISCOVER * +# ***************** + +# +# This series of queries allocates an IP address +# + +# +# MSSQL-specific syntax - required if finding the address and updating +# it are separate queries +# +#allocate_begin = "BEGIN TRAN" +#allocate_commit = "COMMIT TRAN" + +allocate_begin = "" +allocate_commit = "" + +# +# Attempt to find the most recent existing IP address for the client +# +allocate_existing = "\ + WITH cte AS ( \ + SELECT TOP(1) framedipaddress, expiry_time, gateway \ + FROM ${ippool_table} WITH (xlock rowlock readpast) \ + JOIN dhcpstatus ON ${ippool_table}.status_id = dhcpstatus.status_id \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND pool_key = '${pool_key}' \ + AND dhcpstatus.status IN ('dynamic', 'static') \ + ORDER BY expiry_time DESC \ + ) \ + UPDATE cte \ + SET expiry_time = DATEADD(SECOND,${offer_duration},CURRENT_TIMESTAMP), \ + gateway = '%{DHCP-Gateway-IP-Address}' \ + OUTPUT INSERTED.FramedIPAddress \ + FROM ${ippool_table}" + +# +# Determine whether the requested IP address is available +# +allocate_requested = "\ + WITH cte AS ( \ + SELECT TOP(1) framedipaddress, expiry_time, gateway \ + FROM ${ippool_table} WITH (xlock rowlock readpast) \ + JOIN dhcpstatus ON ${ippool_table}.status_id = dhcpstatus.status_id \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND framedipaddress = '%{%{${req_attribute_name}}:-0.0.0.0}' \ + AND dhcpstatus.status = 'dynamic' \ + AND expiry_time < CURRENT_TIMESTAMP \ + ) \ + UPDATE cte \ + SET expiry_time = DATEADD(SECOND,${offer_duration},CURRENT_TIMESTAMP), \ + gateway = '%{DHCP-Gateway-IP-Address}', \ + pool_key = '${pool_key}' \ + OUTPUT INSERTED.FramedIPAddress \ + FROM ${ippool_table}" + +# +# If the existing address can't be found this query will be run to +# find a free address +# +allocate_find = "\ + WITH cte AS ( \ + SELECT TOP(1) framedipaddress, expiry_time, gateway, pool_key \ + FROM ${ippool_table} WITH (xlock rowlock readpast) \ + 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 expiry_time \ + ) \ + UPDATE cte \ + SET expiry_time = DATEADD(SECOND,${offer_duration},CURRENT_TIMESTAMP), \ + gateway = '%{DHCP-Gateway-IP-Address}', \ + pool_key = '${pool_key}' \ + OUTPUT INSERTED.FramedIPAddress \ + FROM ${ippool_table}" + +# +# Alternatively attempt all in one, more complex, query +# +# The ORDER BY clause of this query tries to allocate the same IP-address +# which user had last session. Ensure that pool_key is unique to the user +# within a given pool. +# +#allocate_find = "\ +# UPDATE TOP(1) ${ippool_table} \ +# SET FramedIPAddress = FramedIPAddress, \ +# pool_key = '${pool_key}', \ +# expiry_time = DATEADD(SECOND,${offer_duration},CURRENT_TIMESTAMP), \ +# GatewayIPAddress = '%{DHCP-Gateway-IP-Address}' \ +# OUTPUT INSERTED.FramedIPAddress \ +# FROM ${ippool_table} \ +# WHERE ${ippool_table}.id IN ( \ +# SELECT TOP (1) id FROM ( \ +# (SELECT TOP(1) id, 1 AS o FROM ${ippool_table} WITH (xlock rowlock readpast) \ +# JOIN dhcpstatus ON ${ippool_table}.status_id = dhcpstatus.status_id \ +# WHERE pool_name = '%{control:${pool_name}}' \ +# AND pool_key = '${pool_key}' \ +# AND dhcpstatus.status IN ('dynamic', 'static')) \ +# UNION \ +# (SELECT TOP(1) id, 2 AS o FROM ${ippool_table} WITH (xlock rowlock readpast) \ +# JOIN dhcpstatus ON ${ippool_table}.status_id = dhcpstatus.status_id \ +# WHERE pool_name = '%{control:${pool_name}}' \ +# AND framedipaddress = '%{%{${req_attribute_name}}:-0.0.0.0}' \ +# AND dhcpstatus.status = 'dynamic' \ +# AND ( pool_key = '%{pool_key}' OR expiry_time < CURRENT_TIMESTAMP )) \ +# UNION \ +# (SELECT TOP(1) id, 3 AS o FROM ${ippool_table} WITH (xlock rowlock readpast) \ +# 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 expiry_time) \ +# ) AS q ORDER BY q.o \ +# )" + +# +# 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 = "\ +# WITH cte AS ( \ +# SELECT TOP(1) FramedIPAddress 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 \ +# newid() \ +# ) \ +# UPDATE cte WITH (rowlock, readpast) \ +# SET FramedIPAddress = FramedIPAddress \ +# OUTPUT INSERTED.FramedIPAddress" + +# +# If an IP could not be allocated, check to see if 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 TOP(1) id \ + FROM ${ippool_table} \ + WHERE pool_name='%{control:${pool_name}}'" + +# +# This is the final IP Allocation query, which saves the allocated ip details. +# Only needed if the initial "find" query is not storing the allocation. +# +#allocate_update = "\ +# UPDATE ${ippool_table} \ +# SET \ +# gateway = '%{DHCP-Gateway-IP-Address}', pool_key = '${pool_key}', \ +# expiry_time = DATEADD(SECOND,${offer_duration},CURRENT_TIMESTAMP) \ +# 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 = "\ +# EXEC fr_dhcp_allocate_previous_or_new_framedipaddress \ +# @v_pool_name = '%{control:${pool_name}}', \ +# @v_gateway = '%{DHCP-Gateway-IP-Address}', \ +# @v_pool_key = '${pool_key}', \ +# @v_lease_duration = ${offer_duration}, \ +# @v_requested_address = '%{%{${req_attribute_name}}:-0.0.0.0}' \ +# " +#allocate_update = "" +#allocate_commit = "" + + +# **************** +# * 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 = '', \ + expiry_time = CURRENT_TIMESTAMP \ + 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')" + +# +# 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 = DATEADD(SECOND,${lease_duration},CURRENT_TIMESTAMP), \ + counter = counter + 1 \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND pool_key = '${pool_key}' \ + AND framedipaddress = '%{%{DHCP-Requested-IP-Address}:-%{DHCP-Client-IP-Address}}'" + + +# **************** +# * DHCP RELEASE * +# **************** + +# +# This query frees an IP address when a DHCP RELEASE packet arrives +# +stop_clear = "\ + UPDATE ${ippool_table} \ + SET \ + gateway = '', \ + pool_key = '', \ + expiry_time = CURRENT_TIMESTAMP \ + 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')" + +# +# 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}'" diff --git a/raddb/mods-config/sql/ippool-dhcp/mssql/schema.sql b/raddb/mods-config/sql/ippool-dhcp/mssql/schema.sql new file mode 100644 index 0000000..dae4eff --- /dev/null +++ b/raddb/mods-config/sql/ippool-dhcp/mssql/schema.sql @@ -0,0 +1,40 @@ +-- +-- Table structure for table 'dhcpippool' +-- +-- See also "procedure.sql" in this directory for +-- a stored procedure that gives much faster response. +-- + +CREATE TABLE dhcpstatus ( + status_id int NOT NULL, + status varchar(10) NOT NULL, + PRIMARY KEY (status_id) +) +GO + +INSERT INTO dhcpstatus (status_id, status) VALUES (1, 'dynamic'), (2, 'static'), (3, 'declined'), (4, 'disabled') +GO + +CREATE TABLE dhcpippool ( + id int IDENTITY (1,1) NOT NULL, + pool_name varchar(30) NOT NULL, + FramedIPAddress varchar(15) NOT NULL default '', + pool_key varchar(30) NOT NULL default '', + gateway varchar(15) NOT NULL default '', + expiry_time DATETIME NOT NULL default CURRENT_TIMESTAMP, + status_id int NOT NULL default 1, + counter int NOT NULL default 0, + CONSTRAINT fk_status_id FOREIGN KEY (status_id) REFERENCES dhcpstatus (status_id), + PRIMARY KEY (id) +) +GO + +CREATE INDEX dhcp_poolname_expire ON dhcpippool(pool_name, expiry_time) +GO + +CREATE INDEX dhcp_FramedIPAddress ON dhcpippool(FramedIPAddress) +GO + +CREATE INDEX dhcp_poolname_poolkey_FramedIPAddress ON dhcpippool(pool_name, pool_key, FramedIPAddress) +GO + |