Using stored procedures

In addition to regular SQL queries, attribute mappings can also be created for stored procedures. These 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, one or several XACML attributes can be used as input parameters, while a subject-roles attribute can be mapped to the stored procedure's output parameter.

<cfg:mapping>
    <cfg:xacmlAttribute Category="urn:oasis:names:tc:xacml:1.0:subject-category:access-subject" AttributeId="role" DataType="http://www.w3.org/2001/XMLSchema#string" Issuer=""/>
    <cfg:nativeAttribute outputIndex="4">
        <cfg:sqlType>2004</cfg:sqlType>
        <cfg:query>{call proc(?,?,?,?)}</cfg:query>
        <cfg:key allowMultiple="false" sqlType="12" inputType="IN">
            <cfg:xacmlAttribute Category="urn:oasis:names:tc:xacml:1.0:subject-category:access-subject" AttributeId="urn:oasis:names:tc:xacml:1.0:subject:subject-id" DataType="http://www.w3.org/2001/XMLSchema#string" Issuer=""/>
        </cfg:key>
        <cfg:key allowMultiple="false" sqlType="92" inputType="IN">
            <cfg:xacmlAttribute Category="urn:oasis:names:tc:xacml:3.0:attribute-category:environment" AttributeId="urn:oasis:names:tc:xacml:1.0:environment:current-time" DataType="http://www.w3.org/2001/XMLSchema#time" Issuer=""/>
        </cfg:key>
        <cfg:key allowMultiple="false" sqlType="12" inputType="INOUT">
            <cfg:xacmlAttribute Category="urn:oasis:names:tc:xacml:1.0:subject-category:access-subject" AttributeId="urn:oasis:names:tc:xacml:1.0:subject:subject-id-qualifier" DataType="http://www.w3.org/2001/XMLSchema#string" Issuer=""/>
        </cfg:key>
        <cfg:key allowMultiple="false" sqlType="4" inputType="OUT"/>
    </cfg:nativeAttribute>
    <cfg:uId>fe2ca7f9-bb46-48e4-a2d7-2073abef3869</cfg:uId>
</cfg:mapping>

Example of an attribute mapping that uses a stored procedure

Additional XML elements or attributes used for a stored procedure.

XML element or attribute	Description	Supported format
outputIndex	The outputIndex indicates from where to get the value for the target attribute. If it is 0, the value will be the one returned by the procedure call. If it is 1 and higher, the value will be taken from an output argument according to the index position.	An integer that represents the position of a key element with inputType OUT or INOUT.
inputType	A stored procedure can have any number of input and output parameters. The attribute mapping needs to be informed which parameters are input and which are output.	See here.

Creating an attribute mapping that uses a stored procedure is done as follows:

1. Enter a call to the stored procedure in the "query" element using the following syntax:

   {call proc(?,?,?,?)}

   where proc is the name of the stored procedure and each question mark is a parameter representing either the use of an XACML attribute or an output parameter (OUT or INOUT). 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, for example:

   {call proc(?,'string',42)}

2. Add one key element per question mark encountered in the call string, each with a corresponding inputType and sqlType. For details, see here.

   The association of the placeholders is positional, in that the first placeholder corresponds to the first key element.

3. For parameters with inputType IN or INOUT, you need to specify an XACML attribute, from where it will get its value.

   For parameters with inputType OUT, an attribute must not be specified.

4. Set the outputIndex to the positional value of the output parameter to use.

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 parameter types and source attributes are described in the following table.

Parameter type	Description	Source attribute
IN	Inputs data to the stored procedure. The data will be taken from an XACML attribute with inputType set to IN.	Set to the XACML attribute the value of which is to be used as input data for the stored procedure parameter.
OUT	An output parameter from the stored procedure, the value of which can be used for the XACML attribute mapping, depending on the outputIndex setting.	Not applicable
INOUT	A stored procedure parameter that works both as input and output. The data will be taken from an XACML attribute with inputType set to INOUT.	Set to the XACML attribute the value of which 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.

note

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.