diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-28 09:49:46 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-28 09:49:46 +0000 |
commit | 50b37d4a27d3295a29afca2286f1a5a086142cec (patch) | |
tree | 9212f763934ee090ef72d823f559f52ce387f268 /raddb/mods-config/sql/main/oracle/queries.conf | |
parent | Initial commit. (diff) | |
download | freeradius-upstream.tar.xz freeradius-upstream.zip |
Adding upstream version 3.2.1+dfsg.upstream/3.2.1+dfsgupstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'raddb/mods-config/sql/main/oracle/queries.conf')
-rw-r--r-- | raddb/mods-config/sql/main/oracle/queries.conf | 684 |
1 files changed, 684 insertions, 0 deletions
diff --git a/raddb/mods-config/sql/main/oracle/queries.conf b/raddb/mods-config/sql/main/oracle/queries.conf new file mode 100644 index 0000000..e3dd767 --- /dev/null +++ b/raddb/mods-config/sql/main/oracle/queries.conf @@ -0,0 +1,684 @@ +# -*- text -*- +# +# main/oracle/queries.conf -- Oracle configuration for default schema (schema.sql) +# +# $Id$ + +####################################################################### +# 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 "DEFAULT" as the user name. +#sql_user_name = "%{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}}" +# +sql_user_name = "%{User-Name}" + +####################################################################### +# Query config: Event-Timestamp +####################################################################### +# event_timestamp_epoch is the basis for the time inserted into +# accounting records. Typically this will be the Event-Timestamp of the +# accounting request, which is provided by a NAS. +# +# Uncomment the next line, if you want the timestamp to be based on the +# request reception time recorded by this server, for example if you +# distrust the provided Event-Timestamp. +#event_timestamp_epoch = "%l" + +event_timestamp_epoch = "%{%{integer:Event-Timestamp}:-%l}" + +# event_timestamp is the SQL snippet for converting an epoch timestamp +# to an SQL date. + +event_timestamp = "TO_DATE('1970-01-01','YYYY-MM-DD') + NUMTODSINTERVAL(${event_timestamp_epoch},'SECOND')" + +####################################################################### +# Query config: Class attribute +####################################################################### +# +# 3.0.22 and later have a "class" column in the accounting table. +# +# However, we do NOT want to break existing configurations by adding +# the Class attribute to the default queries. If we did that, then +# systems using newer versions of the server would fail, because +# there is no "class" column in their accounting tables. +# +# The solution to that is the following "class" subsection. If your +# database has a "class" column for the various tables, then you can +# uncomment the configuration items here. The queries below will +# then automatically insert the Class attribute into radacct, +# radpostauth, etc. +# +class { + # + # Delete the '#' character from each of the configuration + # items in this section. This change puts the Class + # attribute into the various tables. Leave the double-quoted + # string there, as the value for the configuration item. + # + # See also policy.d/accounting, and the "insert_acct_class" + # policy. You will need to list (or uncomment) + # "insert_acct_class" in the "post-auth" section in order to + # create a Class attribute. + # + column_name = # ", class" + packet_xlat = # ", '%{Class}'" + reply_xlat = # ", '%{reply:Class}'" +} + +####################################################################### +# Default profile +####################################################################### +# This is the default profile. It is found in SQL by group membership. +# That means that this profile must be a member of at least one group +# which will contain the corresponding check and reply items. +# This profile will be queried in the authorize section for every user. +# The point is to assign all users a default profile without having to +# manually add each one to a group that will contain the profile. +# The SQL module will also honor the User-Profile attribute. This +# attribute can be set anywhere in the authorize section (ie the users +# file). It is found exactly as the default profile is found. +# If it is set then it will *overwrite* the default profile setting. +# The idea is to select profiles based on checks on the incoming packets, +# not on user group membership. For example: +# -- users file -- +# DEFAULT Service-Type == Outbound-User, User-Profile := "outbound" +# DEFAULT Service-Type == Framed-User, User-Profile := "framed" +# +# By default the default_user_profile is not set +# +#default_user_profile = "DEFAULT" +# +# Determines if we will query the default_user_profile or the User-Profile +# if the user is not found. If the profile is found then we consider the user +# found. By default this is set to 'no'. +# +#query_on_not_found = no + + +####################################################################### +# NAS Query +####################################################################### +# This query retrieves the radius clients +# +# 0. Row ID (currently unused) +# 1. Name (or IP address) +# 2. Shortname +# 3. Type +# 4. Secret +# 5. Virtual server +####################################################################### + +client_query = "\ + SELECT id, nasname, shortname, type, secret, server \ + FROM ${client_table}" + +####################################################################### +# 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 +####################################################################### +# +# WARNING: Oracle is case sensitive +# +# The main difference between MySQL and Oracle queries is the date format. +# You must use the TO_DATE function to transform the radius date format to +# the Oracle date format, and put NULL otherwise '0' in a void date field. +# +####################################################################### + +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" + +####################################################################### +# Simultaneous Use Checking Queries +####################################################################### +# simul_count_query - query for the number of current connections +# - If this is not defined, no simultaneous use checking +# - will be performed by this module instance +# simul_verify_query - query to return details of current connections for verification +# - Leave blank or commented out to disable verification step +# - Note that the returned field order should not be changed. +####################################################################### + +simul_count_query = "\ + SELECT COUNT(*) \ + FROM ${acct_table1} \ + WHERE UserName = '%{SQL-User-Name}' \ + AND AcctStopTime IS NULL" + +simul_verify_query = "\ + SELECT \ + RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, \ + FramedIPAddress, CallingStationId, FramedProtocol \ + FROM ${acct_table1} \ + WHERE UserName='%{SQL-User-Name}' \ + AND AcctStopTime IS NULL" + +####################################################################### +# Group Membership Queries +####################################################################### +# group_membership_query - Check user group membership +####################################################################### + +group_membership_query = "\ + SELECT GroupName \ + FROM ${usergroup_table} \ + WHERE UserName='%{SQL-User-Name}'" + +####################################################################### +# 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 = ${....event_timestamp}, \ + AcctSessionTime = ROUND((${....event_timestamp} - \ + TO_DATE(TO_CHAR(acctstarttime, 'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss'))*86400), \ + AcctTerminateCause='%{%{Acct-Terminate-Cause}:-NAS-Reboot}', \ + AcctStopDelay = %{%{Acct-Delay-Time}:-0} \ + WHERE AcctStopTime IS NULL \ + AND NASIPAddress = '%{NAS-IP-Address}' \ + AND AcctStartTime <= ${....event_timestamp}" + } + + 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} (\ + AcctSessionId, \ + AcctUniqueId, \ + UserName, \ + Realm, \ + NASIPAddress, \ + NASPortId, \ + NASPortType, \ + AcctStartTime, \ + AcctStopTime, \ + 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}', \ + TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \ + NULL, \ + 0, \ + '', \ + '%{Connect-Info}', \ + NULL, \ + 0, \ + 0, \ + '%{Called-Station-Id}', \ + '%{Calling-Station-Id}', \ + NULL, \ + '%{Service-Type}', \ + NULL, \ + '', \ + '', \ + '', \ + '', \ + '')" + + query = "\ + UPDATE ${....acct_table1} SET \ + AcctStartTime = TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \ + 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} (\ + RadAcctId, \ + AcctSessionId, \ + AcctUniqueId, \ + UserName, \ + Realm, \ + NASIPAddress, \ + NASPortId, \ + NASPortType, \ + AcctStartTime, \ + AcctStopTime, \ + AcctSessionTime, \ + AcctAuthentic, \ + ConnectInfo_start, \ + ConnectInfo_stop, \ + AcctInputOctets, \ + AcctOutputOctets, \ + CalledStationId, \ + CallingStationId, \ + AcctTerminateCause, \ + ServiceType, \ + FramedProtocol, \ + FramedIPAddress, \ + FramedIPv6Address, \ + FramedIPv6Prefix, \ + FramedInterfaceId, \ + DelegatedIPv6Prefix, \ + AcctStartDelay, \ + AcctStopDelay, \ + XAscendSessionSvrKey \ + ${....class.column_name}) \ + VALUES(\ + '', \ + '%{Acct-Session-Id}', \ + '%{Acct-Unique-Session-Id}', \ + '%{SQL-User-Name}', \ + '%{Realm}', \ + '%{NAS-IP-Address}', \ + '%{%{NAS-Port-ID}:-%{NAS-Port}}', \ + '%{NAS-Port-Type}', \ + ${....event_timestamp}, \ + NULL, \ + '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}' \ + ${....class.packet_xlat})" + + # + # 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 = NULLIF('%{Framed-IP-Address}', ''), \ + FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', ''), \ + FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', ''), \ + FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \ + DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', ''), \ + AcctStartTime = TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \ + AcctSessionTime = '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" + + query = "\ + UPDATE ${....acct_table1} \ + SET \ + AcctStartTime = ${....event_timestamp}, \ + AcctStartDelay = '%{%{Acct-Delay-Time}:-0}', \ + ConnectInfo_start = '%{Connect-Info}' \ + WHERE AcctUniqueId = '%{Acct-Unique-Session-ID}' \ + AND AcctStopTime IS NULL" + } + + interim-update { + query = "\ + UPDATE ${....acct_table1} \ + SET \ + FramedIPAddress = NULLIF('%{Framed-IP-Address}', ''), \ + FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', ''), \ + FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', ''), \ + FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \ + DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', ''), \ + AcctSessionTime = '%{Acct-Session-Time}', \ + AcctInputOctets = '%{Acct-Input-Octets}' + \ + ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \ + AcctOutputOctets = '%{Acct-Output-Octets}' + \ + ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296) \ + WHERE AcctUniqueId = '%{Acct-Unique-Session-ID}' \ + AND AcctStopTime IS NULL" + + query = "\ + INSERT into ${....acct_table1} (\ + RadAcctId, \ + AcctSessionId, \ + AcctUniqueId, \ + UserName, \ + Realm, \ + NASIPAddress, \ + NASPortId, \ + NASPortType, \ + AcctStartTime, \ + AcctSessionTime, \ + AcctAuthentic, \ + ConnectInfo_start, \ + AcctInputOctets, \ + AcctOutputOctets, \ + CalledStationId, \ + CallingStationId, \ + ServiceType, \ + FramedProtocol, \ + FramedIPAddress, \ + FramedIPv6Address, \ + FramedIPv6Prefix, \ + FramedInterfaceId, \ + DelegatedIPv6Prefix, \ + AcctStartDelay, \ + XAscendSessionSvrKey \ + ${....class.column_name}) \ + VALUES(\ + '', \ + '%{Acct-Session-Id}', \ + '%{Acct-Unique-Session-Id}', \ + '%{SQL-User-Name}', \ + '%{Realm}', \ + '%{NAS-IP-Address}', \ + '%{%{NAS-Port-ID}:-%{NAS-Port}}', \ + '%{NAS-Port-Type}', \ + NULL, \ + '%{Acct-Session-Time}', \ + '%{Acct-Authentic}', \ + '', \ + '%{Acct-Input-Octets}' + \ + ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \ + '%{Acct-Output-Octets}' + \ + ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296), \ + '%{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}' \ + ${....class.packet_xlat})" + + # + # 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 = NULLIF('%{Framed-IP-Address}', ''), \ + FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', ''), \ + FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', ''), \ + FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \ + DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', ''), \ + AcctSessionTime = '%{Acct-Session-Time}', \ + AcctInputOctets = '%{Acct-Input-Octets}' + \ + ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \ + AcctOutputOctets = '%{Acct-Output-Octets}' + \ + ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296) \ + 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 = ${....event_timestamp}, \ + AcctSessionTime = '%{Acct-Session-Time}', \ + AcctInputOctets = '%{Acct-Input-Octets}' + \ + ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \ + AcctOutputOctets = '%{Acct-Output-Octets}' + \ + ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296), \ + AcctTerminateCause = '%{Acct-Terminate-Cause}', \ + AcctStopDelay = '%{%{Acct-Delay-Time}:-0}', \ + ConnectInfo_stop = '%{Connect-Info}' \ + WHERE AcctUniqueId = '%{Acct-Unique-Session-ID}' \ + AND AcctStopTime IS NULL" + + query = "\ + INSERT into ${....acct_table2} (\ + RadAcctId, \ + AcctSessionId, \ + AcctUniqueId, \ + UserName, \ + Realm, \ + NASIPAddress, \ + NASPortId, \ + NASPortType, \ + AcctStartTime, \ + AcctStopTime, \ + AcctSessionTime, \ + AcctAuthentic, \ + ConnectInfo_start, \ + ConnectInfo_stop, \ + AcctInputOctets, \ + AcctOutputOctets, \ + CalledStationId, \ + CallingStationId, \ + AcctTerminateCause, \ + ServiceType, \ + FramedProtocol, \ + FramedIPAddress, \ + FramedIPv6Address, \ + FramedIPv6Prefix, \ + FramedInterfaceId, \ + DelegatedIPv6Prefix, \ + AcctStartDelay, \ + AcctStopDelay \ + ${....class.column_name}) \ + VALUES(\ + '', \ + '%{Acct-Session-Id}', \ + '%{Acct-Unique-Session-Id}', \ + '%{SQL-User-Name}', \ + '%{Realm}', \ + '%{NAS-IP-Address}', \ + '%{%{NAS-Port-ID}:-%{NAS-Port}}', \ + '%{NAS-Port-Type}', \ + NULL, \ + ${....event_timestamp}, \ + '%{Acct-Session-Time}', \ + '%{Acct-Authentic}', \ + NULL, \ + '%{Connect-Info}', \ + '%{Acct-Input-Octets}' + \ + ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \ + '%{Acct-Output-Octets}' + \ + ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296), \ + '%{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}' \ + ${....class.packet_xlat})" + + # + # 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 = NULLIF('%{Framed-IP-Address}', ''), \ + FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', ''), \ + FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', ''), \ + FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \ + DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', ''), \ + AcctStopTime = TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \ + AcctSessionTime = '%{Acct-Session-Time}', \ + AcctInputOctets = '%{Acct-Input-Octets}' + \ + ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \ + AcctOutputOctets = '%{Acct-Output-Octets}' + \ + ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296), \ + 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" + + } + } +} + +####################################################################### +# Authentication Logging Queries +####################################################################### +# postauth_query - Insert some info after authentication +####################################################################### + +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 + query = "\ + INSERT INTO ${..postauth_table} \ + (username, pass, reply, authdate ${..class.column_name}) \ + VALUES (\ + '%{User-Name}', \ + '%{%{User-Password}:-%{Chap-Password}}', \ + '%{reply:Packet-Type}', \ + TO_TIMESTAMP('%S.%M','YYYY-MM-DDHH24:MI:SS.FF') \ + ${..class.reply_xlat})" +} |