SQL Attribute Connector
The SQL Attribute Connector represents all the information an Authorization Service needs to fetch attributes from a SQL database.
To properly configure a SQL Attribute Connector, a familiarity with SQL is required along with the ability to write SQL queries.
The configuration consists of two parts:
A general part specifying the client implementation to use, the location of the SQL database, the authentication mechanism and account to use.
An attribute specific part that specifies which XACML attribute can be fetched and how to obtain it from the database.
General configuration
Click the "Configuration" link in the Attribute Connector editor to open the base form for the chosen attribute connector type.
| Field | Description | Supported format |
|---|---|---|
| Connection type | The type of connection to the SQL database. There are different parameter fields depending on the type selected. | This field is selected from a drop-down combo box. Only the JDBC data source is supported in this version. |
| Driver | The driver specifies the class name of the JDBC driver to be used by PDPs in order to connect to the SQL database. Drivers can be made available to the ASM built-in SQL Attribute Connector. Note that the class name can be entered manually or chosen from the list of already installed drivers. Installing drivers in ASM is not strictly necessary, but the drivers provide a means to test the connection to the database. See The Check connection button below. | A fully qualified class name of the SQL driver class. For example: oracle.jdbc.OracleDriver |
| Connection URL | The connection URL contains the URL to access the SQL database. | A valid JDBC URL. For example: jdbc:oracle:thin:@//localhost:1521/my-instance |
The notice in the ASM UI about a performance degradation issue using JDBC can be disregarded. It refers to a situation with a legacy authorization engine, and is no longer relevant in this version of ASM.
The Check connection button
As mentioned in the table above, additional JDBC drivers can be made available to the SQL Attribute Connector. The availability of these drivers in the ASM UI supports the check connection function. When a driver has been selected from the list, and the URL has been set in the Connection URL field, the Check connection button becomes enabled, allowing the user to verify the connection to the database.
Attribute-specific configuration
Click the Add button at the bottom of the Attribute Connector editor. This switches to the Attribute Mapping page, where the attribute-specific configuration is managed.
| Field | Description | Supported format |
|---|---|---|
| Attribute provided by the mapping | The URI of the XACML attribute to be fetched from this attribute source. | Selection is done from the list of attributes defined in the Attribute Dictionary. |
| Query | The SQL query or stored procedure needed to fetch the attribute value from the database. | See the SQL queries and Stored procedures sections below. |
| Cache | A Cache Configuration telling PDPs using the Attribute Source how to cache attribute values fetched using the query. | A choice of existing Cache Configurations. |
SQL queries
SQL queries to retrieve attributes from SQL attribute sources are passed as is to the SQL database driver. Therefore, the user is responsible for the syntax specific to the SQL dialect employed by the user's SQL database system.
Like the LDAP Attribute Connector, the SQL Attribute in most cases needs to make use of the value of other available attributes from the specific request context. The user will denote these runtime-supplied attributes in a way similar to the way the user denotes them for the LDAP search filter, namely by using the ? symbol as the placeholder for the values in the SQL query. An equivalent form of the SQL query used in the previous example of username/userid for the LDAP Attribute Connector would be as follows:
SELECT name FROM tbl_user WHERE id=?
The mapping of the KEY to an XACML attribute bears similarities to the mapping process for the LDAP search filter, with one addition: the user needs to specify the corresponding SQL datatype of the supplement attribute. (See the figure below.)
Key values
An attribute passed to the SQL Attribute Finder in runtime as a key to lookup another attribute can either contain no key value (an empty set), a single key value, or multiple key values. In cases where
No key value is given - the Attribute Finder will return "empty set"
A single key value is given - the Attribute Finder runs the query and returns the retrieved data
Multiple key values are given - the Attribute Finder runs the query multiple times, iterating over the set of key values and collects the full set of all the values returned for each query
Stored procedures
In addition to regular SQL queries, attribute mappings can also be created for stored procedures. Stored procedures are database specific subroutines that can be called with a number of input and output parameters.
A stored procedure with at least one output parameter can be used in an attribute mapping where the return values can be mapped to an XACML attribute.
Similarly, XACML attributes can be used as input parameters for a stored procedure.
An example of a stored procedure is a subroutine that maps a user ID to a set of roles. In such a case, an XACML subject-id attribute can be used as an input parameter, while a subject-roles attribute can be mapped to the stored procedure's output parameter.
Creating an attribute mapping that uses a stored procedure is done in the ASM GUI as follows:
In the XACML attributes table, select the attribute that is to hold the values of one of the stored procedure's output parameters.
Enter a call to the stored procedure in the Query field using the following syntax:
{call proc(?,?)}where "proc" is the name of the stored procedure and each question mark is a parameter representing the use of an XACML attribute. The number of parameters differs among stored procedures.
The expression inside the curly braces follows JDBC syntax for calling stored procedures. It is also possible to use static values for parameters instead of using attributes, e.g.:
{call proc(?,'string',42)}After entering the call to the stored procedure, click outside the Query text box.
This will update the Key Associations field with the information needed to complete the stored procedure configuration. The key association field will contain one row of configuration fields for each question mark encountered in the call-string.
Enter the correct input/output information for every parameter question mark ('?') in the stored procedure call-string as described in the [In/out parameters](#Inout parameters) section below.
Submit the mapping by clicking Apply.
In/out parameters
As a stored procedure can have any number of input and output parameters, the attribute mapping needs to be informed which parameters are input, which are output, and which of the output parameters is to be used for the attribute in the mapping.
The Source attribute and Result field depend on the parameter type (input or output). The Result is a checkbox that must be set for exactly one output parameter (type OUT or INOUT) since this is the parameter returning the values that will be set for the target XACML attribute. The parameter types and Source attributes are described in the following table.
| Parameter type | Description | Source attribute |
|---|---|---|
IN | Inputs data to the stored procedure. The actual data will be taken from an XACML attribute set in the Source attribute field. | Set to the XACML attribute whose value is to be used as input data for the stored procedure parameter. |
OUT | An output parameter from the stored procedure, whose return value can be used for the XACML attribute mapping. | Not applicable. |
INOUT | A stored procedure parameter that works both as input and output. | Set to the XACML attribute whose value is to be used as input data for the stored procedure parameter. Note that the values of an XACML attribute used as a source attribute for an INOUT parameter will not be altered. |
The Type field is always the datatype of the stored procedure parameter in the target database. While the "Type" drop-down menu provides a list of common SQL datatypes, an integer value can be entered so that other JDBC-supported datatypes (such as -10 for an Oracle cursor) can be used. Refer to your vendor documentation for a list of valid values.
Compatibility between the database type of the result stored procedure output parameter and the XACML datatype of the target XACML attribute is not checked or ensured. The Attribute Finder will simply convert the string representation of the result to the XACML datatype or the target attribute.