| # -*- text -*- |
| # |
| # main/mssql/queries.conf -- MSSQL configuration for default schema (schema.sql) |
| # |
| # $Id: 6fcea6edb5998f9f6c302f6246a88cdddf83dbaa $ |
| |
| # 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}.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}" |
| } |
| |
| 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, AcctStartDelay, \ |
| AcctStopDelay, XAscendSessionSvrKey) \ |
| VALUES(\ |
| '%{Acct-Session-Id}', \ |
| '%{Acct-Unique-Session-Id}', \ |
| '%{SQL-User-Name}', \ |
| '%{Realm}', \ |
| '%{NAS-IP-Address}', \ |
| '%{NAS-Port-Id}', \ |
| '%{NAS-Port-Type}', \ |
| '%S', \ |
| '0', \ |
| '%{Acct-Authentic}', \ |
| '%{Connect-Info}', \ |
| '', \ |
| '0', \ |
| '0', \ |
| '%{Called-Station-Id}', \ |
| '%{Calling-Station-Id}', \ |
| '', \ |
| '%{Service-Type}', \ |
| '%{Framed-Protocol}', \ |
| '%{Framed-IP-Address}', \ |
| '%{Acct-Delay-Time}', \ |
| '0', \ |
| '%{X-Ascend-Session-Svr-Key}')" |
| |
| query = "\ |
| UPDATE ${....acct_table1} \ |
| SET \ |
| AcctStartTime = '%S', \ |
| AcctStartDelay = '%{%{Acct-Delay-Time}:-0}', \ |
| ConnectInfo_start = '%{Connect-Info}' \ |
| WHERE AcctSessionId = '%{Acct-Session-Id}' \ |
| AND UserName = '%{SQL-User-Name}' \ |
| AND NASIPAddress = '%{NAS-IP-Address}' \ |
| AND AcctStopTime = 0" |
| } |
| |
| interim-update { |
| query = "\ |
| UPDATE ${....acct_table1} \ |
| SET \ |
| FramedIPAddress = '%{Framed-IP-Address}' \ |
| WHERE AcctSessionId = '%{Acct-Session-Id}' \ |
| AND UserName = '%{SQL-User-Name}' \ |
| AND NASIPAddress= '%{NAS-IP-Address}' \ |
| 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, AcctStartDelay, \ |
| XAscendSessionSvrKey) \ |
| VALUES(\ |
| '%{Acct-Session-Id}', \ |
| '%{Acct-Unique-Session-Id}', \ |
| '%{SQL-User-Name}', \ |
| '%{Realm}', \ |
| '%{NAS-IP-Address}', \ |
| '%{NAS-Port-Id}', \ |
| '%{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}', \ |
| '0', \ |
| '%{X-Ascend-Session-Svr-Key}')" |
| } |
| |
| stop { |
| query = "\ |
| UPDATE ${....acct_table2} \ |
| SET \ |
| AcctStopTime = '%S', \ |
| AcctSessionTime = '%{Acct-Session-Time}', \ |
| AcctInputOctets = '%{Acct-Input-Octets}', \ |
| AcctOutputOctets = '%{Acct-Output-Octets}', \ |
| AcctTerminateCause = '%{Acct-Terminate-Cause}', \ |
| AcctStopDelay = '%{%{Acct-Delay-Time}:-0}', \ |
| ConnectInfo_stop = '%{Connect-Info}' \ |
| WHERE AcctSessionId = '%{Acct-Session-Id}' \ |
| AND UserName = '%{SQL-User-Name}' \ |
| AND NASIPAddress = '%{NAS-IP-Address}' \ |
| 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, AcctStartDelay, \ |
| AcctStopDelay) \ |
| VALUES(\ |
| '%{Acct-Session-Id}', \ |
| '%{Acct-Unique-Session-Id}', \ |
| '%{SQL-User-Name}', \ |
| '%{Realm}', \ |
| '%{NAS-IP-Address}', \ |
| '%{NAS-Port-Id}', \ |
| '%{NAS-Port-Type}', \ |
| '%S', \ |
| '%{Acct-Session-Time}', \ |
| '%{Acct-Authentic}', \ |
| '', \ |
| '%{Connect-Info}', \ |
| '%{Acct-Input-Octets}', \ |
| '%{Acct-Output-Octets}', \ |
| '%{Called-Station-Id}', \ |
| '%{Calling-Station-Id}', \ |
| '%{Acct-Terminate-Cause}', \ |
| '%{Service-Type}', \ |
| '%{Framed-Protocol}', \ |
| '%{Framed-IP-Address}', \ |
| '0', \ |
| '%{%{Acct-Delay-Time}:-0}')" |
| } |
| } |
| } |
| |
| 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 |
| } |