diff options
Diffstat (limited to 'raddb/mods-config/sql/dhcp/mssql')
| -rw-r--r-- | raddb/mods-config/sql/dhcp/mssql/queries.conf | 52 | ||||
| -rw-r--r-- | raddb/mods-config/sql/dhcp/mssql/schema.sql | 91 |
2 files changed, 143 insertions, 0 deletions
diff --git a/raddb/mods-config/sql/dhcp/mssql/queries.conf b/raddb/mods-config/sql/dhcp/mssql/queries.conf new file mode 100644 index 0000000..8345c70 --- /dev/null +++ b/raddb/mods-config/sql/dhcp/mssql/queries.conf @@ -0,0 +1,52 @@ +# -*- text -*- +# +# dhcp/mssql/queries.conf -- MSSQL configuration for DHCP schema (schema.sql) +# +# $Id$ + +# Safe characters list for sql queries. Everything else is replaced +# with their mime-encoded equivalents. +# The default list should be ok +# safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /" + +####################################################################### +# Query config: Identifier +####################################################################### +# This is the identifier that will get substituted, escaped, and added +# as attribute 'SQL-User-Name'. '%{SQL-User-Name}' should be used +# below everywhere an identifier substitution is needed so you you can +# be sure the identifier passed from the client is escaped properly. +# +sql_user_name = "%{control:DHCP-SQL-Option-Identifier}" + +####################################################################### +# Attribute Lookup Queries +####################################################################### +# These queries setup the reply items in ${dhcpreply_table} and +# ${group_reply_query}. You can use any query/tables you want, but +# the return data for each row MUST be in the following order: +# +# 0. Row ID (currently unused) +# 1. Identifier +# 2. Item Attr Name +# 3. Item Attr Value +# 4. Item Attr Operation +####################################################################### + +authorize_reply_query = "\ + SELECT id, Identifier, Attribute, Value, op \ + FROM ${dhcpreply_table} \ + WHERE Identifier = '%{SQL-User-Name}' AND Context = '%{control:DHCP-SQL-Option-Context}' \ + ORDER BY id" + +authorize_group_reply_query = "\ + SELECT id, GroupName, Attribute, Value, op \ + FROM ${groupreply_table} \ + WHERE GroupName = '%{${group_attribute}}' AND Context = '%{control:DHCP-SQL-Option-Context}' \ + ORDER BY id" + +group_membership_query = "\ + SELECT GroupName \ + FROM ${dhcpgroup_table} \ + WHERE Identifier='%{SQL-User-Name}' AND Context = '%{control:DHCP-SQL-Option-Context}' \ + ORDER BY priority" diff --git a/raddb/mods-config/sql/dhcp/mssql/schema.sql b/raddb/mods-config/sql/dhcp/mssql/schema.sql new file mode 100644 index 0000000..8584949 --- /dev/null +++ b/raddb/mods-config/sql/dhcp/mssql/schema.sql @@ -0,0 +1,91 @@ +-- $Id$ +-- +-- MSSQL schema for DHCP for FreeRADIUS +-- +-- To load: +-- isql -S db_ip_addr -d db_name -U db_login -P db_passwd -i schema.sql + +-- +-- Table structure for table 'dhcpgroupreply' +-- +CREATE TABLE [dhcpgroupreply] ( + [id] [int] IDENTITY (1, 1) NOT NULL, + [GroupName] [varchar] (64) NOT NULL, + [Attribute] [varchar] (32) NOT NULL, + [Value] [varchar] (253) NOT NULL, + [op] [char] (2) NULL, + [prio] [int] NOT NULL, + [Context] [varchar] (16) NOT NULL +) ON [PRIMARY] +GO + +ALTER TABLE [dhcpgroupreply] WITH NOCHECK ADD + CONSTRAINT [DF_dhcpgroupreply_GroupName] DEFAULT ('') FOR [GroupName], + CONSTRAINT [DF_dhcpgroupreply_Attribute] DEFAULT ('') FOR [Attribute], + CONSTRAINT [DF_dhcpgroupreply_Value] DEFAULT ('') FOR [Value], + CONSTRAINT [DF_dhcpgroupreply_op] DEFAULT (null) FOR [op], + CONSTRAINT [DF_dhcpgroupreply_prio] DEFAULT (0) FOR [prio], + CONSTRAINT [DF_dhcpgroupreply_context] DEFAULT ('') FOR [Context], + CONSTRAINT [PK_dhcpgroupreply] PRIMARY KEY NONCLUSTERED + ( + [id] + ) ON [PRIMARY] +GO + +CREATE INDEX [GroupName] ON [dhcpgroupreply]([Context],[GroupName]) ON [PRIMARY] +GO + + +-- +-- Table structure for table 'dhcpreply' +-- +CREATE TABLE [dhcpreply] ( + [id] [int] IDENTITY (1, 1) NOT NULL, + [Identifier] [varchar] (64) NOT NULL, + [Attribute] [varchar] (32) NOT NULL, + [Value] [varchar] (253) NOT NULL, + [op] [char] (2) NULL, + [Context] [varchar] (16) NOT NULL +) ON [PRIMARY] +GO + +ALTER TABLE [dhcpreply] WITH NOCHECK ADD + CONSTRAINT [DF_dhcpreply_Identifier] DEFAULT ('') FOR [Identifier], + CONSTRAINT [DF_dhcpreply_Attribute] DEFAULT ('') FOR [Attribute], + CONSTRAINT [DF_dhcpreply_Value] DEFAULT ('') FOR [Value], + CONSTRAINT [DF_dhcpreply_op] DEFAULT (null) FOR [op], + CONSTRAINT [DF_dhcpreply_Context] DEFAULT ('') FOR [Context], + CONSTRAINT [PK_dhcpreply] PRIMARY KEY NONCLUSTERED + ( + [id] + ) ON [PRIMARY] +GO + +CREATE INDEX [Identifier] ON [dhcpreply]([Context],[Identifier]) ON [PRIMARY] +GO + + +-- +-- Table structure for table 'dhcpgroup' +-- +CREATE TABLE [dhcpgroup] ( + [id] [int] IDENTITY (1, 1) NOT NULL, + [Identifier] [varchar] (64) NOT NULL, + [GroupName] [varchar] (64) NULL, + [Priority] [int] NULL, + [Context] [varchar] (16) NULL +) ON [PRIMARY] +GO + +ALTER TABLE [dhcpgroup] WITH NOCHECK ADD + CONSTRAINT [DF_dhcpgroup_Identifier] DEFAULT ('') FOR [Identifier], + CONSTRAINT [DF_dhcpgroup_GroupName] DEFAULT ('') FOR [GroupName], + CONSTRAINT [DF_dhcpgroup_Context] DEFAULT ('') FOR [Context], + CONSTRAINT [PK_dhcpgroup] PRIMARY KEY NONCLUSTERED + ( + [id] + ) ON [PRIMARY] +GO + +CREATE INDEX [Identifier] ON [dhcpgroup]([Context],[Identifier]) ON [PRIMARY] +GO |