Specifying database tables and columns - PingFederate - 11.1

PingFederate Server

bundle
pingfederate-111
ft:publication_title
PingFederate Server
Product_Version_ce
PingFederate 11.1
category
Administrator
Audience
Capability
DeploymentMethod
Product
SingleSignonSSO
Software
SystemAdministrator
pf-111
pingfederate
ContentType_ce

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.

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.
Important:

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.

  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.
    Note:

    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

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.