# -*- text -*- # # main/mssql/queries.conf -- MSSQL configuration for default schema (schema.sql) # # $Id: 6ba2a7af8dfa075297711d879bb84d1dadb9339f $ # 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: Username ####################################################################### # This is the username that will get substituted, escaped, and added # as attribute 'SQL-User-Name'. '%{SQL-User-Name}' should be used # below everywhere a username substitution is needed so you you can # be sure the username passed from the client is escaped properly. # # Uncomment the next line, if you want the sql_user_name to mean: # # Use Stripped-User-Name, if it's there. # Else use User-Name, if it's there, # Else use hard-coded string "none" as the user name. #sql_user_name = "%{%{Stripped-User-Name}:-%{%{User-Name}:-none}}" # sql_user_name = "%{User-Name}" ####################################################################### # Authorization Queries ####################################################################### # These queries compare the check items for the user # in ${authcheck_table} and setup the reply items in # ${authreply_table}. 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. UserName/GroupName # 2. Item Attr Name # 3. Item Attr Value # 4. Item Attr Operation ####################################################################### # Query for case sensitive usernames was removed. Please contact with me, # if you know analog of STRCMP functions for MS SQL. authorize_check_query = "\ SELECT id, UserName, Attribute, Value, op \ FROM ${authcheck_table} \ WHERE Username = '%{SQL-User-Name}' \ ORDER BY id" authorize_reply_query = "\ SELECT id, UserName, Attribute, Value, op \ FROM ${authreply_table} \ WHERE Username = '%{SQL-User-Name}' \ ORDER BY id" authorize_group_check_query = "\ SELECT \ ${groupcheck_table}.id,${groupcheck_table}.GroupName, \ ${groupcheck_table}.Attribute,${groupcheck_table}.Value, \ ${groupcheck_table}.op \ FROM ${groupcheck_table},${usergroup_table} \ WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' \ AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName \ ORDER BY ${groupcheck_table}.id" authorize_group_reply_query = "\ SELECT \ ${groupreply_table}.id, ${groupreply_table}.GroupName, \ ${groupreply_table}.Attribute,${groupreply_table}.Value, \ ${groupreply_table}.op \ FROM ${groupreply_table},${usergroup_table} \ WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' \ AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName \ ORDER BY ${groupreply_table}.id" group_membership_query = "\ SELECT groupname \ FROM ${usergroup_table} \ WHERE username = '%{SQL-User-Name}' \ ORDER BY priority" ####################################################################### # Accounting and Post-Auth Queries ####################################################################### # These queries insert/update accounting and authentication records. # The query to use is determined by the value of 'reference'. # This value is used as a configuration path and should resolve to one # or more 'query's. If reference points to multiple queries, and a query # fails, the next query is executed. # # Behaviour is identical to the old 1.x/2.x module, except we can now # fail between N queries, and query selection can be based on any # combination of attributes, or custom 'Acct-Status-Type' values. ####################################################################### accounting { reference = "%{tolower:type.%{%{Acct-Status-Type}:-%{Request-Processing-Stage}}.query}" # Write SQL queries to a logfile. This is potentially useful for bulk inserts # when used with the rlm_sql_null driver. # logfile = ${logdir}/accounting.sql type { accounting-on { query = "\ UPDATE ${....acct_table1} \ SET \ AcctStopTime='%S', \ AcctSessionTime=unix_timestamp('%S') - \ unix_timestamp(AcctStartTime), \ AcctTerminateCause='%{%{Acct-Terminate-Cause}:-NAS-Reboot}', \ AcctStopDelay = %{%{Acct-Delay-Time}:-0} \ WHERE AcctStopTime = 0 \ AND NASIPAddress = '%{NAS-IP-Address}' \ AND AcctStartTime <= '%S'" } accounting-off { query = "${..accounting-on.query}" } # # Implement the "sql_session_start" policy. # See raddb/policy.d/accounting for more details. # # You also need to fix the other queries as # documented below. Look for "sql_session_start". # post-auth { query = "\ INSERT INTO ${....acct_table1} \ INSERT INTO ${....acct_table1} ( \ AcctSessionId, \ AcctUniqueId, \ UserName, \ Realm, \ NASIPAddress, \ NASPort, \ NASPortType, \ AcctStartTime, \ AcctSessionTime, \ AcctAuthentic, \ ConnectInfo_start, \ ConnectInfo_stop, \ AcctInputOctets, \ AcctOutputOctets, \ CalledStationId, \ CallingStationId, \ AcctTerminateCause, \ ServiceType, \ FramedProtocol, \ FramedIPAddress, \ FramedIPv6Address, \ FramedIPv6Prefix, \ FramedInterfaceId, \ DelegatedIPv6Prefix) \ VALUES(\ '%{Acct-Session-Id}', \ '%{Acct-Unique-Session-Id}', \ '%{SQL-User-Name}', \ '%{Realm}', \ '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}', \ '%{%{NAS-Port-ID}:-%{NAS-Port}}', \ '%{NAS-Port-Type}', \ '%S', \ 0, \ '', \ '%{Connect-Info}', \ '', \ 0, \ 0, \ '%{Called-Station-Id}', \ '%{Calling-Station-Id}', \ '', \ '%{Service-Type}', \ '', \ '', \ '', \ '', \ '', \ '')" query = "\ UPDATE ${....acct_table1} SET \ AcctStartTime = '%S', \ ConnectInfo_start = '%{Connect-Info}', \ AcctSessionId = '%{Acct-Session-Id}' \ WHERE UserName = '%{SQL-User-Name}' \ AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \ AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \ AND NASPortType = '%{NAS-Port-Type}' \ AND AcctStopTime IS NULL" } start { query = "\ INSERT INTO ${....acct_table1} ( \ AcctSessionId, \ AcctUniqueId, \ UserName, \ Realm, \ NASIPAddress, \ NASPort, \ NASPortType, \ AcctStartTime, \ AcctSessionTime, \ AcctAuthentic, \ ConnectInfo_start, \ ConnectInfo_stop, \ AcctInputOctets, \ AcctOutputOctets, \ CalledStationId, \ CallingStationId, \ AcctTerminateCause, \ ServiceType, \ FramedProtocol, \ FramedIPAddress, \ FramedIPv6Address, \ FramedIPv6Prefix, \ FramedInterfaceId, \ DelegatedIPv6Prefix, \ AcctStartDelay, \ AcctStopDelay, \ XAscendSessionSvrKey) \ VALUES(\ '%{Acct-Session-Id}', \ '%{Acct-Unique-Session-Id}', \ '%{SQL-User-Name}', \ '%{Realm}', \ '%{NAS-IP-Address}', \ '%{%{NAS-Port-ID}:-%{NAS-Port}}', \ '%{NAS-Port-Type}', \ '%S', \ '0', \ '%{Acct-Authentic}', \ '%{Connect-Info}', \ '', \ '0', \ '0', \ '%{Called-Station-Id}', \ '%{Calling-Station-Id}', \ '', \ '%{Service-Type}', \ '%{Framed-Protocol}', \ '%{Framed-IP-Address}', \ '%{Framed-IPv6-Address}', \ '%{Framed-IPv6-Prefix}', \ '%{Framed-Interface-Id}', \ '%{Delegated-IPv6-Prefix}', \ '%{Acct-Delay-Time}', \ '0', \ '%{X-Ascend-Session-Svr-Key}')" # # When using "sql_session_start", you should comment out # the previous query, and enable this one. # # Just change the previous query to "-query", # and this one to "query". The previous one # will be ignored, and this one will be # enabled. # -query = "\ UPDATE ${....acct_table1} \ SET \ AcctSessionId = '%{Acct-Session-Id}', \ AcctUniqueId = '%{Acct-Unique-Session-Id}', \ AcctAuthentic = '%{Acct-Authentic}', \ ConnectInfo_start = '%{Connect-Info}', \ ServiceType = '%{Service-Type}', \ FramedProtocol = '%{Framed-Protocol}', \ FramedIpAddress = '%{Framed-IP-Address}', \ FramedIpv6Address = '%{Framed-IPv6-Address}', \ FramedIpv6Prefix = '%{Framed-IPv6-Prefix}', \ FramedInterfaceId = '%{Framed-Interface-Id}', \ DelegatedIpv6Prefix = '%{Delegated-IPv6-Prefix}', \ AcctStartTime = '%S' \ WHERE UserName = '%{SQL-User-Name}' \ AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \ AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \ AND NASPortType = '%{NAS-Port-Type}' \ AND AcctStopTime IS NULL" query = "\ UPDATE ${....acct_table1} \ SET \ AcctStartTime = '%S', \ AcctStartDelay = '%{%{Acct-Delay-Time}:-0}', \ ConnectInfo_start = '%{Connect-Info}' \ WHERE AcctUniqueId = '%{Acct-Unique-Session-ID}' \ AND AcctStopTime = 0" } interim-update { query = "\ UPDATE ${....acct_table1} \ SET \ FramedIPAddress = '%{Framed-IP-Address}', \ FramedIPv6Address = '%{Framed-IPv6-Address}', \ FramedIPv6Prefix = '%{Framed-IPv6-Prefix}', \ FramedInterfaceId = '%{Framed-Interface-Id}', \ DelegatedIPv6Prefix = '%{Delegated-IPv6-Prefix}' \ WHERE AcctUniqueId = '%{Acct-Unique-Session-ID}' \ AND AcctStopTime = 0" query = "\ INSERT INTO ${....acct_table1} ( \ AcctSessionId, \ AcctUniqueId, \ UserName, \ Realm, \ NASIPAddress, \ NASPort, \ NASPortType, \ AcctSessionTime, \ AcctAuthentic, \ ConnectInfo_start, \ AcctInputOctets, \ AcctOutputOctets, \ CalledStationId, \ CallingStationId, \ ServiceType, \ FramedProtocol, \ FramedIPAddress, \ FramedIPv6Address, \ FramedIPv6Prefix, \ FramedInterfaceId, \ DelegatedIPv6Prefix, \ AcctStartDelay, \ XAscendSessionSvrKey) \ VALUES(\ '%{Acct-Session-Id}', \ '%{Acct-Unique-Session-Id}', \ '%{SQL-User-Name}', \ '%{Realm}', \ '%{NAS-IP-Address}', \ '%{%{NAS-Port-ID}:-%{NAS-Port}}', \ '%{NAS-Port-Type}', \ '%{Acct-Session-Time}', \ '%{Acct-Authentic}', \ '', \ '%{Acct-Input-Octets}', \ '%{Acct-Output-Octets}', \ '%{Called-Station-Id}', \ '%{Calling-Station-Id}', \ '%{Service-Type}', \ '%{Framed-Protocol}', \ '%{Framed-IP-Address}', \ '%{Framed-IPv6-Address}', \ '%{Framed-IPv6-Prefix}', \ '%{Framed-Interface-Id}', \ '%{Delegated-IPv6-Prefix}', \ '0', \ '%{X-Ascend-Session-Svr-Key}')" # # When using "sql_session_start", you should comment out # the previous query, and enable this one. # # Just change the previous query to "-query", # and this one to "query". The previous one # will be ignored, and this one will be # enabled. # -query = "\ UPDATE ${....acct_table1} \ SET \ AcctSessionId = '%{Acct-Session-Id}', \ AcctUniqueId = '%{Acct-Unique-Session-Id}', \ AcctAuthentic = '%{Acct-Authentic}', \ ConnectInfo_start = '%{Connect-Info}', \ ServiceType = '%{Service-Type}', \ FramedProtocol = '%{Framed-Protocol}', \ FramedIPAddress = '%{Framed-IP-Address}', \ FramedIPv6Address = '%{Framed-IPv6-Address}', \ FramedIPv6Prefix = '%{Framed-IPv6-Prefix}', \ FramedInterfaceId = '%{Framed-Interface-Id}', \ DelegatedIPv6Prefix = '%{Delegated-IPv6-Prefix}', \ AcctInputOctets = convert(bigint, '%{%{Acct-Input-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Input-Octets}:-0}', \ AcctOutputOctets = convert(bigint, '%{%{Acct-Output-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Output-Octets}:-0}' \ WHERE UserName = '%{SQL-User-Name}' \ AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \ AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \ AND NASPortType = '%{NAS-Port-Type}' \ AND AcctStopTime IS NULL" } stop { query = "\ UPDATE ${....acct_table2} \ SET \ AcctStopTime = '%S', \ AcctSessionTime = '%{Acct-Session-Time}', \ AcctInputOctets = convert(bigint, '%{%{Acct-Input-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Input-Octets}:-0}', \ AcctOutputOctets = convert(bigint, '%{%{Acct-Output-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Output-Octets}:-0}', \ AcctTerminateCause = '%{Acct-Terminate-Cause}', \ AcctStopDelay = '%{%{Acct-Delay-Time}:-0}', \ ConnectInfo_stop = '%{Connect-Info}' \ WHERE AcctUniqueId = '%{Acct-Unique-Session-ID}' \ AND AcctStopTime = 0" query = "\ INSERT into ${....acct_table2} (\ AcctSessionId, \ AcctUniqueId, \ UserName, \ Realm, \ NASIPAddress, \ NASPort, \ NASPortType, \ AcctStopTime, \ AcctSessionTime, \ AcctAuthentic, \ ConnectInfo_start, \ ConnectInfo_stop, \ AcctInputOctets, \ AcctOutputOctets, \ CalledStationId, \ CallingStationId, \ AcctTerminateCause, \ ServiceType, \ FramedProtocol, \ FramedIPAddress, \ FramedIPv6Address, \ FramedIPv6Prefix, \ FramedInterfaceId, \ DelegatedIPv6Prefix, \ AcctStartDelay, \ AcctStopDelay) \ VALUES(\ '%{Acct-Session-Id}', \ '%{Acct-Unique-Session-Id}', \ '%{SQL-User-Name}', \ '%{Realm}', \ '%{NAS-IP-Address}', \ '%{%{NAS-Port-ID}:-%{NAS-Port}}', \ '%{NAS-Port-Type}', \ '%S', \ '%{Acct-Session-Time}', \ '%{Acct-Authentic}', \ '', \ '%{Connect-Info}', \ NULL, \ convert(bigint, '%{%{Acct-Input-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Input-Octets}:-0}', \ convert(bigint, '%{%{Acct-Output-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Output-Octets}:-0}', \ '%{Called-Station-Id}', \ '%{Calling-Station-Id}', \ '%{Acct-Terminate-Cause}', \ '%{Service-Type}', \ '%{Framed-Protocol}', \ '%{Framed-IP-Address}', \ '%{Framed-IPv6-Address}', \ '%{Framed-IPv6-Prefix}', \ '%{Framed-Interface-Id}', \ '%{Delegated-IPv6-Prefix}', \ '0', \ '%{%{Acct-Delay-Time}:-0}')" # # When using "sql_session_start", you should comment out # the previous query, and enable this one. # # Just change the previous query to "-query", # and this one to "query". The previous one # will be ignored, and this one will be # enabled. # -query = "\ UPDATE ${....acct_table1} \ SET \ AcctSessionId = '%{Acct-Session-Id}', \ AcctUniqueId = '%{Acct-Unique-Session-Id}', \ AcctAuthentic = '%{Acct-Authentic}', \ ConnectInfo_start = '%{Connect-Info}', \ ServiceType = '%{Service-Type}', \ FramedProtocol = '%{Framed-Protocol}', \ FramedIPAddress = '%{Framed-IP-Address}', \ FramedIPv6Address = '%{Framed-IPv6-Address}', \ FramedIPv6Prefix = '%{Framed-IPv6-Prefix}', \ FramedInterfaceId = '%{Framed-Interface-Id}', \ DelegatedIPv6Prefix = '%{Delegated-IPv6-Prefix}', \ AcctStopTime = '%S', \ AcctSessionTime = %{Acct-Session-Time}, \ AcctInputOctets = convert(bigint, '%{%{Acct-Input-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Input-Octets}:-0}', \ AcctOutputOctets = convert(bigint, '%{%{Acct-Output-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Output-Octets}:-0}', \ AcctTerminateCause = '%{Acct-Terminate-Cause}', \ ConnectInfo_stop = '%{Connect-Info}' \ WHERE UserName = '%{SQL-User-Name}' \ AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \ AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \ AND NASPortType = '%{NAS-Port-Type}' \ AND AcctStopTime IS NULL" } # # No Acct-Status-Type == ignore the packet # accounting { query = "SELECT true" } } } post-auth { # Write SQL queries to a logfile. This is potentially useful for bulk inserts # when used with the rlm_sql_null driver. # logfile = ${logdir}/post-auth.sql }