Chetan Gaonker | 7f4bf74 | 2016-05-04 15:56:08 -0700 | [diff] [blame] | 1 | # -*- text -*- |
| 2 | # |
| 3 | # main/oracle/queries.conf -- Oracle configuration for default schema (schema.sql) |
| 4 | # |
| 5 | # $Id: ca22f5f5c9bf5dff47e60fb2bed56d6b161a4d08 $ |
| 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 | # Default profile |
| 26 | ####################################################################### |
| 27 | # This is the default profile. It is found in SQL by group membership. |
| 28 | # That means that this profile must be a member of at least one group |
| 29 | # which will contain the corresponding check and reply items. |
| 30 | # This profile will be queried in the authorize section for every user. |
| 31 | # The point is to assign all users a default profile without having to |
| 32 | # manually add each one to a group that will contain the profile. |
| 33 | # The SQL module will also honor the User-Profile attribute. This |
| 34 | # attribute can be set anywhere in the authorize section (ie the users |
| 35 | # file). It is found exactly as the default profile is found. |
| 36 | # If it is set then it will *overwrite* the default profile setting. |
| 37 | # The idea is to select profiles based on checks on the incoming packets, |
| 38 | # not on user group membership. For example: |
| 39 | # -- users file -- |
| 40 | # DEFAULT Service-Type == Outbound-User, User-Profile := "outbound" |
| 41 | # DEFAULT Service-Type == Framed-User, User-Profile := "framed" |
| 42 | # |
| 43 | # By default the default_user_profile is not set |
| 44 | # |
| 45 | #default_user_profile = "DEFAULT" |
| 46 | # |
| 47 | # Determines if we will query the default_user_profile or the User-Profile |
| 48 | # if the user is not found. If the profile is found then we consider the user |
| 49 | # found. By default this is set to 'no'. |
| 50 | # |
| 51 | #query_on_not_found = no |
| 52 | |
| 53 | |
| 54 | ####################################################################### |
| 55 | # NAS Query |
| 56 | ####################################################################### |
| 57 | # This query retrieves the radius clients |
| 58 | # |
| 59 | # 0. Row ID (currently unused) |
| 60 | # 1. Name (or IP address) |
| 61 | # 2. Shortname |
| 62 | # 3. Type |
| 63 | # 4. Secret |
| 64 | # 5. Virtual server |
| 65 | ####################################################################### |
| 66 | |
| 67 | client_query = "\ |
| 68 | SELECT id, nasname, shortname, type, secret, server \ |
| 69 | FROM ${client_table}" |
| 70 | |
| 71 | ####################################################################### |
| 72 | # Authorization Queries |
| 73 | ####################################################################### |
| 74 | # These queries compare the check items for the user |
| 75 | # in ${authcheck_table} and setup the reply items in |
| 76 | # ${authreply_table}. You can use any query/tables |
| 77 | # you want, but the return data for each row MUST |
| 78 | # be in the following order: |
| 79 | # |
| 80 | # 0. Row ID (currently unused) |
| 81 | # 1. UserName/GroupName |
| 82 | # 2. Item Attr Name |
| 83 | # 3. Item Attr Value |
| 84 | # 4. Item Attr Operation |
| 85 | ####################################################################### |
| 86 | # |
| 87 | # WARNING: Oracle is case sensitive |
| 88 | # |
| 89 | # The main difference between MySQL and Oracle queries is the date format. |
| 90 | # You must use the TO_DATE function to transform the radius date format to |
| 91 | # the Oracle date format, and put NULL otherwise '0' in a void date field. |
| 92 | # |
| 93 | ####################################################################### |
| 94 | |
| 95 | authorize_check_query = "\ |
| 96 | SELECT id, UserName, Attribute, Value, op \ |
| 97 | FROM ${authcheck_table} \ |
| 98 | WHERE Username = '%{SQL-User-Name}' \ |
| 99 | ORDER BY id" |
| 100 | |
| 101 | authorize_reply_query = "\ |
| 102 | SELECT id, UserName, Attribute, Value, op \ |
| 103 | FROM ${authreply_table} \ |
| 104 | WHERE Username = '%{SQL-User-Name}' \ |
| 105 | ORDER BY id" |
| 106 | |
| 107 | authorize_group_check_query = "\ |
| 108 | SELECT \ |
| 109 | ${groupcheck_table}.id, ${groupcheck_table}.GroupName, ${groupcheck_table}.Attribute, \ |
| 110 | ${groupcheck_table}.Value,${groupcheck_table}.op \ |
| 111 | FROM ${groupcheck_table}, ${usergroup_table} \ |
| 112 | WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' \ |
| 113 | AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName \ |
| 114 | ORDER BY ${groupcheck_table}.id" |
| 115 | |
| 116 | authorize_group_reply_query = "\ |
| 117 | SELECT \ |
| 118 | ${groupreply_table}.id, ${groupreply_table}.GroupName, ${groupreply_table}.Attribute, \ |
| 119 | ${groupreply_table}.Value, ${groupreply_table}.op \ |
| 120 | FROM ${groupreply_table}, ${usergroup_table} \ |
| 121 | WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' \ |
| 122 | AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName \ |
| 123 | ORDER BY ${groupreply_table}.id" |
| 124 | |
| 125 | ####################################################################### |
| 126 | # Simultaneous Use Checking Queries |
| 127 | ####################################################################### |
| 128 | # simul_count_query - query for the number of current connections |
| 129 | # - If this is not defined, no simultaneouls use checking |
| 130 | # - will be performed by this module instance |
| 131 | # simul_verify_query - query to return details of current connections for verification |
| 132 | # - Leave blank or commented out to disable verification step |
| 133 | # - Note that the returned field order should not be changed. |
| 134 | ####################################################################### |
| 135 | |
| 136 | # |
| 137 | # Uncomment simul_count_query to enable simultaneous use checking |
| 138 | # |
| 139 | #simul_count_query = "\ |
| 140 | # SELECT COUNT(*) \ |
| 141 | # FROM ${acct_table1} \ |
| 142 | # WHERE UserName = '%{SQL-User-Name}' \ |
| 143 | # AND AcctStopTime IS NULL" |
| 144 | |
| 145 | simul_verify_query = "\ |
| 146 | SELECT \ |
| 147 | RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, \ |
| 148 | FramedIPAddress, CallingStationId, FramedProtocol \ |
| 149 | FROM ${acct_table1} \ |
| 150 | WHERE UserName='%{SQL-User-Name}' \ |
| 151 | AND AcctStopTime IS NULL" |
| 152 | |
| 153 | ####################################################################### |
| 154 | # Group Membership Queries |
| 155 | ####################################################################### |
| 156 | # group_membership_query - Check user group membership |
| 157 | ####################################################################### |
| 158 | |
| 159 | group_membership_query = "\ |
| 160 | SELECT GroupName \ |
| 161 | FROM ${usergroup_table} \ |
| 162 | WHERE UserName='%{SQL-User-Name}'" |
| 163 | |
| 164 | ####################################################################### |
| 165 | # Accounting and Post-Auth Queries |
| 166 | ####################################################################### |
| 167 | # These queries insert/update accounting and authentication records. |
| 168 | # The query to use is determined by the value of 'reference'. |
| 169 | # This value is used as a configuration path and should resolve to one |
| 170 | # or more 'query's. If reference points to multiple queries, and a query |
| 171 | # fails, the next query is executed. |
| 172 | # |
| 173 | # Behaviour is identical to the old 1.x/2.x module, except we can now |
| 174 | # fail between N queries, and query selection can be based on any |
| 175 | # combination of attributes, or custom 'Acct-Status-Type' values. |
| 176 | ####################################################################### |
| 177 | accounting { |
| 178 | reference = "%{tolower:type.%{Acct-Status-Type}.query}" |
| 179 | |
| 180 | # Write SQL queries to a logfile. This is potentially useful for bulk inserts |
| 181 | # when used with the rlm_sql_null driver. |
| 182 | # logfile = ${logdir}/accounting.sql |
| 183 | |
| 184 | type { |
| 185 | accounting-on { |
| 186 | query = "\ |
| 187 | UPDATE ${....acct_table1} \ |
| 188 | SET \ |
| 189 | AcctStopTime = TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \ |
| 190 | AcctSessionTime = round((TO_DATE('%S','yyyy-mm-dd hh24:mi:ss') - \ |
| 191 | TO_DATE(TO_CHAR(acctstarttime, 'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss'))*86400), \ |
| 192 | AcctTerminateCause='%{%{Acct-Terminate-Cause}:-NAS-Reboot}', \ |
| 193 | AcctStopDelay = %{%{Acct-Delay-Time}:-0} \ |
| 194 | WHERE AcctStopTime IS NULL \ |
| 195 | AND NASIPAddress = '%{NAS-IP-Address}' \ |
| 196 | AND AcctStartTime <= TO_DATE('%S','yyyy-mm-dd hh24:mi:ss')" |
| 197 | } |
| 198 | |
| 199 | accounting-off { |
| 200 | query = "${..accounting-on.query}" |
| 201 | } |
| 202 | |
| 203 | start { |
| 204 | query = "\ |
| 205 | INSERT INTO ${....acct_table1} \ |
| 206 | (RadAcctId, AcctSessionId, AcctUniqueId, \ |
| 207 | UserName, Realm, NASIPAddress, \ |
| 208 | NASPortId, NASPortType, AcctStartTime, \ |
| 209 | AcctStopTime, AcctSessionTime, AcctAuthentic, \ |
| 210 | ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, \ |
| 211 | AcctOutputOctets, CalledStationId, CallingStationId, \ |
| 212 | AcctTerminateCause, ServiceType, FramedProtocol, \ |
| 213 | FramedIPAddress, AcctStartDelay, AcctStopDelay, \ |
| 214 | XAscendSessionSvrKey) \ |
| 215 | VALUES(\ |
| 216 | '', \ |
| 217 | '%{Acct-Session-Id}', \ |
| 218 | '%{Acct-Unique-Session-Id}', \ |
| 219 | '%{SQL-User-Name}', \ |
| 220 | '%{Realm}', \ |
| 221 | '%{NAS-IP-Address}', \ |
| 222 | '%{NAS-Port-Id}', \ |
| 223 | '%{NAS-Port-Type}', \ |
| 224 | TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \ |
| 225 | NULL, \ |
| 226 | '0', \ |
| 227 | '%{Acct-Authentic}', \ |
| 228 | '%{Connect-Info}', \ |
| 229 | '', \ |
| 230 | '0', \ |
| 231 | '0', \ |
| 232 | '%{Called-Station-Id}', \ |
| 233 | '%{Calling-Station-Id}', \ |
| 234 | '', \ |
| 235 | '%{Service-Type}', \ |
| 236 | '%{Framed-Protocol}', \ |
| 237 | '%{Framed-IP-Address}', \ |
| 238 | '%{Acct-Delay-Time}', \ |
| 239 | '0', \ |
| 240 | '%{X-Ascend-Session-Svr-Key}')" |
| 241 | |
| 242 | query = "\ |
| 243 | UPDATE ${....acct_table1} \ |
| 244 | SET \ |
| 245 | AcctStartTime = TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \ |
| 246 | AcctStartDelay = '%{%{Acct-Delay-Time}:-0}', \ |
| 247 | ConnectInfo_start = '%{Connect-Info}' \ |
| 248 | WHERE AcctSessionId = '%{Acct-Session-Id}' \ |
| 249 | AND UserName = '%{SQL-User-Name}' \ |
| 250 | AND NASIPAddress = '%{NAS-IP-Address}' \ |
| 251 | AND AcctStopTime IS NULL" |
| 252 | } |
| 253 | |
| 254 | interim-update { |
| 255 | query = "\ |
| 256 | UPDATE ${....acct_table1} \ |
| 257 | SET \ |
| 258 | FramedIPAddress = NULLIF('%{Framed-IP-Address}', ''), \ |
| 259 | AcctSessionTime = '%{Acct-Session-Time}', \ |
| 260 | AcctInputOctets = '%{Acct-Input-Octets}' + \ |
| 261 | ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \ |
| 262 | AcctOutputOctets = '%{Acct-Output-Octets}' + \ |
| 263 | ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296) \ |
| 264 | WHERE AcctSessionId = '%{Acct-Session-Id}' \ |
| 265 | AND UserName = '%{SQL-User-Name}' \ |
| 266 | AND NASIPAddress= '%{NAS-IP-Address}' \ |
| 267 | AND AcctStopTime IS NULL" |
| 268 | |
| 269 | query = "\ |
| 270 | INSERT into ${....acct_table1} \ |
| 271 | (RadAcctId, AcctSessionId, AcctUniqueId, \ |
| 272 | UserName, Realm, NASIPAddress, \ |
| 273 | NASPortId, NASPortType, AcctStartTime, \ |
| 274 | AcctSessionTime, AcctAuthentic, ConnectInfo_start, \ |
| 275 | AcctInputOctets, AcctOutputOctets, CalledStationId, \ |
| 276 | CallingStationId, ServiceType, FramedProtocol, \ |
| 277 | FramedIPAddress, AcctStartDelay, XAscendSessionSvrKey) \ |
| 278 | VALUES(\ |
| 279 | '', \ |
| 280 | '%{Acct-Session-Id}', \ |
| 281 | '%{Acct-Unique-Session-Id}', \ |
| 282 | '%{SQL-User-Name}', \ |
| 283 | '%{Realm}', \ |
| 284 | '%{NAS-IP-Address}', \ |
| 285 | '%{NAS-Port-Id}', \ |
| 286 | '%{NAS-Port-Type}', \ |
| 287 | NULL, \ |
| 288 | '%{Acct-Session-Time}', \ |
| 289 | '%{Acct-Authentic}', \ |
| 290 | '', \ |
| 291 | '%{Acct-Input-Octets}' + \ |
| 292 | ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \ |
| 293 | '%{Acct-Output-Octets}' + \ |
| 294 | ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296), \ |
| 295 | '%{Called-Station-Id}', \ |
| 296 | '%{Calling-Station-Id}', \ |
| 297 | '%{Service-Type}', \ |
| 298 | '%{Framed-Protocol}', \ |
| 299 | '%{Framed-IP-Address}', \ |
| 300 | '0', \ |
| 301 | '%{X-Ascend-Session-Svr-Key}')" |
| 302 | } |
| 303 | |
| 304 | stop { |
| 305 | query = "\ |
| 306 | UPDATE ${....acct_table2} \ |
| 307 | SET \ |
| 308 | AcctStopTime = TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \ |
| 309 | AcctSessionTime = '%{Acct-Session-Time}', \ |
| 310 | AcctInputOctets = '%{Acct-Input-Octets}' + \ |
| 311 | ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \ |
| 312 | AcctOutputOctets = '%{Acct-Output-Octets}' + \ |
| 313 | ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296), \ |
| 314 | AcctTerminateCause = '%{Acct-Terminate-Cause}', \ |
| 315 | AcctStopDelay = '%{%{Acct-Delay-Time}:-0}', \ |
| 316 | ConnectInfo_stop = '%{Connect-Info}' \ |
| 317 | WHERE AcctSessionId = '%{Acct-Session-Id}' \ |
| 318 | AND UserName = '%{SQL-User-Name}' \ |
| 319 | AND NASIPAddress = '%{NAS-IP-Address}' \ |
| 320 | AND AcctStopTime IS NULL" |
| 321 | |
| 322 | query = "\ |
| 323 | INSERT into ${....acct_table2} \ |
| 324 | (RadAcctId, AcctSessionId, AcctUniqueId, \ |
| 325 | UserName, Realm, NASIPAddress, \ |
| 326 | NASPortId, NASPortType, AcctStartTime, \ |
| 327 | AcctStopTime, AcctSessionTime, AcctAuthentic, \ |
| 328 | ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, \ |
| 329 | AcctOutputOctets, CalledStationId, CallingStationId, \ |
| 330 | AcctTerminateCause, ServiceType, FramedProtocol, \ |
| 331 | FramedIPAddress, AcctStartDelay, AcctStopDelay) \ |
| 332 | VALUES(\ |
| 333 | '', \ |
| 334 | '%{Acct-Session-Id}', \ |
| 335 | '%{Acct-Unique-Session-Id}', \ |
| 336 | '%{SQL-User-Name}', \ |
| 337 | '%{Realm}', \ |
| 338 | '%{NAS-IP-Address}', \ |
| 339 | '%{NAS-Port-Id}', \ |
| 340 | '%{NAS-Port-Type}', \ |
| 341 | NULL, \ |
| 342 | TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \ |
| 343 | '%{Acct-Session-Time}', \ |
| 344 | '%{Acct-Authentic}', \ |
| 345 | '', \ |
| 346 | '%{Connect-Info}', \ |
| 347 | '%{Acct-Input-Octets}' + \ |
| 348 | ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \ |
| 349 | '%{Acct-Output-Octets}' + \ |
| 350 | ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296), \ |
| 351 | '%{Called-Station-Id}', \ |
| 352 | '%{Calling-Station-Id}', \ |
| 353 | '%{Acct-Terminate-Cause}', \ |
| 354 | '%{Service-Type}', \ |
| 355 | '%{Framed-Protocol}', \ |
| 356 | '%{Framed-IP-Address}', \ |
| 357 | '0', \ |
| 358 | '%{%{Acct-Delay-Time}:-0}')" |
| 359 | |
| 360 | } |
| 361 | } |
| 362 | } |
| 363 | |
| 364 | ####################################################################### |
| 365 | # Authentication Logging Queries |
| 366 | ####################################################################### |
| 367 | # postauth_query - Insert some info after authentication |
| 368 | ####################################################################### |
| 369 | |
| 370 | post-auth { |
| 371 | # Write SQL queries to a logfile. This is potentially useful for bulk inserts |
| 372 | # when used with the rlm_sql_null driver. |
| 373 | # logfile = ${logdir}/post-auth.sql |
| 374 | query = "\ |
| 375 | INSERT INTO ${..postauth_table} \ |
| 376 | (username, pass, reply, authdate) \ |
| 377 | VALUES (\ |
| 378 | '%{User-Name}', \ |
| 379 | '%{%{User-Password}:-%{Chap-Password}}', \ |
| 380 | '%{reply:Packet-Type}', \ |
| 381 | TO_TIMESTAMP('%S','YYYY-MM-DDHH24:MI:SS'))" |
| 382 | } |