SQL Access Overview

You can use SQL mappings to define how to associate or "map" RADIUS attributes to and from the input and output of your SQL statement . The execution of the SQL statement and associated mappings occur in three steps:

In the typical case, you map RADIUS attributes (input source) to SQL statement placeholders (input target). The HP-UX AAA Server binds the RADIUS data to the SQL statement in preparation for execution.

After execution of the SQL statement, the HP-UX AAA Server processes the output mappings, which typically consist of a mapping to check the result of the SQL statement execution and one or more mappings of database columns (output source) to RADIUS reply attributes (output target). A new RADIUS attribute will be allocated for each output mapping.

For maximum flexibility and customization, there are no pre-determined or hard coded relationships between database columns and RADIUS attributes; that relationship is created entirely through the sqlaccess.config file. See “sqlaccess.config File Configuration” for complete configuration definitions of the sqlaccess.config file.

Figure 17-2 “RADIUS Attribute to SQL Statement Mapping” illustrates the SQL mapping concept for RADIUS attribute to database column mapping for a specific access request using OCI, in this example by user John.

Figure 17-2 RADIUS Attribute to SQL Statement Mapping

During input mapping, the value for the RADIUS attribute User-name is passed to the SQL statement SELECT as a search value into the database table USERTABLE using the SQL placeholder to bind to the data value John. The output mapping entry tells the SQL Access AATV that the database column db_passwd maps to the RADIUS attribute password, with a returned value of Johnpass in the attribute-value pair.

You can also use a pre-defined or user-defined mapping function as the source or target of a mapping. For example, the pre-defined mapping function get_sid retrieves the session ID from the RADIUS request's CLASS attribute-value pair or generates a unique session ID if the CLASS attribute-value pair does not exist. You can then insert the session ID value into a database table using the SQL INSERT command to allow for session management via SQL Access.

Pre-defined or user-defined conversion functions execute on the data in transit between the source and the target of a mapping. For example, the pre-defined conversion function AAAIPv6toString converts a binary format IPv6 address to an ANSI string suitable for generating human readable output. This can be used to translate an IPv6 address from a RADIUS attribute to a string formatted column in the database.

The SQL Access AATV processes all mapping entries of an SQL action in the order in which they are defined in the sqlaccess.config file. It first processes all input mapping entries in order, then executes the SQL statement, and finally processes the output mapping entries in order.

SQL actions start with an event of ACK and mapping entries usually return an event of ACK. If any mapping entry returns an event other than ACK, the SQL processing is stopped and control is immediately returned to the FSM . You can control this behavior with customized mapping functions to set pre-defined or custom event codes other than ACK and ACK.

If all mapping entries are processed successfully, the SQL Access AATV returns control to the FSM at the end of the SQL action with an ACK event or a customized value for the event code.

Note that by default, the HP-UX AAA Server will not take any action based on the SQL statement execution result code returned by the database client library. However, you can configure an SQL result output mapping to define the behavior of the HP-UX AAA Server based on the SQL statement result. For more information on how to control execution based on SQL statement result codes from SQL statement execution, see “SQL Result Mapping”.

	NOTE: An SQL query can return more than one matching row, however, only the first row of a result is used for output mapping. You can use SQL result mapping to check the number of matched rows and use this information for further processing in a pre-defined or custom mapping function if required. See “SQL Result Mapping” for more information.