raddb/sql/oracle/dialup.conf

   1 # -*- text -*-
   2 ##
   3 ## dialup.conf -- Oracle configuration for default schema (schema.sql)
   4 ##
   5 ##      $Id$
   6
   7         #######################################################################
   8         #  Query config:  Username
   9         #######################################################################
  10         # This is the username that will get substituted, escaped, and added
  11         # as attribute 'SQL-User-Name'.  '%{SQL-User-Name}' should be used below
  12         # everywhere a username substitution is needed so you you can be sure
  13         # the username passed from the client is escaped properly.
  14         #
  15         #  Uncomment the next line, if you want the sql_user_name to mean:
  16         #
  17         #    Use Stripped-User-Name, if it's there.
  18         #    Else use User-Name, if it's there,
  19         #    Else use hard-coded string "DEFAULT" as the user name.
  20         #sql_user_name = "%{Stripped-User-Name:-%{User-Name:-DEFAULT}}"
  21         #
  22         sql_user_name = "%{User-Name}"
  23
  24         #######################################################################
  25         #  Default profile
  26         #######################################################################
  27         # This is the default profile. It is found in SQL by group membership.
  28         # That means that this profile must be a member of at least one group
  29         # which will contain the corresponding check and reply items.
  30         # This profile will be queried in the authorize section for every user.
  31         # The point is to assign all users a default profile without having to
  32         # manually add each one to a group that will contain the profile.
  33         # The SQL module will also honor the User-Profile attribute. This
  34         # attribute can be set anywhere in the authorize section (ie the users
  35         # file). It is found exactly as the default profile is found.
  36         # If it is set then it will *overwrite* the default profile setting.
  37         # The idea is to select profiles based on checks on the incoming packets,
  38         # not on user group membership. For example:
  39         # -- users file --
  40         # DEFAULT       Service-Type == Outbound-User, User-Profile := "outbound"
  41         # DEFAULT       Service-Type == Framed-User, User-Profile := "framed"
  42         #
  43         # By default the default_user_profile is not set
  44         #
  45         #default_user_profile = "DEFAULT"
  46         #
  47         # Determines if we will query the default_user_profile or the User-Profile
  48         # if the user is not found. If the profile is found then we consider the user
  49         # found. By default this is set to 'no'.
  50         #
  51         #query_on_not_found = no
  52
  53
  54         #######################################################################
  55         #  NAS Query
  56         #######################################################################
  57         #  This query retrieves the radius clients
  58         #
  59         #  0. Row ID (currently unused)
  60         #  1. Name (or IP address)
  61         #  2. Shortname
  62         #  3. Type
  63         #  4. Secret
  64         #  5. Virtual server
  65         #######################################################################
  66
  67         nas_query = "SELECT id, nasname, shortname, type, secret, server FROM ${nas_table}"
  68         #######################################################################
  69         #  Authorization Queries
  70         #######################################################################
  71         #  These queries compare the check items for the user
  72         #  in ${authcheck_table} and setup the reply items in
  73         #  ${authreply_table}.  You can use any query/tables
  74         #  you want, but the return data for each row MUST
  75         #  be in the  following order:
  76         #
  77         #  0. Row ID (currently unused)
  78         #  1. UserName/GroupName
  79         #  2. Item Attr Name
  80         #  3. Item Attr Value
  81         #  4. Item Attr Operation
  82         #######################################################################
  83         #
  84         # WARNING: Oracle is case sensitive
  85         #
  86         # The main difference between MySQL and Oracle queries is the date format.
  87         # You must use the TO_DATE function to transform the radius date format to
  88         # the Oracle date format, and put NULL otherwise '0' in a void date field.
  89         #
  90         #######################################################################
  91
  92         authorize_check_query = "SELECT id,UserName,Attribute,Value,op FROM ${authcheck_table} WHERE Username = '%{SQL-User-Name}' ORDER BY id"
  93         authorize_reply_query = "SELECT id,UserName,Attribute,Value,op FROM ${authreply_table} WHERE Username = '%{SQL-User-Name}' ORDER BY id"
  94
  95         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"
  96         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"
  97
  98         #######################################################################
  99         # Simultaneous Use Checking Queries
 100         #######################################################################
 101         # simul_count_query     - query for the number of current connections
 102         #                       - If this is not defined, no simultaneouls use checking
 103         #                       - will be performed by this module instance
 104         # simul_verify_query    - query to return details of current connections for verification
 105         #                       - Leave blank or commented out to disable verification step
 106         #                       - Note that the returned field order should not be changed.
 107         #######################################################################
 108
 109         # Uncomment simul_count_query to enable simultaneous use checking
 110         # simul_count_query = "SELECT COUNT(*) FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime IS NULL"
 111         simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, FramedProtocol FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime IS NULL"
 112
 113         #######################################################################
 114         # Group Membership Queries
 115         #######################################################################
 116         # group_membership_query        - Check user group membership
 117         #######################################################################
 118
 119         group_membership_query = "SELECT GroupName FROM ${usergroup_table} WHERE UserName='%{SQL-User-Name}'"
 120
 121         #######################################################################
 122         # Accounting and Post-Auth Queries
 123         #######################################################################
 124         # These queries insert/update accounting and authentication records.
 125         # The query to use is determined by the value of 'reference'.
 126         # This value is used as a configuration path and should resolve to one
 127         # or more 'query's. If reference points to multiple queries, and a query
 128         # fails, the next query is executed.
 129         #
 130         # Behaviour is identical to the old 1.x/2.x module, except we can now
 131         # fail between N queries, and query selection can be based on any
 132         # combination of attributes, or custom 'Acct-Status-Type' values.
 133         #######################################################################
 134         accounting {
 135                 reference = "%{tolower:type.%{Acct-Status-Type}.query}"
 136
 137                 #  All SQL statements are logged to this file.
 138                 #  This file is preferred to the "logfile" set in
 139                 #  the mods-enabled/sql file.  The filename is dynamically
 140                 #  expanded at run time, so you can use %H, etc., just
 141                 #  as with the detail file.
 142 #               logfile = ${logdir}/accounting.sql
 143
 144                 type {
 145                         accounting-on {
 146                                 query = "\
 147                                         UPDATE ${....acct_table1} \
 148                                         SET \
 149                                                 AcctStopTime = TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \
 150                                                 AcctSessionTime = round((TO_DATE('%S','yyyy-mm-dd hh24:mi:ss') - \
 151                                                         TO_DATE(TO_CHAR(acctstarttime, 'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss'))*86400), \
 152                                                 AcctTerminateCause='%{Acct-Terminate-Cause}', \
 153                                                 AcctStopDelay = %{Acct-Delay-Time:-0} \
 154                                         WHERE AcctStopTime IS NULL \
 155                                         AND NASIPAddress = '%{NAS-IP-Address}' \
 156                                         AND AcctStartTime <= TO_DATE('%S','yyyy-mm-dd hh24:mi:ss')"
 157                         }
 158
 159                         accounting-off {
 160                                 query = "${..accounting-on.query}"
 161                         }
 162
 163                         start {
 164                                 query = "\
 165                                         INSERT INTO ${....acct_table1} \
 166                                                 (RadAcctId,             AcctSessionId,          AcctUniqueId, \
 167                                                 UserName,               Realm,                  NASIPAddress, \
 168                                                 NASPortId,              NASPortType,            AcctStartTime, \
 169                                                 AcctStopTime,           AcctSessionTime,        AcctAuthentic, \
 170                                                 ConnectInfo_start,      ConnectInfo_stop,       AcctInputOctets, \
 171                                                 AcctOutputOctets,       CalledStationId,        CallingStationId, \
 172                                                 AcctTerminateCause,     ServiceType,            FramedProtocol, \
 173                                                 FramedIPAddress,        AcctStartDelay,         AcctStopDelay, \
 174                                                 XAscendSessionSvrKey) \
 175                                         VALUES(\
 176                                                 '', \
 177                                                 '%{Acct-Session-Id}', \
 178                                                 '%{Acct-Unique-Session-Id}', \
 179                                                 '%{SQL-User-Name}', \
 180                                                 '%{Realm}', \
 181                                                 '%{NAS-IP-Address}', \
 182                                                 '%{NAS-Port-Id}', \
 183                                                 '%{NAS-Port-Type}', \
 184                                                 TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \
 185                                                 NULL, \
 186                                                 '0', \
 187                                                 '%{Acct-Authentic}', \
 188                                                 '%{Connect-Info}', \
 189                                                 '', \
 190                                                 '0', \
 191                                                 '0', \
 192                                                 '%{Called-Station-Id}', \
 193                                                 '%{Calling-Station-Id}', \
 194                                                 '', \
 195                                                 '%{Service-Type}', \
 196                                                 '%{Framed-Protocol}', \
 197                                                 '%{Framed-IP-Address}', \
 198                                                 '%{Acct-Delay-Time}', \
 199                                                 '0', \
 200                                                 '%{X-Ascend-Session-Svr-Key}')"
 201
 202                                 query = "\
 203                                         UPDATE ${....acct_table1} \
 204                                         SET \
 205                                                 AcctStartTime = TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \
 206                                                 AcctStartDelay = '%{Acct-Delay-Time:-0}', \
 207                                                 ConnectInfo_start = '%{Connect-Info}' \
 208                                         WHERE AcctSessionId = '%{Acct-Session-Id}' \
 209                                         AND UserName = '%{SQL-User-Name}' \
 210                                         AND NASIPAddress = '%{NAS-IP-Address}' \
 211                                         AND AcctStopTime IS NULL"
 212                         }
 213
 214                         interim-update {
 215                                 query = "\
 216                                         UPDATE ${....acct_table1} \
 217                                         SET \
 218                                                 FramedIPAddress = NULLIF('%{Framed-IP-Address}', ''), \
 219                                                 AcctSessionTime = '%{Acct-Session-Time}', \
 220                                                 AcctInputOctets = '%{Acct-Input-Octets}' + \
 221                                                         ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \
 222                                                 AcctOutputOctets = '%{Acct-Output-Octets}' +  \
 223                                                         ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296) \
 224                                         WHERE AcctSessionId = '%{Acct-Session-Id}' \
 225                                         AND UserName = '%{SQL-User-Name}' \
 226                                         AND NASIPAddress= '%{NAS-IP-Address}' \
 227                                         AND AcctStopTime IS NULL"
 228
 229                                 query = "\
 230                                         INSERT into ${....acct_table1} \
 231                                                 (RadAcctId,             AcctSessionId,          AcctUniqueId, \
 232                                                 UserName,               Realm,                  NASIPAddress, \
 233                                                 NASPortId,              NASPortType,            AcctStartTime, \
 234                                                 AcctSessionTime,        AcctAuthentic,          ConnectInfo_start, \
 235                                                 AcctInputOctets,        AcctOutputOctets,       CalledStationId, \
 236                                                 CallingStationId,       ServiceType,            FramedProtocol, \
 237                                                 FramedIPAddress,        AcctStartDelay,         XAscendSessionSvrKey) \
 238                                         VALUES(\
 239                                                 '', \
 240                                                 '%{Acct-Session-Id}', \
 241                                                 '%{Acct-Unique-Session-Id}', \
 242                                                 '%{SQL-User-Name}', \
 243                                                 '%{Realm}', \
 244                                                 '%{NAS-IP-Address}', \
 245                                                 '%{NAS-Port-Id}', \
 246                                                 '%{NAS-Port-Type}', \
 247                                                 NULL, \
 248                                                 '%{Acct-Session-Time}', \
 249                                                 '%{Acct-Authentic}', \
 250                                                 '', \
 251                                                 '%{Acct-Input-Octets}' + \
 252                                                         ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \
 253                                                 '%{Acct-Output-Octets}' +  \
 254                                                         ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296), \
 255                                                 '%{Called-Station-Id}', \
 256                                                 '%{Calling-Station-Id}', \
 257                                                 '%{Service-Type}', \
 258                                                 '%{Framed-Protocol}', \
 259                                                 '%{Framed-IP-Address}', \
 260                                                 '0', \
 261                                                 '%{X-Ascend-Session-Svr-Key}')"
 262                         }
 263
 264                         stop {
 265                                 query = "\
 266                                         UPDATE ${....acct_table2} \
 267                                         SET \
 268                                                 AcctStopTime = TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \
 269                                                 AcctSessionTime = '%{Acct-Session-Time}', \
 270                                                 AcctInputOctets = '%{Acct-Input-Octets}' + \
 271                                                         ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \
 272                                                 AcctOutputOctets = '%{Acct-Output-Octets}' +  \
 273                                                         ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296), \
 274                                                 AcctTerminateCause = '%{Acct-Terminate-Cause}', \
 275                                                 AcctStopDelay = '%{Acct-Delay-Time:-0}', \
 276                                                 ConnectInfo_stop = '%{Connect-Info}' \
 277                                         WHERE AcctSessionId = '%{Acct-Session-Id}' \
 278                                         AND UserName = '%{SQL-User-Name}' \
 279                                         AND NASIPAddress = '%{NAS-IP-Address}' \
 280                                         AND AcctStopTime IS NULL"
 281
 282                                 query = "\
 283                                         "INSERT into ${....acct_table2} \
 284                                                 (RadAcctId,             AcctSessionId,          AcctUniqueId, \
 285                                                  UserName,              Realm,                  NASIPAddress, \
 286                                                  NASPortId,             NASPortType,            AcctStartTime, \
 287                                                  AcctStopTime,          AcctSessionTime,        AcctAuthentic, \
 288                                                  ConnectInfo_start,     ConnectInfo_stop,       AcctInputOctets, \
 289                                                  AcctOutputOctets,      CalledStationId,        CallingStationId, \
 290                                                  AcctTerminateCause,    ServiceType,            FramedProtocol, \
 291                                                  FramedIPAddress,       AcctStartDelay,         AcctStopDelay) \
 292                                         VALUES(\
 293                                                 '', \
 294                                                 '%{Acct-Session-Id}', \
 295                                                 '%{Acct-Unique-Session-Id}', \
 296                                                 '%{SQL-User-Name}', \
 297                                                 '%{Realm}', \
 298                                                 '%{NAS-IP-Address}', \
 299                                                 '%{NAS-Port-Id}', \
 300                                                 '%{NAS-Port-Type}', \
 301                                                 NULL, \
 302                                                 TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \
 303                                                 '%{Acct-Session-Time}', \
 304                                                 '%{Acct-Authentic}', \
 305                                                 '', \
 306                                                 '%{Connect-Info}', \
 307                                                 '%{Acct-Input-Octets}' + \
 308                                                         ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \
 309                                                 '%{Acct-Output-Octets}' + \
 310                                                         ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296), \
 311                                                 '%{Called-Station-Id}', \
 312                                                 '%{Calling-Station-Id}', \
 313                                                 '%{Acct-Terminate-Cause}', \
 314                                                 '%{Service-Type}', \
 315                                                 '%{Framed-Protocol}', \
 316                                                 '%{Framed-IP-Address}', \
 317                                                 '0', \
 318                                                 '%{Acct-Delay-Time:-0}')"
 319
 320                         }
 321                 }
 322         }
 323
 324         #######################################################################
 325         # Authentication Logging Queries
 326         #######################################################################
 327         # postauth_query                - Insert some info after authentication
 328         #######################################################################
 329
 330         post-auth {
 331         query = "\
 332                 INSERT INTO ${..postauth_table} \
 333                         (username, pass, reply, authdate) \
 334             VALUES (\
 335                 '%{User-Name}', \
 336                 '%{%{User-Password}:-%{Chap-Password}}', \
 337                 '%{reply:Packet-Type}', \
 338                 TO_TIMESTAMP('%S','YYYY-MM-DDHH24:MI:SS'))"
 339         }