Use the Database Table and Columns screen to specify a table as a source of data to fulfill your use case.
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. |
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.
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.