Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?

Navigation  Back up to About Overview 
  
[+] Expand All
[-] Collapse All

SQL Statement Construction

The authentication transaction is based on an SQL query that returns a password (and possibly other information) based on the name entered by the user attempting to log in.

While a sample SQL query is provided in the original configuration file, you must configure the SQL entry of the configuration file with a query appropriate to your database. The query you enter must be either a SQL SELECT or SQL EXECUTE statement that contains additional syntax elements which are preprocessed by the SQL authentication module.

The SQL authentication module executes SQL statements in parameterized form. This means that the SQL statement is compiled once, with parameter markers (usually question marks) as placeholders for data items that vary from one execution to the next. Only upon execution of the statement are the actual data values supplied.

The SQL statement you compose must not include parameter markers directly. Instead, include the names of the parameters where parameter markers would appear, in a format described below. The SQL authentication module translates the SQL statement provided, replacing parameter names with parameter markers before passing the SQL statement to the database engine.

The SQL statement can be very simple. Basically, all that is required is to look up a password and possibly some optional information based on a username. The SQL statement can also be quite complex; it can include inner joins, and it can contain expressions. The underlying database engine is responsible for handling the SQL statement; the SQL authentication module performs no interpretation of the SQL statement other than to translate parameter names to parameter markers.

Example:

SELECT password, profile, fullname FROM usertable WHERE username = %name/63s

As shown in this example, a parameter consists of a percent sign (%), the name of the parameter and a format specifier. Table 49 lists SQL statement parameter names.

Table 49: SQL Statement Parameters

Item

Meaning for SQL Authentication

%OriginalUserName

The original full identification of the user, before any processing (that is, user@realm).

%User

The user portion of OriginalUserName (the section before “@”).

%UserName

The full user identification (user and realm strings) after all stripping and processing has been performed.

%Name

Synonym for UserName.

%EffectiveUser

The name of the user (the section before “@”) as presented to the authentication method. This may be a modified version of the original username.

%Realm

The realm portion of the original user identification (the section after “@”) as presented to the authentication method. This may be a modified version of the original realm name.

%EffectiveRealm

The realm portion of the user identification as presented to the method. This may be a modified version of the original realm name.

%NASName

The name of the network access server that originated the request. This may be the name of the RADIUS clients entry in the database or the value of the NAS-Identifier or NAS-IP-Address attribute.

%NASAddress

The address of the network access server, in dotted notation.

%NASModel

The make/model of the network access server, as specified in the Steel-Belted Radius Carrier database.

%Password

The PAP password.

%AllowedAccessHours

The times that the user is allowed to be logged in.

%RADIUSClientName

The name of the network access server, as specified in a RADIUS clients entry in the Steel-Belted Radius Carrier database.

Along with these parameters, any RADIUS attribute received in the Access-Request can be referred to by using an at-sign (“@”) followed by the name of the attribute. If you need to specify a literal at-sign character in an SQL statement, such as in a UserName, you must use two at-signs in a row. For example:

SELECT foo FROM bar WHERE field = “abc@@xyz”

Likewise, if you need to specify a literal percent character (“%”) in an SQL statement you must use a two percent characters in a row.

The format specifier describes the database storage format of the column that corresponds to the parameter. It consists of a slash (“/”), a length, and a type, which for SQL authentication is always “s” for string. For example, if the user’s name is stored in the database as a string of up to 63 bytes, you would enter:

%name/63s

Note: Be sure to specify a length no greater than the actual field size in the database. The compilation of the SQL statement may fail if a parameter size greater than the actual field size is specified.

Normally, the only parameter you need to include in the WHERE section of a SQL statement is %name. The following SQL statement explains how to look up a password based on a username:

SELECT Password FROM Usertable WHERE Username = %name/32
[Results]Password=1/32

It is not mandatory for you to provide the password in the first column of the SELECT statement. Also, you can change the position of the password in the SELECT statement. When the position of the password is changed, the element-number for the password in the [Results] section of the .aut file must be changed accordingly.

In the following statement, %name is an input parameter used to look up the user’s profile:

SELECT profile FROM database WHERE username = %name

Because there is no password output parameter, no password authentication is performed. The following [Results] section of the .aut file works correctly with the preceding SELECT statement:

[Results]Password=0Profile=1/50Alias=0

If the record cannot be found in the database, the authentication attempt fails.

Note: If you are not using password checking for authentication, the Password parameter must be set to 0 in the [Results] section.

Overlapped Execution of SQL Statements

The SQL authentication module is multi-threaded. SQL authentication can be configured with a maximum number of simultaneous executions of any SQL statement, using the MaxConcurrent entry in the .aut file’s [Settings] section.

Note: The MaxConcurrent entry is valid only for Oracle SQL authentication (radsql.aut). It is not valid for JDBC SQL authentication (radsqljdbc.aut).

If MaxConcurrent is set to 1, SQL execution occurs serially, and the SQL execution for each authentication request must complete before execution for the next request may begin.

By increasing MaxConcurrent, it may be possible to increase throughput by overlapping operations, especially if the database server is remote and a large part of the time to complete a statement execution is taken up by network latency. If the database server is local, the point of diminishing returns may be reached at a small value of MaxConcurrent, possibly even at 1 or 2. The optimum value requires experimentation.

Note: A setting of MaxConcurrent = 1 should be sufficient for all but the most demanding environments. Increase this value slowly and conservatively.

You might expect that databases that are licensed by number of connections would debit a single connection regardless of how many SQL statements are active. This is not necessarily the case; some databases count each open compiled SQL statement against the licensed number of connections. Another factor that determines how MaxConcurrent is set might be the database license.

%result Parameter

The %result parameter is a string value that can be returned as a column or stored procedure output parameter. The %result parameter can be used with or without password authentication.

The value expected to be returned in this parameter when authenticating a user can be specified in the SuccessResult entry of the [Settings] section. For example, if a user is successfully authenticated by the SQL authentication method, the result signifying success is the text string “okay”. This can be automatically checked by the following setting.

[Settings]SuccessResult = okay

Note: The string comparison is case insensitive.

If the SQL statement succeeds but the SuccessResult value does not match the expected value returned from the database, Steel-Belted Radius Carrier issues a reject response, which can include any attributes and values configured in the [FailedSuccessResultAttributes] section of the *.aut file.

If PerformSuccessResultCheckAfterPasswordCheck=1 is specified and the SQL statement performs a password check that fails, Steel-Belted Radius Carrier does not process the SuccessResult and does not return the attributes from the [FailedSuccessResultAttributes] section in the reject response. If PerformSuccessResultCheckAfterPasswordCheck=1 is specified and the SQL statement performs a password check that succeeds but the SuccessResult value does not match the value returned from the database, Steel-Belted Radius Carrier issues a reject response that contains the attributes from the [FailedSuccessResultAttributes] section.

In the following statement, %password is passed to a stored procedure, which returns a %result of either “okay” or something else (that signifies a rejection):

BEGIN CheckUser(%name, %password, %result!o); END;

Another example might be a database of usernames, passwords, and account status. The administrator can enable a user by setting the user’s account status to “okay” or disable the user by setting the account status to some other value, without having to delete the record. In the following statement, both password and result columns are checked:

SELECT password, result FROM database WHERE username = %name[Results]Password=1/50%Result=2/50Profile=0Alias=0

SQL Authentication and Password Format

Steel-Belted Radius Carrier supports the authentication of users residing in a SQL database, in which password values for the users are stored in one of the following formats: clear-text, UNIXcrypt, Secured Hash Algorithm (SHA1+Base64 hash), MD4 hash, or enc-md5 reversibly-encoded password.

Hashed Passwords

Values in the Password column include a prefix that indicates how the password has been processed. The prefix is in clear-text between curly braces { } and is immediately followed by a hash value computed from the password. If no prefix is present in the value retrieved from the table Password column, the entire password is assumed to be in clear-text format. In summary:

  • PasswordText indicates clear-text format (no encryption)
  • {crypt}HashHash indicates UNIXcrypt format
  • {SHA}HashHashHash indicates SHA1+Base64 hash
  • {SSHA}HashHashHashSalt indicates salted SHA1+Base64 hash
  • {md4} HashHash indicates MD4 hash of the Unicode form of password
  • {enc-md5}EncryptedEncrypted indicates a reversibly encrypted password

    Note: Refer to RFC 2759 for details about how MS-CHAP v2 produces an MD4 hash value.

    Note: Although Steel-Belted Radius Carrier reads passwords encoded in enc-md5 format, you must purchase the Software Developer’s Kit to convert clear-text passwords to this format.

UNIXcrypt is the standard hash algorithm that is used for the /etc/passwd file on Solaris systems. This may be necessary if, for example, the standard user database on a Solaris machine (the /etc/passwd file) is migrated to a SQL database, so that the values in the Password column of the SQL table are processed with UNIXcrypt.

Steel-Belted Radius Carrier may be configured to expect that the values retrieved from the SQL table Password column during authentication have been run through UNIXcrypt by adding the following entry into the [Settings] section of the SQL authentication configuration file:

PasswordFormat=3

Automatic Parsing

If PasswordFormat is set to 0, Steel-Belted Radius Carrier attempts to determine the password format automatically by parsing it. This is the recommended setting. Automatic parsing expects the password to be stored in one of the formats described in this section.

Note: The setting for automatic password parsing in older versions of Steel-Belted Radius Carrier (auto) has been deprecated.

Modified: 2017-09-27