Entering a database search filter
Use a WHERE
clause on the Database Filter window to set up a database search filter in PingFederate.
About this task
On the Database Filter window, enter a WHERE
clause for PingFederate to query the database table you selected to retrieve a record associated with particular values. The clause is in the form:
[WHERE] column1=value1
The left side (column1) is a column from the database table that you selected on the Database Table and Columns window.
To get a list of columns, click the View List of Columns from … link. |
The right side (value1) is the match-against value, generally a variable passed in from either an authentication source for an identity provider (IdP) or an assertion for a service provider (SP). The variables are shown underneath the Where text field. If you are retrieving attributes from multiple data stores using one mapping, attributes available from other sources, if previously configured, are listed near the bottom of the window.
You can also apply additional search criteria by using other columns from the targeted table.
Steps
-
Enter a WHERE clause in the text field.
The initial
WHERE
is optional. -
Ensure the syntax and variable names are correct. For more information about WHERE clauses, consult your database management system (DBMS) documentation.
You can reference attribute values in the form of
$\{attributeName:-defaultValue}
. The default value is optional. When specified, it is used at runtime if the attribute value is not available. Do not use$\{
and}
in the default value. -
Click Next to complete the configuration to query attributes from the database server.
Later in the workflow, you can use the attribute values returned from the database in the applicable contract fulfillment window, the issuance criteria window, or both, to fulfill your use case.
Example
Suppose you have selected a data table named ACCESSTABLE on the Database Table and Columns window. You, the IdP, want to locate user records by matching userid
column against the username from an HTML Form Adapter. As a passed-in variable from the HTML Form Adapter, ${username}
is shown underneath the Where text field.
On the Database Filter window, enter the following filter in the Where text field:
userid='${username}'
userid
-
The column in the table containing the username information in this example.
'${username}'
-
The value of the username variable (
username
) from an HTML Form Adapter
You must use the |