| 1 | # |
|---|
| 2 | # Configuration for the SQL module, when using MSSQL. |
|---|
| 3 | # |
|---|
| 4 | # The database schema is available at: |
|---|
| 5 | # |
|---|
| 6 | # doc/examples/mssql.sql |
|---|
| 7 | # |
|---|
| 8 | # $Id: mssql.conf,v 1.6.4.1 2006/02/04 14:53:44 nbk Exp $ |
|---|
| 9 | # |
|---|
| 10 | sql { |
|---|
| 11 | |
|---|
| 12 | # Database type |
|---|
| 13 | # Current supported are: rlm_sql_mysql, rlm_sql_postgresql, |
|---|
| 14 | # rlm_sql_iodbc, rlm_sql_oracle, rlm_sql_unixodbc |
|---|
| 15 | driver = "rlm_sql_unixodbc" |
|---|
| 16 | |
|---|
| 17 | # Connect info |
|---|
| 18 | server = "localhost" |
|---|
| 19 | login = "root" |
|---|
| 20 | password = "rootpass" |
|---|
| 21 | |
|---|
| 22 | # Database table configuration |
|---|
| 23 | radius_db = "radius" |
|---|
| 24 | |
|---|
| 25 | # If you want both stop and start records logged to the |
|---|
| 26 | # same SQL table, leave this as is. If you want them in |
|---|
| 27 | # different tables, put the start table in acct_table1 |
|---|
| 28 | # and stop table in acct_table2 |
|---|
| 29 | acct_table1 = "radacct" |
|---|
| 30 | acct_table2 = "radacct" |
|---|
| 31 | |
|---|
| 32 | authcheck_table = "radcheck" |
|---|
| 33 | authreply_table = "radreply" |
|---|
| 34 | |
|---|
| 35 | groupcheck_table = "radgroupcheck" |
|---|
| 36 | groupreply_table = "radgroupreply" |
|---|
| 37 | |
|---|
| 38 | usergroup_table = "usergroup" |
|---|
| 39 | |
|---|
| 40 | # Remove stale session if checkrad does not see a double login |
|---|
| 41 | deletestalesessions = yes |
|---|
| 42 | |
|---|
| 43 | # Print all SQL statements when in debug mode (-x) |
|---|
| 44 | sqltrace = no |
|---|
| 45 | sqltracefile = ${logdir}/sqltrace.sql |
|---|
| 46 | |
|---|
| 47 | # number of sql connections to make to server |
|---|
| 48 | num_sql_socks = 5 |
|---|
| 49 | |
|---|
| 50 | # Safe characters list for sql queries. Everything else is replaced |
|---|
| 51 | # with their mime-encoded equivalents. |
|---|
| 52 | # The default list should be ok |
|---|
| 53 | #safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /" |
|---|
| 54 | |
|---|
| 55 | ####################################################################### |
|---|
| 56 | # Query config: Username |
|---|
| 57 | ####################################################################### |
|---|
| 58 | # This is the username that will get substituted, escaped, and added |
|---|
| 59 | # as attribute 'SQL-User-Name'. '%{SQL-User-Name}' should be used |
|---|
| 60 | # below everywhere a username substitution is needed so you you can |
|---|
| 61 | # be sure the username passed from the client is escaped properly. |
|---|
| 62 | # |
|---|
| 63 | # Uncomment the next line, if you want the sql_user_name to mean: |
|---|
| 64 | # |
|---|
| 65 | # Use Stripped-User-Name, if it's there. |
|---|
| 66 | # Else use User-Name, if it's there, |
|---|
| 67 | # Else use hard-coded string "none" as the user name. |
|---|
| 68 | #sql_user_name = "%{Stripped-User-Name:-%{User-Name:-none}}" |
|---|
| 69 | # |
|---|
| 70 | sql_user_name = "%{User-Name}" |
|---|
| 71 | |
|---|
| 72 | |
|---|
| 73 | ####################################################################### |
|---|
| 74 | # Authorization Queries |
|---|
| 75 | ####################################################################### |
|---|
| 76 | # These queries compare the check items for the user |
|---|
| 77 | # in ${authcheck_table} and setup the reply items in |
|---|
| 78 | # ${authreply_table}. You can use any query/tables |
|---|
| 79 | # you want, but the return data for each row MUST |
|---|
| 80 | # be in the following order: |
|---|
| 81 | # |
|---|
| 82 | # 0. Row ID (currently unused) |
|---|
| 83 | # 1. UserName/GroupName |
|---|
| 84 | # 2. Item Attr Name |
|---|
| 85 | # 3. Item Attr Value |
|---|
| 86 | # 4. Item Attr Operation |
|---|
| 87 | ####################################################################### |
|---|
| 88 | # Query for case sensitive usernames was removed. Please contact with me, |
|---|
| 89 | # if you know analog of STRCMP functions for MS SQL. |
|---|
| 90 | |
|---|
| 91 | authorize_check_query = "SELECT id,UserName,Attribute,Value,op FROM ${authcheck_table} WHERE Username = '%{SQL-User-Name}' ORDER BY id" |
|---|
| 92 | authorize_reply_query = "SELECT id,UserName,Attribute,Value,op FROM ${authreply_table} WHERE Username = '%{SQL-User-Name}' ORDER BY id" |
|---|
| 93 | |
|---|
| 94 | 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" |
|---|
| 95 | 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" |
|---|
| 96 | |
|---|
| 97 | |
|---|
| 98 | ####################################################################### |
|---|
| 99 | # Accounting Queries |
|---|
| 100 | ####################################################################### |
|---|
| 101 | # accounting_onoff_query - query for Accounting On/Off packets |
|---|
| 102 | # accounting_update_query - query for Accounting update packets |
|---|
| 103 | # accounting_update_query_alt - query for Accounting update packets |
|---|
| 104 | # (alternate in case first query fails) |
|---|
| 105 | # accounting_start_query - query for Accounting start packets |
|---|
| 106 | # accounting_start_query_alt - query for Accounting start packets |
|---|
| 107 | # (alternate in case first query fails) |
|---|
| 108 | # accounting_stop_query - query for Accounting stop packets |
|---|
| 109 | # accounting_stop_query_alt - query for Accounting start packets |
|---|
| 110 | # (alternate in case first query doesn't |
|---|
| 111 | # affect any existing rows in the table) |
|---|
| 112 | ####################################################################### |
|---|
| 113 | 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'" |
|---|
| 114 | |
|---|
| 115 | accounting_update_query = "UPDATE ${acct_table1} SET FramedIPAddress = '%{Framed-IP-Address}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime = 0" |
|---|
| 116 | |
|---|
| 117 | accounting_update_query_alt = "INSERT into ${acct_table1} (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPort, NASPortType, AcctSessionTime, AcctAuthentic, ConnectInfo_start, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port-Id}', '%{NAS-Port-Type}', '%{Acct-Session-Time}', '%{Acct-Authentic}', '', '%{Acct-Input-Octets}', '%{Acct-Output-Octets}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '0')" |
|---|
| 118 | |
|---|
| 119 | # accounting_start_query: Inserting of RadAcctId and AcctStopTime was |
|---|
| 120 | # removed. These fields are processing by a database |
|---|
| 121 | accounting_start_query = "INSERT into ${acct_table1} (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPort, NASPortType, AcctStartTime, 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-Id}', '%{NAS-Port-Type}', '%S', '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0')" |
|---|
| 122 | |
|---|
| 123 | 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}' AND AcctStopTime = 0" |
|---|
| 124 | |
|---|
| 125 | accounting_stop_query = "UPDATE ${acct_table2} SET AcctStopTime = '%S', AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets = '%{Acct-Input-Octets}', AcctOutputOctets = '%{Acct-Output-Octets}', AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time}', ConnectInfo_stop = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime = 0" |
|---|
| 126 | |
|---|
| 127 | # accounting_stop_query_alt: Inserting of RadAcctId and AcctStartTime was |
|---|
| 128 | # removed. These fields are processing by a database |
|---|
| 129 | accounting_stop_query_alt = "INSERT into ${acct_table2} (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPort, NASPortType, 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-Id}', '%{NAS-Port-Type}', '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '', '%{Connect-Info}', '%{Acct-Input-Octets}', '%{Acct-Output-Octets}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '0', '%{Acct-Delay-Time}')" |
|---|
| 130 | |
|---|
| 131 | } |
|---|
