raddb/sql/main/mssql/queries.conf

   1 # -*- text -*-
   2 ##
   3 ## dialup.conf -- MSSQL configuration for default schema (schema.sql)
   4 ##
   5 ##      $Id$
   6
   7         # Safe characters list for sql queries. Everything else is replaced
   8         # with their mime-encoded equivalents.
   9         # The default list should be ok
  10         #safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
  11
  12         #######################################################################
  13         #  Query config:  Username
  14         #######################################################################
  15         # This is the username that will get substituted, escaped, and added
  16         # as attribute 'SQL-User-Name'.  '%{SQL-User-Name}' should be used
  17         # below everywhere a username substitution is needed so you you can
  18         # be sure the username passed from the client is escaped properly.
  19         #
  20         # Uncomment the next line, if you want the sql_user_name to mean:
  21         #
  22         #    Use Stripped-User-Name, if it's there.
  23         #    Else use User-Name, if it's there,
  24         #    Else use hard-coded string "none" as the user name.
  25         #sql_user_name = "%{%{Stripped-User-Name}:-%{User-Name:-none}}"
  26         #
  27         sql_user_name = "%{User-Name}"
  28
  29
  30         #######################################################################
  31         #  Authorization Queries
  32         #######################################################################
  33         #  These queries compare the check items for the user
  34         #  in ${authcheck_table} and setup the reply items in
  35         #  ${authreply_table}.  You can use any query/tables
  36         #  you want, but the return data for each row MUST
  37         #  be in the  following order:
  38         #
  39         #  0. Row ID (currently unused)
  40         #  1. UserName/GroupName
  41         #  2. Item Attr Name
  42         #  3. Item Attr Value
  43         #  4. Item Attr Operation
  44         #######################################################################
  45         # Query for case sensitive usernames was removed. Please contact with me,
  46         # if you know analog of STRCMP functions for MS SQL.
  47
  48         authorize_check_query = "SELECT id,UserName,Attribute,Value,op FROM ${authcheck_table} WHERE Username = '%{SQL-User-Name}' ORDER BY id"
  49         authorize_reply_query = "SELECT id,UserName,Attribute,Value,op FROM ${authreply_table} WHERE Username = '%{SQL-User-Name}' ORDER BY id"
  50
  51         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"
  52         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"
  53
  54         group_membership_query = "SELECT groupname \
  55           FROM ${usergroup_table} \
  56           WHERE username = '%{SQL-User-Name}' \
  57           ORDER BY priority"
  58
  59         #######################################################################
  60         # Accounting and Post-Auth Queries
  61         #######################################################################
  62         # These queries insert/update accounting and authentication records.
  63         # The query to use is determined by the value of 'reference'.
  64         # This value is used as a configuration path and should resolve to one
  65         # or more 'query's. If reference points to multiple queries, and a query
  66         # fails, the next query is executed.
  67         #
  68         # Behaviour is identical to the old 1.x/2.x module, except we can now
  69         # fail between N queries, and query selection can be based on any
  70         # combination of attributes, or custom 'Acct-Status-Type' values.
  71         #######################################################################
  72         accounting {
  73                 reference = "%{tolower:type.%{Acct-Status-Type}.query}"
  74
  75                 #  All SQL statements are logged to this file.
  76                 #  This file is preferred to the "logfile" set in
  77                 #  the mods-enabled/sql file.  The filename is dynamically
  78                 #  expanded at run time, so you can use %H, etc., just
  79                 #  as with the detail file.
  80 #               logfile = ${logdir}/accounting.sql
  81
  82                 type {
  83                         accounting-on {
  84                                 query = "\
  85                                         UPDATE ${....acct_table1} \
  86                                         SET \
  87                                                 AcctStopTime='%S', \
  88                                                 AcctSessionTime=unix_timestamp('%S') - \
  89                                                         unix_timestamp(AcctStartTime), \
  90                                                 AcctTerminateCause='%{Acct-Terminate-Cause}', \
  91                                                 AcctStopDelay = %{Acct-Delay-Time:-0} \
  92                                         WHERE AcctStopTime = 0 \
  93                                         AND NASIPAddress = '%{NAS-IP-Address}' \
  94                                         AND AcctStartTime <= '%S'"
  95                         }
  96
  97                         accounting-off {
  98                                 query = "${..accounting-on.query}"
  99                         }
 100
 101                         start {
 102                                 query = "\
 103                                         INSERT INTO ${....acct_table1} \
 104                                                 (AcctSessionId,         AcctUniqueId,           UserName, \
 105                                                 Realm,                  NASIPAddress,           NASPort, \
 106                                                 NASPortType,            AcctStartTime,          AcctSessionTime, \
 107                                                 AcctAuthentic,          ConnectInfo_start,      ConnectInfo_stop, \
 108                                                 AcctInputOctets,        AcctOutputOctets,       CalledStationId, \
 109                                                 CallingStationId,       AcctTerminateCause,     ServiceType, \
 110                                                 FramedProtocol,         FramedIPAddress,        AcctStartDelay, \
 111                                                 AcctStopDelay,          XAscendSessionSvrKey) \
 112                                         VALUES(\
 113                                                 '%{Acct-Session-Id}', \
 114                                                 '%{Acct-Unique-Session-Id}', \
 115                                                 '%{SQL-User-Name}', \
 116                                                 '%{Realm}', \
 117                                                 '%{NAS-IP-Address}', \
 118                                                 '%{NAS-Port-Id}', \
 119                                                 '%{NAS-Port-Type}', \
 120                                                 '%S', \
 121                                                 '0', \
 122                                                 '%{Acct-Authentic}', \
 123                                                 '%{Connect-Info}', \
 124                                                 '', \
 125                                                 '0', \
 126                                                 '0', \
 127                                                 '%{Called-Station-Id}', \
 128                                                 '%{Calling-Station-Id}', \
 129                                                 '', \
 130                                                 '%{Service-Type}', \
 131                                                 '%{Framed-Protocol}', \
 132                                                 '%{Framed-IP-Address}', \
 133                                                 '%{Acct-Delay-Time}', \
 134                                                 '0', \
 135                                                 '%{X-Ascend-Session-Svr-Key}')"
 136
 137                                 query = "\
 138                                         UPDATE ${....acct_table1} \
 139                                         SET \
 140                                                 AcctStartTime = '%S', \
 141                                                 AcctStartDelay = '%{Acct-Delay-Time:-0}', \
 142                                                 ConnectInfo_start = '%{Connect-Info}' \
 143                                         WHERE AcctSessionId = '%{Acct-Session-Id}' \
 144                                         AND UserName = '%{SQL-User-Name}' \
 145                                         AND NASIPAddress = '%{NAS-IP-Address}' \
 146                                         AND AcctStopTime = 0"
 147                         }
 148
 149                         interim-update {
 150                                 query = "\
 151                                         UPDATE ${....acct_table1} \
 152                                         SET \
 153                                                 FramedIPAddress = '%{Framed-IP-Address}' \
 154                                         WHERE AcctSessionId = '%{Acct-Session-Id}' \
 155                                         AND UserName = '%{SQL-User-Name}' \
 156                                         AND NASIPAddress= '%{NAS-IP-Address}' \
 157                                         AND AcctStopTime = 0"
 158
 159                                 query = "\
 160                                         INSERT INTO ${....acct_table1} \
 161                                                 (AcctSessionId,         AcctUniqueId,           UserName, \
 162                                                 Realm,                  NASIPAddress,           NASPort, \
 163                                                 NASPortType,            AcctSessionTime,        AcctAuthentic, \
 164                                                 ConnectInfo_start,      AcctInputOctets,        AcctOutputOctets, \
 165                                                 CalledStationId,        CallingStationId,       ServiceType, \
 166                                                 FramedProtocol,         FramedIPAddress,        AcctStartDelay, \
 167                                                 XAscendSessionSvrKey) \
 168                                         VALUES(\
 169                                                 '%{Acct-Session-Id}', \
 170                                                 '%{Acct-Unique-Session-Id}', \
 171                                                 '%{SQL-User-Name}', \
 172                                                 '%{Realm}', \
 173                                                 '%{NAS-IP-Address}', \
 174                                                 '%{NAS-Port-Id}', \
 175                                                 '%{NAS-Port-Type}', \
 176                                                 '%{Acct-Session-Time}', \
 177                                                 '%{Acct-Authentic}', \
 178                                                 '', \
 179                                                 '%{Acct-Input-Octets}', \
 180                                                 '%{Acct-Output-Octets}', \
 181                                                 '%{Called-Station-Id}', \
 182                                                 '%{Calling-Station-Id}', \
 183                                                 '%{Service-Type}', \
 184                                                 '%{Framed-Protocol}', \
 185                                                 '%{Framed-IP-Address}', \
 186                                                 '0', \
 187                                                 '%{X-Ascend-Session-Svr-Key}')"
 188                         }
 189
 190                         stop {
 191                                 query = "\
 192                                         UPDATE ${....acct_table2} \
 193                                         SET \
 194                                                 AcctStopTime = '%S', \
 195                                                 AcctSessionTime = '%{Acct-Session-Time}', \
 196                                                 AcctInputOctets = '%{Acct-Input-Octets}', \
 197                                                 AcctOutputOctets = '%{Acct-Output-Octets}', \
 198                                                 AcctTerminateCause = '%{Acct-Terminate-Cause}', \
 199                                                 AcctStopDelay = '%{Acct-Delay-Time:-0}', \
 200                                                 ConnectInfo_stop = '%{Connect-Info}' \
 201                                         WHERE AcctSessionId = '%{Acct-Session-Id}' \
 202                                         AND UserName = '%{SQL-User-Name}' \
 203                                         AND NASIPAddress = '%{NAS-IP-Address}' \
 204                                         AND AcctStopTime = 0"
 205
 206                                 query = "\
 207                                         INSERT into ${....acct_table2} \
 208                                                 (AcctSessionId,         AcctUniqueId,           UserName, \
 209                                                 Realm,                  NASIPAddress,           NASPort, \
 210                                                 NASPortType,            AcctStopTime,           AcctSessionTime, \
 211                                                 AcctAuthentic,          ConnectInfo_start,      ConnectInfo_stop, \
 212                                                 AcctInputOctets,        AcctOutputOctets,       CalledStationId, \
 213                                                 CallingStationId,       AcctTerminateCause,     ServiceType, \
 214                                                 FramedProtocol,         FramedIPAddress,        AcctStartDelay, \
 215                                                 AcctStopDelay) \
 216                                         VALUES(\
 217                                                 '%{Acct-Session-Id}', \
 218                                                 '%{Acct-Unique-Session-Id}', \
 219                                                 '%{SQL-User-Name}', \
 220                                                 '%{Realm}', \
 221                                                 '%{NAS-IP-Address}', \
 222                                                 '%{NAS-Port-Id}', \
 223                                                 '%{NAS-Port-Type}', \
 224                                                 '%S', \
 225                                                 '%{Acct-Session-Time}', \
 226                                                 '%{Acct-Authentic}', \
 227                                                 '', \
 228                                                 '%{Connect-Info}', \
 229                                                 '%{Acct-Input-Octets}', \
 230                                                 '%{Acct-Output-Octets}', \
 231                                                 '%{Called-Station-Id}', \
 232                                                 '%{Calling-Station-Id}', \
 233                                                 '%{Acct-Terminate-Cause}', \
 234                                                 '%{Service-Type}', \
 235                                                 '%{Framed-Protocol}', \
 236                                                 '%{Framed-IP-Address}', \
 237                                                 '0', \
 238                                                 '%{Acct-Delay-Time:-0}')"
 239                         }
 240                 }
 241         }
 242
 243         post-auth {
 244
 245         }