PingFederate Server

Specifying database tables and columns

In the Database Table and Columns window, you can specify exactly where additional data can be found to fulfill your use case. You can only use one table as a source of data for a database query.

About this task

For more information about each field, see the following table.

Field Description

Schema

Lists the table structure that stores information within a database. Some databases require selection of a specific schema for database queries. Other databases 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 want to return from the database queries.

For MySQL users — To allow for table and column names that might 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 Java Database Connectivity (JDBC) connection string of your datastore instance, as in the following example.

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.

Steps

  1. From the Schema list, choose a schema when applicable.

  2. From the Table list, select a table.

  3. Optional: To determine what attributes to examine, click View Attribute Contract.

  4. Optional: To update an existing configuration where changes to the database might have occurred, click Refresh.

  5. Under Columns to return from SELECT, choose a column name and click Add Attribute.

    You do not have to add a column here to use it as part of a search filter. Add only the column that is 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

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 an SP.

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