When you retrieve attribute values from a database server, you follow these configuration steps.

On the Database Table and Columns screen, specify exactly where to find additional data to fulfill your use case. You can use only one table as a source of data for a database query. For more information about each field, refer to the following table:

Field Description
Schema Lists the table structure that stores information within a database. Some databases, such as Oracle, require selection of a specific schema for database queries. Other databases, such as MySQL, do not require selection of a schema.
Table Displays the tables contained in the database. Select the table to retrieve data from the datastore.
Columns to return from SELECT Displays selected columns from the selected tables. Select the columns that are associated with the desired attributes you would like to return from the database queries.
Important:

This applies to MySQL users. To accommodate table and column names that contain spaces, PingFederate inserts double quotes around the names at runtime. To avoid SQL syntax errors resulting from the quotes, add the session variable sql_mode=ANSI_QUOTES to the JDBC connection string of your datastore instance.

jdbc:mysql://myhost.mydomain.com:3306/pf?sessionVariables=sql_mode=ANSI_QUOTES

Alternatively, you can configure the system variable sql_mode with the ANSI_QUOTES option. For more information, see https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html.

  1. Choose a schema (when applicable) from the Schema list.
  2. Select a table from the Table list.
  3. Optional: Click View Attribute Contract to determine what attributes to look up.
  4. Optional: Click Refresh if you are updating an existing configuration and you could have changed the database.
  5. Under Columns to return from SELECT, choose a column name and click Add Attribute.
    Note:

    You do not need to add a column here to use it as part of a search filter. Add only the columns required by subsequent sibling configuration items, such as contract fulfillment or token authorization. Any added columns left unused are removed when the configuration is saved.

    Repeat this step to add more columns as needed.

Example

Suppose you, the identity provider (IdP), have a data table named ACCESSTABLE with thee columns: userid, department, and accesslevel. Your use case requires you to map accesslevel into a SAML contract to a service provider (SP).

On the Database Table and Columns screen, select the ACCESSTABLE table and add the accesslevel column.