Relational Database Data Connector

The relational database data connector pulls attribute information from any JDBC capable relational database. The following steps walk through how to set up this connector.

1. Define the Connector

To define a new relational database data connector, create a <DataConnector xsi:type="RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc"> with the following attribute:

• id - A unique ID for this data connector used within the resolver configuration

The following advanced options may also be defined as attributes on the <resolver:DataConnector> element.

• readOnlyConnection- a boolean flag that determines whether connections to the database should be read-only; defaults to true
• queryUsesStoredProcedure - a boolean flag that indicates whether the SQL query, defined in step 3, uses stored procedures or not; defaults to false
• cacheResults - a boolean flag that indicates whether search results should be cached for the lifetime of a user's session; defaults to false, deprecated in favor of the <ResultCache> element (see below)
• noResultIsError - a boolean flat that indicates whether a query that returns no results should be treated as an error; defaults to false
• queryTimeout - timeout in milliseconds of the SQL query; defaults to 5000

<resolver:DataConnector xsi:type="RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc"
                        id="UNIQUE_ID">

     <!-- Remaining configuration from the next steps goes here -->

</resolver:DataConnector>

2. Define Dependencies

This step is optional.

One component within the attribute resolver, like data connectors, can depend on information retrieved or constructed by another component. The values from these dependencies may then be used within the query template in the next step. This might be useful if your SQL query depended on the value retrieved from another connector. For example, if you wanted to use an employeeID retrieved from one source to get attributes from another, a dependency on that attribute would allow it to be used as a variable in the SQL query.

Dependencies are expressed by the <resolver:Dependency> with a ref attribute whose value is the unique ID of the attribute definition or the data connector that this connector depends on.

<resolver:DataConnector xsi:type="RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc"
                        id="UNIQUE_ID">

     <resolver:Dependency ref="DEFINITION_ID_1" />
     <resolver:Dependency ref="DEFINITION_ID_2" />
     <resolver:Dependency ref="CONNECTOR_ID_3" />
     <resolver:Dependency ref="CONNECTOR_ID_4" />

     <!-- Remaining configuration from the next steps go here -->

</resolver:DataConnector>

3. Define Failover Data Connectors

This step is optional.

If the data connector encounters an error when trying to retrieve attribute information from the database, one or more failover data connectors can be defined. These connectors are only invoked if this data connector fails (not if simply finds no results) and they are invoked in the order they are listed until one succeeds.

Failover connectors are defined by the <resolver:FailoverDataConnector> element with a ref attribute whose value is the unique ID of the data connector to use in the event that this one fails.

<resolver:DataConnector xsi:type="dc:RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc"
                                          id="UNIQUE_ID">

     <resolver:Dependency ref="DEFINITION_ID_1" />
     <resolver:Dependency ref="DEFINITION_ID_2" />
     <resolver:Dependency ref="CONNECTOR_ID_3" />
     <resolver:Dependency ref="CONNECTOR_ID_4" />

     <resolver:FailoverDataConnector ref="CONNECTOR_ID_1" />

     <!-- Remaining configuration from the next steps goes here -->

</resolver:DataConnector>

4a. Define Identity Provider (Application) Managed Connections

Deployers wishing to allow the data connector to manage connections to the database should follow these instructions; those wishing to allow the Servlet container to manage connections should use the instructions in step 4b.

Identity Provider (application) managed connections are defined by a <ApplicationManagedConnection> element with the following attributes:

• jdbcDriver - the fully qualified class name of the JDBC driver used to make connections to the database
• jdbcURL - the connection URL for the database
• jdbcUserName - the user name used to connect to the database
• jdbcPassword - the password used to connect to the database

<resolver:DataConnector xsi:type="RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc"
                        id="UNIQUE_ID">

     <!-- Dependency and Failover information would go here -->

     <ApplicationManagedConnection jdbcDriver="DRIVER_CLASS"
                                   jdbcURL="DATABASE_URL"
                                   jdbcUserName="DATABASE_USER"
                                   jdbcPassword="DATABASE_USER_PASSWORD" />

     <!-- Remaining configuration from the next steps goes here -->

</resolver:DataConnector>

Advanced Options

The <ApplicationManagedConnection> element also allows the following, advanced, configuration attributes controlling how connections are pooled:

• poolAcquireIncrement - number of new connections to create when a pool is exhausted; defaults to 3
• poolAcquireRetryAttempts - number of times the pool will attempt retry connections, upon a failure; defaults to 36
• poolAcquireRetryDelay - number of milliseconds the pool will wait before trying to establish a connection to the database if an error occurs; defaults to 5000
• poolBreakAfterAcquireFailure - boolean flag indicating whether the database should be marked as permanently unavailable if a good connection can not be created after the poolAcquireRetryAttempts; defaults to true
• poolMinSize - minimum number of open connections that the pool will keep; defaults to 2
• poolMaxSize - maximum number of open connections the pool will keep; defaults to 50, a value of 0 indicated no maximum
• poolMaxIdleTime - number of seconds a connection may remain idle before being shutdown; defaults to 600
• poolIdleTestPeriod - number of seconds the pool will wait before running the next scan for idle connections; defaults to 180

4b. Define Container Managed Connections Configuration

Deployers wishing to have their Servlet container manage connections to a database should follow these instructions, those wishing to allow the data connector to manage connections should use the instructions in step 4a.

Servlet container managed connections are defined by a <ContainerManagedConnection> element with the following attribute:

• resourceName - JNDI location of the Java Connection DataSource

The <ContainerManagedConnection> may also contain any number of <JNDIConnectionProperty> elements that specify the JNDI connection properties appropriate for the container. Refer to your container documentation for these properties. Some common JNDI connection parameters are listed by Sun.

<resolver:DataConnector xsi:type="RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc"
                        id="UNIQUE_ID">

     <!-- Dependency and Failover information would go here -->

     <ContainerManagedConnection resourceName="RESOURCE_NAME"  />

     <!-- Remaining configuration from the next steps go here -->

</resolver:DataConnector>

5. Define the SQL Query

The SQL query used to retrieve attribute information from the database is specified within a single <QueryTemplate> element. The content of the element is a template resulting in a well formed SQL statement and should always be encapsulated in CDATA to ensure proper handling. Variables may be used, including those passed in through dependencies and a special one, $requestContext.principalName, which is always the principal name as supplied by the user authentication.

<resolver:DataConnector xsi:type="RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc"
                        id="UNIQUE_ID">

     <!-- Dependency and Failover information would go here -->

     <ContainerManagedConnection resourceName="RESOURCE_NAME" />

     <QueryTemplate>
          <![CDATA[
               SELECT * FROM PEOPLE WHERE userid='$requestContext.principalName'
          ]]>
     </QueryTemplate>

     <!-- Remaining configuration from the next steps go here -->

</resolver:DataConnector>

6. Define Column Mappings

This step is optional.

By default, the data connector will produce one attribute per column retrieved, using the column name as the attribute's ID, each row as an attribute value, and the column's type as the object type for each value. For example, consider a SQL query SELECT firstname, personid FROM PERSON WHERE userid='${requestContext.principalName}' with a firstname column of type VARCHAR and a personid column of type NUMBER(9). The data connector would create two attributes with ID's of firstname and personid. Each of the values of firstname would be a String and each of the values of personid would be an Integer.

Column mappings allow you to change this behavior by mapping a column name to a specified name and/or specifying an object type for the values retrieved from that column. The mappings are defined by one or more <Column> element with the following attributes:

• columnName - name of the database column being mapped; this attribute is required
• attributeID - ID to use for the attribute created from the column
• type - object type to use for the values of the attribute; acceptable values are: BigDecimal, Boolean, Byte, ByteArray, Date, Double, Float, Integer, Long, Object, Short, String, Time, Timestamp, URL

<resolver:DataConnector xsi:type="RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc"
                        id="UNIQUE_ID">

     <!-- Dependency and Failover information would go here -->

     <ContainerManagedConnection resourceName="RESOURCE_NAME"  />

     <QueryTemplate>
          <![CDATA[
               SELECT * FROM PEOPLE WHERE userid='$principal'
          ]]>
     </QueryTemplate>

     <Column columnName="firstname" attributeID="fname" />
     <Column columnName="personid" type="String" />

</resolver:DataConnector>

7. Result Caching

The data connector may cache query results for a period of time or until too many results are in memory.

To enable and configuration result caching add the <ResultCache> element as the last element in the data connector. The element has the following attributes:

• elementTimeToLive - amount of time, in duration notation, that a result will be cached (Default value: 4 hours)
• maximumCachedElements - maximum number of results that will be cached at any one time, if this is exceeded the oldest entries will be discard first. (Default value: 500)

8. Putting It Together

A final, complete RDBMS <DataConnector> could look like this.

<resolver:DataConnector xsi:type="RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc"
                        id="UNIQUE_ID">

     <resolver:Dependency ref="DEFINITION_ID_1" />
     <resolver:Dependency ref="CONNECTOR_ID_3" />

     <resolver:FailoverDataConnector ref="CONNECTOR_ID_1" />

     <ContainerManagedConnection resourceName="RESOURCE_NAME"  />

     <QueryTemplate>
          <![CDATA[
               SELECT * FROM PEOPLE WHERE cn='$DEFINITION_ID_1'
          ]]>
     </QueryTemplate>

     <Column columnName="firstname" attributeID="fname" />
     <Column columnName="personid" type="String" />

     <ResultCache elementTimeToLive="PT1H" />
</resolver:DataConnector>