If the query returns no records, it results in the user not being found.
If the query returns more than one record, it results in the username being ambiguous.

If this property is not defined, the username itself is used as primary key in the user table (the usual and efficient way).

Note: If this property is defined user insertion by this plugin is no more possible.

The name of the database column with the number of failed password reset attempts for flow-based password reset. The type of this column is NUMBER

Security note: If this column is not specified, failed password reset attempts are not counted, which enables brute-force attacks.

Example: In the following example, the user table is USER, there is role table ROLE and a table with the user-to-role mappings USER2ROLE:

roles-query="SELECT r.role_name from ROLE r, USER u, USER2ROLE u2r where u.userName = ${userId} AND u.id = u2r.user AND u2r.role = r.id"

Specifying such a query is only necessary if the username cannot be used as primary key in the user table (this only if property "User Name Resolve Query" is specified).

The query must be such that it returns one-column records one username (userid) per row.

Note that his query is used both when returning all user ids and when returning only matching user ids (filtered by the user). Thus, the query must be such that LIKE-clauses against context data columns work. This usually means that you must join the result with the user table (even if the user id is not read from the usertable) so the LIKE-clauses can access the context data of the user table. Failing to do so will result in runtime SQL syntax exceptions!

Note: If this property is specified, "Additional Iterator Clauses" and the deleted flag is ignored. They must be part of the query itself!

The value of this configuration property can be empty (no effect) or any valid SQL query. You can use values of the user record (Record selected from table specified by configuration property user-table-name by user name and optionally additional where clause) in the query as follows: ${xxx} refers to the field (column) xxx from the selected user record.

Example: In our example the selected user record has the following values (column name = value): user_id = 'freddie', person_no = 13, ...
Further, there is a different database table PERSON which is referenced by the user table. The table PERSON has a column of type boolean called "valid" which indicates whether a person record is valid or not. Consider the following value for this configuration property: SELECT p.valid FROM PERSON p WHERE p.person_no = ${person_no}
Thus, when looking for the user record (given the username and the matching the optional additonal where part), the above query is executed where ${person_no} is substituted by the value 13 of field person_no of the selected user record.

A list of database columns that are loaded/stored in the user's context data container.

Use either an appropriately typed instance (preferred) or the legacy type using auto-detection (the default up to IAM 6.4).

Note: These context data values are read only! When fetching user records, the query will be executed for each user and the values will be added to the context data container. Modified, new or deleted values will not be written when user records are updated.

Also note that context data fields defined in configuration property context-data-columns override corresponding entries in this property.

Example:
SELECT p.mobile_no FROM person p WHERE p.person_no = ${person_no}

Note that this plugin still uses its own data-based optimistic locking mechanism. It just increments the value within a transaction in order to be compliant with other components' locking mechanisms.

The column must be of an integer type. Usually a long type is used.

The type of the column must be compatible with a timestamp.

Note that - if configured (see separate property) - user information may also be written to the database at the same time.

Note that - if configured (see separate property) - the insertion date may also be written to the database at the same time.

The type of the column must be compatible with a timestamp.

Note that - if configured (see separate property) - user information may also be written to the database at the same time.

Note that - if configured (see separate property) - the modification date may also be written to the database at the same time.

This allows you to add arbitrary fixed or dynamic values when a new record is created. This is useful if some database fields may not be NULL but are not inserted by this plugin by default.

Caution: Make sure to appropriately escape values (e.g. use single quotes around strings). They are used as provided in the SQL insert statements. This allows calling database dependent functions (e.g. in order to get a sequence number, system date, etc).

Caution: If the columns specified here are the same as configured in the context data fields or in any "Col ..." property, remove them from the other places for this persister instance.

This property only has an effect if used in connection with a "Database User Store".

A string formatter pattern describing how to constrain the result set to a subrange of all results. Set this value in case Airlock IAM cannot determine the optimal query pattern automatically.

The first argument is the number of rows to skip (offset), and the second argument is the number of rows to return (limit).

If no pattern is set, Airlock IAM will attempt to automatically determine the query based on the database type.

Commonly used patterns:

• LIMIT %2$d OFFSET %1$d for MySQL, MariaDB, H2, HSQLDB, PostgreSQL, SQLite
• OFFSET %1$d ROWS FETCH NEXT %2$d ROWS ONLY for SQL:2008 standard, Derby, SQL Server 2012, Oracle 12c

This property only has an effect if used in connection with a "Database User Store".

If the database is already case sensitive (most DBs, except MySQL and MariaDB) the default value can be used.

The argument (%s) is the name of the field to compare, and the question mark is the value to be searched for.

Commonly used patterns:

• %s = ? – in most cases
• BINARY `%s` = ? – for MySQL and MariaDB databases with standard (case insensitive) settings

This property only has an effect if used in connection with a "Database User Store".

If the database is already case sensitive (most DBs, except MySQL and MariaDB) the default value can be used.

The argument (%s) is the name of the field to compare, and the question mark is the value to be searched for. Important: This is used for approximate matching, e.g. "contains" matching, where the search value could be like "%bla%", thus the "LIKE" operator must be used instead of the equality sign.

Commonly used patterns:

• %s LIKE ? – in most cases
• %s LIKE BINARY ? – for MySQL and MariaDB databases with standard (case insensitive) settings
• %s COLLATE latin1_general_cs LIKE ? – alternative for MySQL and MariaDB, possibly more efficient, but collation must be known

This property only has an effect if used in connection with a "Database User Store".

If the database is already case insensitive (e.g. MySQL and MariaDB) the default value can be used.

The argument (%s) is the name of the field to compare, and the question mark is the value to be searched for. For some databases a less efficient "LIKE" operator has to be used for this.

Depending on used DB and version, as well as the specific setup, different values can be the most efficient. For large user repositories, a DB expert might be consulted or tests with different settings should be performed.

Commonly used patterns:

• LOWER( %s ) = LOWER ( ? ) – works for most DBs, tested with Oracle DB. Note that this is only really efficient, if a lower-case index is created for the relevant columns.
• %s COLLATE latin1_general_ci = ? – recommended for MSSQL.
• %s = ? – for databases with default case insensitive matching, e.g. MySQL and MariaDB with standard settings

This property only has an effect if used in connection with a "Database User Store".

If the database is already case insensitive (e.g. MySQL and MariaDB) the default value can be used.

The argument (%s) is the name of the field to compare, and the question mark is the value to be searched for. Important: This is used for approximate matching, e.g. "contains" matching, where the search value could be like "%bla%", thus the "LIKE" operator must be used instead of the equality sign.

Depending on used DB and version, as well as the specific setup, different values can be the most efficient. For large user repositories, a DB expert might be consulted or tests with different settings should be performed.

Commonly used patterns:

• LOWER( %s ) LIKE LOWER ( ? ) – works for most DBs, tested with Oracle DB. Note that this is only really efficient, if a lower-case index is created for the relevant columns.
• %s COLLATE latin1_general_ci LIKE ? – recommended for MSSQL.
• %s LIKE ? – for databases with default case insensitive matching, e.g. MySQL and MariaDB with standard settings
• %s ILIKE ? – for PostgreSQL databases