Database Connectors

The relational database data connector describes an external RDBMS from which attributes will be sourced. JDBC is used to connect to the database and pull information from it using the SQL query language. This connection can either be made directly by the provider or through the container using JNDI. Individual values that result from the SQL query are available to attribute definitions which prepare the attributes for use within the provider and subsequent release.

The Velocity template engine is used to define variables for use in SQL queries. The identifier of the user is available by using the variable ${principal}. The names of all attributes may be called by ${attributeID} and respective values by ${attributeID.getValues()}. Conditionals, user-defined variables, and more are supported as described in the Velocity user guide.

Basic Configuration

A basic database connection managed by Shibboleth itself is defined using a DataConnector like the ones below. The first example is a connection Shibboleth itself establishes using JDBC, and the second leverages a connection managed by the container through JNDI.

Application(Shibboleth provider) Managed Connections

There are several attributes that will always need to be changed:

A <QueryTemplate> also needs to be defined which should result in the right attributes being returned for each principal. Standard SQL and Velocity may be used, but it's recommended that everything always be encapsulated in CDATA to ensure proper handling.

<DataConnector id="uniqueID" validationQuery="SQLQUERY"
xsi:type="rdbms:RelationalDatabase" xmlns:rdbms="urn:mace:shibboleth:2.0:resolver:dc:rdbms">
     <ApplicationManagedConnection jdbcURL="jdbc:hsqldb:hsql://127.0.0.1/MyDatabase"
               jdbcDriver="org.hsqldb.jdbcDriver" jdbcUserName="attributeResolver"
               jdbcPassword="topsecret">
          <QueryTemplate>[CDATA[select * from myTable where uniqueId = ${principal}]]</QueryTemplate>
     </ApplicationManagedConnection>
</DataConnector>

Container Managed Connections

Connections maintained by the servlet container can be leveraged by Shibboleth as well. The container itself will generally know all the parameters for the proper connection, so in most cases the only things that must be specified are the resourceName and the validationQuery.

<DataConnector id="uniqueID" xsi:type="rdbms:RelationalDatabase"
    validationQuery="SQLQUERY"
    xmlns:rdbms="urn:mace:shibboleth:2.0:resolver:dc:rdbms">

    <ContainerManagedConnection resourceName="java:comp/env/jdbc/MyDatabase"=>
        <QueryTemplate>[CDATA[select * from myTable where uniqueId = ${principal}]]</QueryTemplate>
    </ContainerManagedConnection>

</DataConnector>

Advanced Configuration

A <resolver:DataConnector> element with xsi:type="rdbms:RelationalDatabase" defines a database data connector. There can be unlimited <DataConnector xsi:type="rdbms:RelationalDatabase> elements in resolver.xml, each defining one interface to an external database. Each column resulting from the query is assigned a matching attributeID within the IdP. These attributes may then be transformed by an encoder into an attribute name based on protocol when this attribute is sent to another provider. The uniqueID must be unique across the resolver.

<resolver:DataConnector xsi:type="rdbms:RelationalDatabase" id="uniqueID"
   validationQuery="SQLQUERY" xmlns:rdbms="urn:mace:shibboleth:2.0:resolver:dc:rdbms"
   readOnlyConnection="true" queryUsesStoredProcedure="queryUsesStoredProcedure"
   cacheResults="true" validationQuery="SELECT query">

    <ApplicationManagedConnection jdbcURL="jdbc:hsqldb:hsql://127.0.0.1/MyDatabase"
        jdbcDriver="org.hsqldb.jdbcDriver" jdbcUserName="attributeResolver"
        jdbcPassword="topsecret" poolAcquireIncrement="3" poolAcquireRetryAttempts="36"
        poolAcquireRetryDelay="5000" poolBreakAfterAcquireFailure="true"
        poolMinSize="2" poolMaxSize="50" poolMaxIdleTime="600" poolIdleTestPeriod="180">

        <QueryTemplate>[CDATA[select * from myTable where name = ${principal}]]</QueryTemplate>
        <Column columnName="georgetownAffiliation" attributeID="affiliation" type="string"/>

    </ApplicationManagedConnection>

    OR

    <ContainerManagedConnection resourceName="java:comp/env/jndi/MyDatabase">

        <JNDIConnectionProperty name="java.classname" value="variable"/>

        <QueryTemplate>[CDATA[select * from myTable where name = ${principal}]]</QueryTemplate>
        <Column columnName="georgetownAffiliation" attributeID="affiliation" type="string"/>

    </ContainerManagedConnection>

</resolver:DataConnector>

The <Column/> element, which may appear zero to unlimited times, allows the connector itself to change attribute names from the columnName of any standard SQL type to the attributeID before it's used by the rest of the resolver. This may also be done using multiple <AttributeDefinition> elements.

Additional application-managed configuration parameters:

The additional JNDI connection parameters are all defined as <JNDIConnectionProperty/> elements. Some common parameters are listed by Sun.

Database Reference Table

The validationQuery, jdbcDriver, and jdbcURL must be changed match the database and driver you'll be using. Some helpful resources are below, but they are not guaranteed to work in every environment and probably will need to be changed. If you're using a ResolverManagedConnection, the correct JDBC driver for your database must be placed in the /lib/ directory of your provider before building it with Ant. If you're using a ContainerManagedConnection it must be in the classpath of your servlet container.

Database Name

JDBC Driver

Driver URI Pattern

validationQuery

MySQL

com.mysql.jdbc.Driver

jdbc:mysql://localhost/myDB

select 1

Derby

org.apache.derby.jdbc.EmbeddedDriver

jdbc:derby://localhost/myDB

values(1)

PostgreSQL

org.postgresql.Driver

jdbc:postgresql://127.0.0.1/myDB

select 1

Oracle

oracle.jdbc.driver.OracleDriver

jdbc:oracle:thin:@127.0.0.1:myDB

select 1 from dual

DB2

com.ibm.db2.jcc.DB2Driver

jdbc:db2://localhost/myDB

select 1 from dual

HSQLDB

org.hsqldb.jdbcDriver

jdbc:hsqldb:hsql://127.0.0.1/myDB

varies