####################################################################### # 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}" ####################################################################### # 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 ####################################################################### # 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 ####################################################################### # Use these for case sensitive usernames. # authorize_check_query = "SELECT id, UserName, Attribute, Value, op \ # FROM ${authcheck_table} \ # WHERE Username = BINARY '%{SQL-User-Name}' \ # ORDER BY id" # authorize_reply_query = "SELECT id, UserName, Attribute, Value, op \ # FROM ${authreply_table} \ # WHERE Username = BINARY '%{SQL-User-Name}' \ # ORDER BY id" # The default queries are case insensitive. (for compatibility with # older versions of FreeRADIUS) # authorize_check_query = "SELECT id, UserName, Attribute, Value, op \ # FROM ${authcheck_table} \ # WHERE Username = '%{SQL-User-Name}' \ # ORDER BY id" #authorize_check_query = "SELECT id, UserName, Attribute, TheValue, op \ # FROM RADIUS_CHECK ('%{SQL-User-Name}') ORDER BY ID" authorize_check_query = "SELECT attr_id, UserName, Attribute, TheValue, op \ FROM RAD6_CHECK ('CHECK', '%{SQL-User-Name}', \ '%{Calling-Station-Id}', \ '%{Mac-Addr}', \ '%{NAS-Ip-Address}', \ '%{NAS-Port}', \ '%{NAS-Real-Port}', \ '%{NAS-Port-id}', \ '%{DHCP-Option}', \ '%{Agent-Circuit-Id}', \ '%{Agent-Remote-Id}', \ '%{Framed-IP-Address}' \ ) ORDER BY ATTR_ID" #authorize_reply_query = "SELECT id, UserName, Attribute, TheValue, op \ # FROM RADIUS_CHECK ('%{SQL-User-Name}', '%{Calling-Station-Id}', '%{NAS-IP-Address}') ORDER BY ID" authorize_reply_query = "SELECT attr_id, UserName, Attribute, TheValue, op \ FROM RAD6_CHECK ('REPLY', '%{SQL-User-Name}', \ '%{Calling-Station-Id}', \ '%{Mac-Addr}', \ '%{NAS-Ip-Address}', \ '%{NAS-Port}', \ '%{NAS-Real-Port}', \ '%{NAS-Port-id}', \ '%{DHCP-Option}', \ '%{Agent-Circuit-Id}', \ '%{Agent-Remote-Id}', \ '%{Framed-IP-Address}' \ ) ORDER BY ATTR_ID" # authorize_reply_query = "SELECT id, UserName, Attribute, Value, op \ # FROM ${authreply_table} \ # WHERE Username = '%{SQL-User-Name}' \ # ORDER BY id" # Use these for case sensitive usernames. # authorize_group_check_query = "SELECT ${groupcheck_table}.id,${groupcheck_table}.GroupName,${groupcheck_table}.Attribute,${groupcheck_table}.alue,${groupcheck_table}.op FROM ${groupcheck_table},${usergroup_table} WHERE ${usergroup_table}.Username = BINARY '%{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}.alue,${groupreply_table}.op FROM ${groupreply_table},${usergroup_table} WHERE ${usergroup_table}.Username = BINARY '%{SQL-User-Name}' AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName ORDER BY ${groupreply_table}.id" #authorize_group_check_query = "SELECT ${groupcheck_table}.id,${groupcheck_table}.GroupName,${groupcheck_table}.Attribute,${groupcheck_table}.alue,${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}.alue,${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" ####################################################################### # Accounting Queries ####################################################################### # accounting_onoff_query - query for Accounting On/Off packets # accounting_update_query - query for Accounting update packets # accounting_update_query_alt - query for Accounting update packets # (alternate in case first query fails) # accounting_start_query - query for Accounting start packets # accounting_start_query_alt - query for Accounting start packets # (alternate in case first query fails) # accounting_stop_query - query for Accounting stop packets # accounting_stop_query_alt - query for Accounting start packets # (alternate in case first query doesn't # affect any existing rows in the table) ####################################################################### # accounting_onoff_query = "UPDATE ${acct_table1} SET AcctStopTime='%S', AcctSessionTime=unix_timestamp('%S') - unix_timestamp(AcctStartTime), AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time}' WHERE AcctSessionTime=0 AND AcctStopTime=0 AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= '%S'" accounting_onoff_query = "SELECT RESULT FROM RADIUS_ACCT_ONOFF ( \ '%S', \ '%{Acct-Terminate-Cause}', \ '%{Acct-Delay-Time}', \ '%{NAS-IP-Address}' \ )" # accounting_update_query = " \ # UPDATE ${acct_table1} \ # SET \ # FramedIPAddress = '%{Framed-IP-Address}', \ # AcctSessionTime = '%{Acct-Session-Time}', \ # AcctInputOctets = '%{Acct-Input-Gigawords:-0}' << 32 | \ # '%{Acct-Input-Octets:-0}', \ # AcctOutputOctets = '%{Acct-Output-Gigawords:-0}' << 32 | \ # '%{Acct-Output-Octets:-0}' \ # WHERE AcctSessionId = '%{Acct-Session-Id}' \ # AND UserName = '%{SQL-User-Name}' \ # AND NASIPAddress = '%{NAS-IP-Address}'" accounting_update_query = "SELECT RESULT FROM RAD6_ACCT_UPDATE ( \ '%{Framed-IP-Address}', \ '%{Acct-Session-Time}', \ '%{Acct-Input-Gigawords:-0}', \ '%{Acct-Input-Octets:-0}', \ '%{Acct-Output-Gigawords:-0}', \ '%{Acct-Output-Octets:-0}', \ '%{Acct-Session-Id}', \ '%{NAS-IP-Address}', \ '%{SQL-User-Name}', \ '%{NAS-IP-Address}' \ )" # accounting_update_query_alt = " \ # INSERT INTO ${acct_table1} \ # (AcctSessionId, AcctUniqueId, UserName, \ # Realm, NASIPAddress, NASPortId, \ # NASPortType, AcctStartTime, AcctSessionTime, \ # AcctAuthentic, ConnectInfo_start, AcctInputOctets, \ # AcctOutputOctets, CalledStationId, CallingStationId, \ # ServiceType, FramedProtocol, FramedIPAddress, \ # AcctStartDelay, XAscendSessionSvrKey) \ # VALUES \ # ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \ # '%{SQL-User-Name}', \ # '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \ # '%{NAS-Port-Type}', \ # DATE_SUB('%S', \ # INTERVAL (%{Acct-Session-Time:-0} + \ # %{Acct-Delay-Time:-0}) SECOND), \ # '%{Acct-Session-Time}', \ # '%{Acct-Authentic}', '', \ # '%{Acct-Input-Gigawords:-0}' << 32 | \ # '%{Acct-Input-Octets:-0}', \ # '%{Acct-Output-Gigawords:-0}' << 32 | \ # '%{Acct-Output-Octets:-0}', \ # '%{Called-Station-Id}', '%{Calling-Station-Id}', \ # '%{Service-Type}', '%{Framed-Protocol}', \ # '%{Framed-IP-Address}', \ # '0', '%{X-Ascend-Session-Svr-Key}')" # accounting_start_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, \ # AcctStartDelay, AcctStopDelay, XAscendSessionSvrKey) \ # VALUES \ # ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \ # '%{SQL-User-Name}', \ # '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \ # '%{NAS-Port-Type}', '%S', '0', \ # '0', '%{Acct-Authentic}', '%{Connect-Info}', \ # '', '0', '0', \ # '%{Called-Station-Id}', '%{Calling-Station-Id}', '', \ # '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \ # '%{Acct-Delay-Time:-0}', '0', '%{X-Ascend-Session-Svr-Key}')" # accounting_start_query_alt = "UPDATE ${acct_table1} SET AcctStartTime = '%S', AcctStartDelay = '%{Acct-Delay-Time}', ConnectInfo_start = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}'" accounting_start_query = "SELECT RESULT FROM RAD6_ACCT_START ( \ '%{Framed-IP-Address}', \ '%S', \ '%{Acct-Session-Id}', \ '%{NAS-IP-Address}', \ '%{Calling-Station-Id}', \ '%{SQL-User-Name}', \ '%{ADSL-Agent-Circuit-Id}', \ '%{ADSL-Agent-Remote-Id}' \ )" # accounting_stop_query = " \ # UPDATE ${acct_table2} SET \ # AcctStopTime = '%S', \ # AcctSessionTime = '%{Acct-Session-Time}', \ # AcctInputOctets = '%{Acct-Input-Gigawords:-0}' << 32 | \ # '%{Acct-Input-Octets:-0}', \ # AcctOutputOctets = '%{Acct-Output-Gigawords:-0}' << 32 | \ # '%{Acct-Output-Octets:-0}', \ # AcctTerminateCause = '%{Acct-Terminate-Cause}', \ # AcctStopDelay = '%{Acct-Delay-Time:-0}', \ # ConnectInfo_stop = '%{Connect-Info}' \ # WHERE AcctSessionId = '%{Acct-Session-Id}' \ # AND UserName = '%{SQL-User-Name}' \ # AND NASIPAddress = '%{NAS-IP-Address}'" # accounting_stop_query_alt = " \ # INSERT INTO ${acct_table2} \ # (AcctSessionId, AcctUniqueId, UserName, \ # Realm, NASIPAddress, NASPortId, \ # NASPortType, AcctStartTime, AcctStopTime, \ # AcctSessionTime, AcctAuthentic, ConnectInfo_start, \ # ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, \ # CalledStationId, CallingStationId, AcctTerminateCause, \ # ServiceType, FramedProtocol, FramedIPAddress, \ # AcctStartDelay, AcctStopDelay) \ # VALUES \ # ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \ # '%{SQL-User-Name}', \ # '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \ # '%{NAS-Port-Type}', \ # DATE_SUB('%S', \ # INTERVAL (%{Acct-Session-Time:-0} + \ # %{Acct-Delay-Time:-0}) SECOND), \ # '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '', \ # '%{Connect-Info}', \ # '%{Acct-Input-Gigawords:-0}' << 32 | \ # '%{Acct-Input-Octets:-0}', \ # '%{Acct-Output-Gigawords:-0}' << 32 | \ # '%{Acct-Output-Octets:-0}', \ # '%{Called-Station-Id}', '%{Calling-Station-Id}', \ # '%{Acct-Terminate-Cause}', \ # '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \ # '0', '%{Acct-Delay-Time:-0}')" accounting_stop_query = "SELECT RESULT FROM RAD6_ACCT_STOP ( \ '%{Framed-IP-Address}', \ '%S', \ '%{Acct-Session-Id}', \ '%{NAS-IP-Address}', \ '%{Acct-Terminate-Cause}', \ '%{Acct-Input-Gigawords:-0}', \ '%{Acct-Input-Octets:-0}', \ '%{Acct-Output-Gigawords:-0}', \ '%{Acct-Output-Octets:-0}' \ )" ####################################################################### # Simultaneous Use Checking Queries ####################################################################### # simul_count_query - query for the number of current connections # - If this is not defined, no simultaneouls 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. ####################################################################### # Uncomment simul_count_query to enable simultaneous use checking #simul_count_query = "SELECT COUNT(*) \ #FROM ${acct_table1} \ #WHERE UserName='%{SQL-User-Name}' \ #AND AcctStopTime = 0" # simul_count_query = "SELECT COUNT(*) \ # FROM RADIUS_SIMUL_COUNT('%{SQL-User-Name}')" simul_count_query = "SELECT COUNT(*) \ FROM RAD6_CHECK ('SIMUL', '%{SQL-User-Name}', \ '%{Calling-Station-Id}', \ '%{Mac-Addr}', \ '%{NAS-Ip-Address}', \ '%{NAS-Port}', \ '%{NAS-Real-Port}', \ '%{NAS-Port-id}', \ '%{DHCP-Option}', \ '%{Agent-Circuit-Id}', \ '%{Agent-Remote-Id}', \ '%{Framed-IP-Address}'\ )" # simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName, \ # NASIPAddress, NASPortId, FramedIPAddress, \ # CallingStationId, FramedProtocol \ # FROM ${acct_table1} \ # WHERE UserName='%{SQL-User-Name}' \ # AND AcctStopTime = 0" # simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName, \ # NASIPAddress, NASPortId, FramedIPAddress, \ # CallingStationId, FramedProtocol \ # FROM RADIUS_SIMUL_VERIFY('%{SQL-User-Name}')" ####################################################################### # Group Membership Queries ####################################################################### # group_membership_query - Check user group membership ####################################################################### # group_membership_query = "SELECT GroupName FROM ${usergroup_table} WHERE UserName='%{SQL-User-Name}'" # group_membership_query = "SELECT GroupName FROM \ # RADIUS_GET_GROUPS ('%{SQL-User-Name}')" ####################################################################### # Authentication Logging Queries ####################################################################### # postauth_query - Insert some info after authentication ####################################################################### # postauth_query = "INSERT into ${postauth_table} (user, pass, reply, date) values ('%{User-Name}', '%{User-Password:-Chap-Password}', '%{reply:Packet-Type}', NOW())" # postauth_query = "execute procedure RADIUS_POSTAUTH ( \ # '%{User-Name}', \ # '%{User-Password:-Chap-Password}', \ # '%{reply:Packet-Type}')" # # Set to 'yes' to read radius clients from the database ('nas' table) #readclients = yes #}