"Fossies" - the Fresh Open Source Software Archive

Member "freeradius-server-3.0.23/raddb/mods-config/sql/main/oracle/queries.conf" (10 Jun 2021, 23409 Bytes) of package /linux/misc/freeradius-server-3.0.23.tar.bz2:


As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) Generic config files source code syntax highlighting (style: standard) with prefixed line numbers. Alternatively you can here view or download the uninterpreted source code file. See also the latest Fossies "Diffs" side-by-side code changes report for "queries.conf": 3.0.22_vs_3.0.23.

    1 # -*- text -*-
    2 #
    3 #  main/oracle/queries.conf -- Oracle configuration for default schema (schema.sql)
    4 #
    5 #  $Id: ad33d34c2bc15c470c1f331eaea2bd754c69bdf6 $
    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 #  Query config:  Event-Timestamp
   26 #######################################################################
   27 # event_timestamp_epoch is the basis for the time inserted into
   28 # accounting records. Typically this will be the Event-Timestamp of the
   29 # accounting request, which is provided by a NAS.
   30 #
   31 # Uncomment the next line, if you want the timestamp to be based on the
   32 # request reception time recorded by this server, for example if you
   33 # distrust the provided Event-Timestamp.
   34 #event_timestamp_epoch = "%l"
   35 
   36 event_timestamp_epoch = "%{%{integer:Event-Timestamp}:-%l}"
   37 
   38 # event_timestamp is the SQL snippet for converting an epoch timestamp
   39 # to an SQL date.
   40 
   41 event_timestamp = "TO_DATE('1970-01-01','YYYY-MM-DD') + NUMTODSINTERVAL(${event_timestamp_epoch},'SECOND')"
   42 
   43 #######################################################################
   44 #  Query config:  Class attribute
   45 #######################################################################
   46 #
   47 #  3.0.22 and later have a "class" column in the accounting table.
   48 #
   49 #  However, we do NOT want to break existing configurations by adding
   50 #  the Class attribute to the default queries.  If we did that, then
   51 #  systems using newer versions of the server would fail, because
   52 #  there is no "class" column in their accounting tables.
   53 #
   54 #  The solution to that is the following "class" subsection.  If your
   55 #  database has a "class" column for the various tables, then you can
   56 #  uncomment the configuration items here.  The queries below will
   57 #  then automatically insert the Class attribute into radacct,
   58 #  radpostauth, etc.
   59 #
   60 class {
   61     #
   62     #  Delete the '#' character from each of the configuration
   63     #  items in this section.  This change puts the Class
   64     #  attribute into the various tables.  Leave the double-quoted
   65     #  string there, as the value for the configuration item.
   66     #
   67     #  See also policy.d/accounting, and the "insert_acct_class"
   68     #  policy.  You will need to list (or uncomment)
   69     #  "insert_acct_class" in the "post-auth" section in order to
   70     #  create a Class attribute.
   71     #
   72     column_name =   # ", class"
   73     packet_xlat =   # ", '%{Class}'"
   74     reply_xlat =    # ", '%{Reply:Class}'"
   75 }
   76 
   77 #######################################################################
   78 #  Default profile
   79 #######################################################################
   80 # This is the default profile. It is found in SQL by group membership.
   81 # That means that this profile must be a member of at least one group
   82 # which will contain the corresponding check and reply items.
   83 # This profile will be queried in the authorize section for every user.
   84 # The point is to assign all users a default profile without having to
   85 # manually add each one to a group that will contain the profile.
   86 # The SQL module will also honor the User-Profile attribute. This
   87 # attribute can be set anywhere in the authorize section (ie the users
   88 # file). It is found exactly as the default profile is found.
   89 # If it is set then it will *overwrite* the default profile setting.
   90 # The idea is to select profiles based on checks on the incoming packets,
   91 # not on user group membership. For example:
   92 # -- users file --
   93 # DEFAULT   Service-Type == Outbound-User, User-Profile := "outbound"
   94 # DEFAULT   Service-Type == Framed-User, User-Profile := "framed"
   95 #
   96 # By default the default_user_profile is not set
   97 #
   98 #default_user_profile = "DEFAULT"
   99 #
  100 # Determines if we will query the default_user_profile or the User-Profile
  101 # if the user is not found. If the profile is found then we consider the user
  102 # found. By default this is set to 'no'.
  103 #
  104 #query_on_not_found = no
  105 
  106 
  107 #######################################################################
  108 #  NAS Query
  109 #######################################################################
  110 #  This query retrieves the radius clients
  111 #
  112 #  0. Row ID (currently unused)
  113 #  1. Name (or IP address)
  114 #  2. Shortname
  115 #  3. Type
  116 #  4. Secret
  117 #  5. Virtual server
  118 #######################################################################
  119 
  120 client_query = "\
  121     SELECT id, nasname, shortname, type, secret, server \
  122     FROM ${client_table}"
  123 
  124 #######################################################################
  125 #  Authorization Queries
  126 #######################################################################
  127 #  These queries compare the check items for the user
  128 #  in ${authcheck_table} and setup the reply items in
  129 #  ${authreply_table}.  You can use any query/tables
  130 #  you want, but the return data for each row MUST
  131 #  be in the  following order:
  132 #
  133 #  0. Row ID (currently unused)
  134 #  1. UserName/GroupName
  135 #  2. Item Attr Name
  136 #  3. Item Attr Value
  137 #  4. Item Attr Operation
  138 #######################################################################
  139 #
  140 # WARNING: Oracle is case sensitive
  141 #
  142 # The main difference between MySQL and Oracle queries is the date format.
  143 # You must use the TO_DATE function to transform the radius date format to
  144 # the Oracle date format, and put NULL otherwise '0' in a void date field.
  145 #
  146 #######################################################################
  147 
  148 authorize_check_query = "\
  149     SELECT id, UserName, Attribute, Value, op \
  150     FROM ${authcheck_table} \
  151     WHERE Username = '%{SQL-User-Name}' \
  152     ORDER BY id"
  153 
  154 authorize_reply_query = "\
  155     SELECT id, UserName, Attribute, Value, op \
  156     FROM ${authreply_table} \
  157     WHERE Username = '%{SQL-User-Name}' \
  158     ORDER BY id"
  159 
  160 authorize_group_check_query = "\
  161     SELECT \
  162         ${groupcheck_table}.id, ${groupcheck_table}.GroupName, ${groupcheck_table}.Attribute, \
  163         ${groupcheck_table}.Value,${groupcheck_table}.op \
  164     FROM ${groupcheck_table}, ${usergroup_table} \
  165     WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' \
  166     AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName \
  167     ORDER BY ${groupcheck_table}.id"
  168 
  169 authorize_group_reply_query = "\
  170     SELECT \
  171         ${groupreply_table}.id, ${groupreply_table}.GroupName, ${groupreply_table}.Attribute, \
  172         ${groupreply_table}.Value, ${groupreply_table}.op \
  173     FROM ${groupreply_table}, ${usergroup_table} \
  174     WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' \
  175     AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName \
  176     ORDER BY ${groupreply_table}.id"
  177 
  178 #######################################################################
  179 # Simultaneous Use Checking Queries
  180 #######################################################################
  181 # simul_count_query - query for the number of current connections
  182 #           - If this is not defined, no simultaneous use checking
  183 #           - will be performed by this module instance
  184 # simul_verify_query    - query to return details of current connections for verification
  185 #           - Leave blank or commented out to disable verification step
  186 #           - Note that the returned field order should not be changed.
  187 #######################################################################
  188 
  189 simul_count_query = "\
  190     SELECT COUNT(*) \
  191     FROM ${acct_table1} \
  192     WHERE UserName = '%{SQL-User-Name}' \
  193     AND AcctStopTime IS NULL"
  194 
  195 simul_verify_query = "\
  196     SELECT \
  197         RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, \
  198         FramedIPAddress, CallingStationId, FramedProtocol \
  199     FROM ${acct_table1} \
  200     WHERE UserName='%{SQL-User-Name}' \
  201     AND AcctStopTime IS NULL"
  202 
  203 #######################################################################
  204 # Group Membership Queries
  205 #######################################################################
  206 # group_membership_query    - Check user group membership
  207 #######################################################################
  208 
  209 group_membership_query = "\
  210     SELECT GroupName \
  211     FROM ${usergroup_table} \
  212     WHERE UserName='%{SQL-User-Name}'"
  213 
  214 #######################################################################
  215 # Accounting and Post-Auth Queries
  216 #######################################################################
  217 # These queries insert/update accounting and authentication records.
  218 # The query to use is determined by the value of 'reference'.
  219 # This value is used as a configuration path and should resolve to one
  220 # or more 'query's. If reference points to multiple queries, and a query
  221 # fails, the next query is executed.
  222 #
  223 # Behaviour is identical to the old 1.x/2.x module, except we can now
  224 # fail between N queries, and query selection can be based on any
  225 # combination of attributes, or custom 'Acct-Status-Type' values.
  226 #######################################################################
  227 accounting {
  228     reference = "%{tolower:type.%{%{Acct-Status-Type}:-%{Request-Processing-Stage}}.query}"
  229 
  230     # Write SQL queries to a logfile. This is potentially useful for bulk inserts
  231     # when used with the rlm_sql_null driver.
  232 #       logfile = ${logdir}/accounting.sql
  233 
  234     type {
  235         accounting-on {
  236             query = "\
  237                 UPDATE ${....acct_table1} \
  238                 SET \
  239                     AcctStopTime = ${....event_timestamp}, \
  240                     AcctSessionTime = ROUND((${....event_timestamp} - \
  241                         TO_DATE(TO_CHAR(acctstarttime, 'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss'))*86400), \
  242                     AcctTerminateCause='%{%{Acct-Terminate-Cause}:-NAS-Reboot}', \
  243                     AcctStopDelay = %{%{Acct-Delay-Time}:-0} \
  244                 WHERE AcctStopTime IS NULL \
  245                 AND NASIPAddress = '%{NAS-IP-Address}' \
  246                 AND AcctStartTime <= ${....event_timestamp}"
  247         }
  248 
  249         accounting-off {
  250             query = "${..accounting-on.query}"
  251         }
  252 
  253         #
  254         #  Implement the "sql_session_start" policy.
  255         #  See raddb/policy.d/accounting for more details.
  256         #
  257         #  You also need to fix the other queries as
  258         #  documented below.  Look for "sql_session_start".
  259         #
  260         post-auth {
  261             query = "\
  262             INSERT INTO ${....acct_table1} (\
  263                 AcctSessionId, \
  264                 AcctUniqueId, \
  265                 UserName, \
  266                 Realm, \
  267                 NASIPAddress, \
  268                 NASPortId, \
  269                 NASPortType, \
  270                 AcctStartTime, \
  271                 AcctStopTime, \
  272                 AcctSessionTime, \
  273                 AcctAuthentic, \
  274                 ConnectInfo_start, \
  275                 ConnectInfo_stop, \
  276                 AcctInputOctets, \
  277                 AcctOutputOctets, \
  278                 CalledStationId, \
  279                 CallingStationId, \
  280                 AcctTerminateCause, \
  281                 ServiceType, \
  282                 FramedProtocol, \
  283                 FramedIPAddress, \
  284                 FramedIPv6Address, \
  285                 FramedIPv6Prefix, \
  286                 FramedInterfaceId, \
  287                 DelegatedIPv6Prefix) \
  288             VALUES(\
  289                 '%{Acct-Session-Id}', \
  290                 '%{Acct-Unique-Session-Id}', \
  291                 '%{SQL-User-Name}', \
  292                 '%{Realm}', \
  293                 '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}', \
  294                 '%{%{NAS-Port-ID}:-%{NAS-Port}}', \
  295                 '%{NAS-Port-Type}', \
  296                 TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \
  297                 NULL, \
  298                 0, \
  299                 '', \
  300                 '%{Connect-Info}', \
  301                 NULL, \
  302                 0, \
  303                 0, \
  304                 '%{Called-Station-Id}', \
  305                 '%{Calling-Station-Id}', \
  306                 NULL, \
  307                 '%{Service-Type}', \
  308                 NULL, \
  309                 '', \
  310                 '', \
  311                 '', \
  312                 '', \
  313                 '')"
  314 
  315             query = "\
  316                 UPDATE ${....acct_table1} SET \
  317                     AcctStartTime = TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \
  318                     ConnectInfo_start = '%{Connect-Info}', \
  319                     AcctSessionId = '%{Acct-Session-Id}' \
  320                 WHERE UserName = '%{SQL-User-Name}' \
  321                     AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
  322                     AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \
  323                     AND NASPortType = '%{NAS-Port-Type}' \
  324                     AND AcctStopTime IS NULL"
  325         }
  326 
  327         start {
  328             query = "\
  329                 INSERT INTO ${....acct_table1} (\
  330                     RadAcctId, \
  331                     AcctSessionId, \
  332                     AcctUniqueId, \
  333                     UserName, \
  334                     Realm, \
  335                     NASIPAddress, \
  336                     NASPortId, \
  337                     NASPortType, \
  338                     AcctStartTime, \
  339                     AcctStopTime, \
  340                     AcctSessionTime, \
  341                     AcctAuthentic, \
  342                     ConnectInfo_start, \
  343                     ConnectInfo_stop, \
  344                     AcctInputOctets, \
  345                     AcctOutputOctets, \
  346                     CalledStationId, \
  347                     CallingStationId, \
  348                     AcctTerminateCause, \
  349                     ServiceType, \
  350                     FramedProtocol, \
  351                     FramedIPAddress, \
  352                     FramedIPv6Address, \
  353                     FramedIPv6Prefix, \
  354                     FramedInterfaceId, \
  355                     DelegatedIPv6Prefix, \
  356                     AcctStartDelay, \
  357                     AcctStopDelay, \
  358                     XAscendSessionSvrKey \
  359                     ${..class.column_name}) \
  360                 VALUES(\
  361                     '', \
  362                     '%{Acct-Session-Id}', \
  363                     '%{Acct-Unique-Session-Id}', \
  364                     '%{SQL-User-Name}', \
  365                     '%{Realm}', \
  366                     '%{NAS-IP-Address}', \
  367                     '%{%{NAS-Port-ID}:-%{NAS-Port}}', \
  368                     '%{NAS-Port-Type}', \
  369                     ${....event_timestamp}, \
  370                     NULL, \
  371                     '0', \
  372                     '%{Acct-Authentic}', \
  373                     '%{Connect-Info}', \
  374                     '', \
  375                     '0', \
  376                     '0', \
  377                     '%{Called-Station-Id}', \
  378                     '%{Calling-Station-Id}', \
  379                     '', \
  380                     '%{Service-Type}', \
  381                     '%{Framed-Protocol}', \
  382                     '%{Framed-IP-Address}', \
  383                     '%{Framed-IPv6-Address}', \
  384                     '%{Framed-IPv6-Prefix}', \
  385                     '%{Framed-Interface-Id}', \
  386                     '%{Delegated-IPv6-Prefix}', \
  387                     '%{Acct-Delay-Time}', \
  388                     '0', \
  389                     '%{X-Ascend-Session-Svr-Key}' \
  390                     ${....class.packet_xlat})"
  391 
  392             #
  393             #  When using "sql_session_start", you should comment out
  394             #  the previous query, and enable this one.
  395             #
  396             #  Just change the previous query to "-query",
  397             #  and this one to "query".  The previous one
  398             #  will be ignored, and this one will be
  399             #  enabled.
  400             #
  401             -query = "\
  402                 UPDATE ${....acct_table1} \
  403                 SET \
  404                     AcctSessionId = '%{Acct-Session-Id}', \
  405                     AcctUniqueId = '%{Acct-Unique-Session-Id}', \
  406                     AcctAuthentic = '%{Acct-Authentic}', \
  407                     ConnectInfo_start = '%{Connect-Info}', \
  408                     ServiceType = '%{Service-Type}', \
  409                     FramedProtocol = '%{Framed-Protocol}', \
  410                     FramedIPAddress = NULLIF('%{Framed-IP-Address}', ''), \
  411                     FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', ''), \
  412                     FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', ''), \
  413                     FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \
  414                     DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', ''), \
  415                     AcctStartTime = TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \
  416                     AcctSessionTime = '0' \
  417                 WHERE UserName = '%{SQL-User-Name}' \
  418                     AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
  419                     AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \
  420                     AND NASPortType = '%{NAS-Port-Type}' \
  421                     AND AcctStopTime IS NULL"
  422 
  423             query = "\
  424                 UPDATE ${....acct_table1} \
  425                 SET \
  426                     AcctStartTime = ${....event_timestamp}, \
  427                     AcctStartDelay = '%{%{Acct-Delay-Time}:-0}', \
  428                     ConnectInfo_start = '%{Connect-Info}' \
  429                 WHERE AcctUniqueId = '%{Acct-Unique-Session-ID}' \
  430                 AND AcctStopTime IS NULL"
  431         }
  432 
  433         interim-update {
  434             query = "\
  435                 UPDATE ${....acct_table1} \
  436                 SET \
  437                     FramedIPAddress = NULLIF('%{Framed-IP-Address}', ''), \
  438                     FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', ''), \
  439                     FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', ''), \
  440                     FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \
  441                     DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', ''), \
  442                     AcctSessionTime = '%{Acct-Session-Time}', \
  443                     AcctInputOctets = '%{Acct-Input-Octets}' + \
  444                         ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \
  445                     AcctOutputOctets = '%{Acct-Output-Octets}' +  \
  446                         ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296) \
  447                 WHERE AcctUniqueId = '%{Acct-Unique-Session-ID}' \
  448                 AND AcctStopTime IS NULL"
  449 
  450             query = "\
  451                 INSERT into ${....acct_table1} (\
  452                     RadAcctId, \
  453                     AcctSessionId, \
  454                     AcctUniqueId, \
  455                     UserName, \
  456                     Realm, \
  457                     NASIPAddress, \
  458                     NASPortId, \
  459                     NASPortType, \
  460                     AcctStartTime, \
  461                     AcctSessionTime, \
  462                     AcctAuthentic, \
  463                     ConnectInfo_start, \
  464                     AcctInputOctets, \
  465                     AcctOutputOctets, \
  466                     CalledStationId, \
  467                     CallingStationId, \
  468                     ServiceType, \
  469                     FramedProtocol, \
  470                     FramedIPAddress, \
  471                     FramedIPv6Address, \
  472                     FramedIPv6Prefix, \
  473                     FramedInterfaceId, \
  474                     DelegatedIPv6Prefix, \
  475                     AcctStartDelay, \
  476                     XAscendSessionSvrKey \
  477                     ${..class.column_name}) \
  478                 VALUES(\
  479                     '', \
  480                     '%{Acct-Session-Id}', \
  481                     '%{Acct-Unique-Session-Id}', \
  482                     '%{SQL-User-Name}', \
  483                     '%{Realm}', \
  484                     '%{NAS-IP-Address}', \
  485                     '%{%{NAS-Port-ID}:-%{NAS-Port}}', \
  486                     '%{NAS-Port-Type}', \
  487                     NULL, \
  488                     '%{Acct-Session-Time}', \
  489                     '%{Acct-Authentic}', \
  490                     '', \
  491                     '%{Acct-Input-Octets}' + \
  492                         ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \
  493                     '%{Acct-Output-Octets}' +  \
  494                         ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296), \
  495                     '%{Called-Station-Id}', \
  496                     '%{Calling-Station-Id}', \
  497                     '%{Service-Type}', \
  498                     '%{Framed-Protocol}', \
  499                     '%{Framed-IP-Address}', \
  500                     '%{Framed-IPv6-Address}', \
  501                     '%{Framed-IPv6-Prefix}', \
  502                     '%{Framed-Interface-Id}', \
  503                     '%{Delegated-IPv6-Prefix}', \
  504                     '0', \
  505                     '%{X-Ascend-Session-Svr-Key}' \
  506                     ${....class.packet_xlat})"
  507 
  508             #
  509             #  When using "sql_session_start", you should comment out
  510             #  the previous query, and enable this one.
  511             #
  512             #  Just change the previous query to "-query",
  513             #  and this one to "query".  The previous one
  514             #  will be ignored, and this one will be
  515             #  enabled.
  516             #
  517             -query = "\
  518                 UPDATE ${....acct_table1} \
  519                 SET \
  520                     AcctSessionId = '%{Acct-Session-Id}', \
  521                     AcctUniqueId = '%{Acct-Unique-Session-Id}', \
  522                     AcctAuthentic = '%{Acct-Authentic}', \
  523                     ConnectInfo_start = '%{Connect-Info}', \
  524                     ServiceType = '%{Service-Type}', \
  525                     FramedProtocol = '%{Framed-Protocol}', \
  526                     FramedIPAddress = NULLIF('%{Framed-IP-Address}', ''), \
  527                     FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', ''), \
  528                     FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', ''), \
  529                     FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \
  530                     DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', ''), \
  531                     AcctSessionTime = '%{Acct-Session-Time}', \
  532                     AcctInputOctets = '%{Acct-Input-Octets}' + \
  533                         ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \
  534                     AcctOutputOctets = '%{Acct-Output-Octets}' + \
  535                         ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296) \
  536                 WHERE UserName = '%{SQL-User-Name}' \
  537                     AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
  538                     AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \
  539                     AND NASPortType = '%{NAS-Port-Type}' \
  540                     AND AcctStopTime IS NULL"
  541 
  542         }
  543 
  544         stop {
  545             query = "\
  546                 UPDATE ${....acct_table2} \
  547                 SET \
  548                     AcctStopTime = ${....event_timestamp}, \
  549                     AcctSessionTime = '%{Acct-Session-Time}', \
  550                     AcctInputOctets = '%{Acct-Input-Octets}' + \
  551                         ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \
  552                     AcctOutputOctets = '%{Acct-Output-Octets}' + \
  553                         ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296), \
  554                     AcctTerminateCause = '%{Acct-Terminate-Cause}', \
  555                     AcctStopDelay = '%{%{Acct-Delay-Time}:-0}', \
  556                     ConnectInfo_stop = '%{Connect-Info}' \
  557                 WHERE AcctUniqueId = '%{Acct-Unique-Session-ID}' \
  558                 AND AcctStopTime IS NULL"
  559 
  560             query = "\
  561                 INSERT into ${....acct_table2} (\
  562                     RadAcctId, \
  563                     AcctSessionId, \
  564                     AcctUniqueId, \
  565                     UserName, \
  566                     Realm, \
  567                     NASIPAddress, \
  568                     NASPortId, \
  569                     NASPortType, \
  570                     AcctStartTime, \
  571                     AcctStopTime, \
  572                     AcctSessionTime, \
  573                     AcctAuthentic, \
  574                     ConnectInfo_start, \
  575                     ConnectInfo_stop, \
  576                     AcctInputOctets, \
  577                     AcctOutputOctets, \
  578                     CalledStationId, \
  579                     CallingStationId, \
  580                     AcctTerminateCause, \
  581                     ServiceType, \
  582                     FramedProtocol, \
  583                     FramedIPAddress, \
  584                     FramedIPv6Address, \
  585                     FramedIPv6Prefix, \
  586                     FramedInterfaceId, \
  587                     DelegatedIPv6Prefix, \
  588                     AcctStartDelay, \
  589                     AcctStopDelay \
  590                     ${..class.column_name}) \
  591                 VALUES(\
  592                     '', \
  593                     '%{Acct-Session-Id}', \
  594                     '%{Acct-Unique-Session-Id}', \
  595                     '%{SQL-User-Name}', \
  596                     '%{Realm}', \
  597                     '%{NAS-IP-Address}', \
  598                     '%{%{NAS-Port-ID}:-%{NAS-Port}}', \
  599                     '%{NAS-Port-Type}', \
  600                     NULL, \
  601                     ${....event_timestamp}, \
  602                     '%{Acct-Session-Time}', \
  603                     '%{Acct-Authentic}', \
  604                     '', \
  605                     '%{Connect-Info}', \
  606                     NULL, \
  607                     '%{Acct-Input-Octets}' + \
  608                         ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \
  609                     '%{Acct-Output-Octets}' + \
  610                         ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296), \
  611                     '%{Called-Station-Id}', \
  612                     '%{Calling-Station-Id}', \
  613                     '%{Acct-Terminate-Cause}', \
  614                     '%{Service-Type}', \
  615                     '%{Framed-Protocol}', \
  616                     '%{Framed-IP-Address}', \
  617                     '%{Framed-IPv6-Address}', \
  618                     '%{Framed-IPv6-Prefix}', \
  619                     '%{Framed-Interface-Id}', \
  620                     '%{Delegated-IPv6-Prefix}', \
  621                     '0', \
  622                     '%{%{Acct-Delay-Time}:-0}' \
  623                     ${....class.packet_xlat})"
  624 
  625             #
  626             #  When using "sql_session_start", you should comment out
  627             #  the previous query, and enable this one.
  628             #
  629             #  Just change the previous query to "-query",
  630             #  and this one to "query".  The previous one
  631             #  will be ignored, and this one will be
  632             #  enabled.
  633             #
  634             -query = "\
  635                 UPDATE ${....acct_table1} \
  636                 SET \
  637                     AcctSessionId = '%{Acct-Session-Id}', \
  638                     AcctUniqueId = '%{Acct-Unique-Session-Id}', \
  639                     AcctAuthentic = '%{Acct-Authentic}', \
  640                     ConnectInfo_start = '%{Connect-Info}', \
  641                     ServiceType = '%{Service-Type}', \
  642                     FramedProtocol = '%{Framed-Protocol}', \
  643                     FramedIPAddress = NULLIF('%{Framed-IP-Address}', ''), \
  644                     FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', ''), \
  645                     FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', ''), \
  646                     FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \
  647                     DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', ''), \
  648                     AcctStopTime = TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \
  649                     AcctSessionTime = '%{Acct-Session-Time}', \
  650                     AcctInputOctets = '%{Acct-Input-Octets}' + \
  651                         ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \
  652                     AcctOutputOctets = '%{Acct-Output-Octets}' + \
  653                         ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296), \
  654                     AcctTerminateCause = '%{Acct-Terminate-Cause}', \
  655                     ConnectInfo_stop = '%{Connect-Info}' \
  656                 WHERE UserName = '%{SQL-User-Name}' \
  657                 AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
  658                 AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \
  659                 AND NASPortType = '%{NAS-Port-Type}' \
  660                 AND AcctStopTime IS NULL"
  661 
  662         }
  663     }
  664 }
  665 
  666 #######################################################################
  667 # Authentication Logging Queries
  668 #######################################################################
  669 # postauth_query                - Insert some info after authentication
  670 #######################################################################
  671 
  672 post-auth {
  673     # Write SQL queries to a logfile. This is potentially useful for bulk inserts
  674     # when used with the rlm_sql_null driver.
  675 #   logfile = ${logdir}/post-auth.sql
  676     query = "\
  677         INSERT INTO ${..postauth_table} \
  678             (username, pass, reply, authdate ${..class.column_name}) \
  679         VALUES (\
  680             '%{User-Name}', \
  681             '%{%{User-Password}:-%{Chap-Password}}', \
  682             '%{reply:Packet-Type}', \
  683             TO_TIMESTAMP('%S.%M','YYYY-MM-DDHH24:MI:SS.FF') \
  684             ${..class.reply_xlat})"
  685 }