#######################################################################
# 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"
+# 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"
+# 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"
+ 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"
+ FROM ${authreply_table} \
+ WHERE username = '%{SQL-User-Name}' \
+ ORDER BY id"
# Use these for case sensitive usernames.
# group_membership_query = "SELECT groupname \
-# FROM ${usergroup_table} \
-# WHERE username = BINARY '%{SQL-User-Name}' \
-# ORDER BY priority"
+# FROM ${usergroup_table} \
+# WHERE username = BINARY '%{SQL-User-Name}' \
+# ORDER BY priority"
group_membership_query = "SELECT groupname \
- FROM ${usergroup_table} \
- WHERE username = '%{SQL-User-Name}' \
- ORDER BY priority"
+ FROM ${usergroup_table} \
+ WHERE username = '%{SQL-User-Name}' \
+ ORDER BY priority"
authorize_group_check_query = "SELECT id, groupname, attribute, \
- Value, op \
- FROM ${groupcheck_table} \
- WHERE groupname = '%{Sql-Group}' \
- ORDER BY id"
+ Value, op \
+ FROM ${groupcheck_table} \
+ WHERE groupname = '%{Sql-Group}' \
+ ORDER BY id"
authorize_group_reply_query = "SELECT id, groupname, attribute, \
- value, op \
- FROM ${groupreply_table} \
- WHERE groupname = '%{Sql-Group}' \
- ORDER BY id"
+ value, op \
+ FROM ${groupreply_table} \
+ WHERE groupname = '%{Sql-Group}' \
+ ORDER BY 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)
+ # (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)
+ # (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)
+ # (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}:-0} \
- WHERE acctstoptime IS NULL \
- AND nasipaddress = '%{NAS-IP-Address}' \
- AND acctstarttime <= '%S'"
+ UPDATE ${acct_table1} \
+ SET \
+ acctstoptime = '%S', \
+ acctsessiontime = unix_timestamp('%S') - \
+ unix_timestamp(acctstarttime), \
+ acctterminatecause = '%{Acct-Terminate-Cause}', \
+ acctstopdelay = %{%{Acct-Delay-Time}:-0} \
+ WHERE acctstoptime IS NULL \
+ AND nasipaddress = '%{NAS-IP-Address}' \
+ AND acctstarttime <= '%S'"
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}'"
+ 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_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_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', NULL, \
- '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}')"
+ 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', NULL, \
+ '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}:-0}', \
- connectinfo_start = '%{Connect-Info}' \
- WHERE acctsessionid = '%{Acct-Session-Id}' \
- AND username = '%{SQL-User-Name}' \
- AND nasipaddress = '%{NAS-IP-Address}'"
+ UPDATE ${acct_table1} SET \
+ acctstarttime = '%S', \
+ acctstartdelay = '%{%{Acct-Delay-Time}:-0}', \
+ connectinfo_start = '%{Connect-Info}' \
+ WHERE acctsessionid = '%{Acct-Session-Id}' \
+ AND username = '%{SQL-User-Name}' \
+ AND nasipaddress = '%{NAS-IP-Address}'"
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}'"
+ 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}')"
+ 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}')"
#######################################################################
# Simultaneous Use Checking Queries
# Uncomment simul_count_query to enable simultaneous use checking
#simul_count_query = "SELECT COUNT(*) \
- #FROM ${acct_table1} \
- #WHERE username = '%{SQL-User-Name}' \
- #AND acctstoptime IS NULL"
+ #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"
+ nasipaddress, nasportid, framedipaddress, \
+ callingstationid, framedprotocol \
+ FROM ${acct_table1} \
+ WHERE username = '%{SQL-User-Name}' \
+ AND acctstoptime IS NULL"
#######################################################################
# Authentication Logging Queries
#######################################################################
postauth_query = "INSERT INTO ${postauth_table} \
- (username, pass, reply, authdate) \
- VALUES ( \
- '%{User-Name}', \
- '%{%{User-Password}:-%{Chap-Password}}', \
- '%{reply:Packet-Type}', '%S')"
+ (username, pass, reply, authdate) \
+ VALUES ( \
+ '%{User-Name}', \
+ '%{%{User-Password}:-%{Chap-Password}}', \
+ '%{reply:Packet-Type}', '%S')"