IAM plugin documentation

Database Token List Persister

Description

Highly configurable persister using a relational database as repository for token lists. The database is accessed via JDBC. It fetches the data of a user by directly executing a prepared statement. Making changes persistent is achieved by multiple update statement executions on the token list record within a transaction.
This plug-in allows you to specify extra where clauses and search filters to select the set of users.

How this plug-in finds a user record

There are two ways how this plugin finds a user record for getting user data, updating user data and deleting user data. The user record is always fetched using a select statement given a primary key and applying the configured filters (additional where clause). The two variants differ in how the primary key is determined:

The primary key for selecting the user record is the username itself. This is by far the most common and most efficient method to obtain user data.
The primary key is determined by a separate query including the username. The query can be configured. This way of selecting the user record is more flexible but results in an additional select statement.

Estimate for the Length of the Token List Database Field

The length of the encoded token hash list depends mainly on the number of unused tokens in the list, the used hash function and the encoding of the list.

Here is an example using the SHA1PasswordHash as hashfunction (which produces 40 bytes for each token) together with the hashed token list encoding used by this persister implementation (actually it is the encoding provided by TokenListHasher#hashedTokenListToBytes(HashedTokenList) ):
Each unused token uses 40 bytes for the hash value, 4 bytes for the index and 4 bytes for the length of the hash value, thus 48 bytes. Due to the nature of the hash function, this figures are independent of the length of the tokens.
Additionally the encoded list holds the number of the tokens (when the list was new) in 4 bytes, the length of the identification string in 4 bytes, the identification string of arbitrary length and the generation timestamp in 8 bytes. This makes another 16 bytes excluding the identification string.
If the list has 100 tokens and the identification string is 20 bytes at most, this makes 100 * 48 + 16 + 20 = 4836 bytes.

Type name

DatabaseTokenListPersister

Class

com.airlock.iam.core.misc.impl.persistency.db.DatabaseTokenListPersister

May be used by

TAN Batch Task TAN Batch Task Cipher Token List Persister Matrix Token Controller Default TAN Service Fixed TAN Generator Task

Properties

SQL Data Source (sqlDataSource)

Description

Defines how connections to the database are obtained.

Attributes

Plugin-Link

Mandatory

Assignable plugins

JDBC Connection Pool

Token List Table Name (tokenListTableName)

Description

The name of the database table containing the token lists (and often also user data).

Attributes

String

Mandatory

Suggested values

medusa_user

Col User Name (colUserName)

Description

The name of the database column with the username. This column is used to search the token list given the user's name.

Attributes

String

Mandatory

Suggested values

username

User Name Resolve Query (userNameResolveQuery)

Description

An SQL query that returns a primary key for the user table given the user name.
Such a query is useful if the username (used on the login page) is not part of the user table.
The query must be such that - given the username - it returns one value that can be used as primary key in the user table. The query must contain one question mark (?) which will be substituted by the username (a string).

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.

Attributes

String

Optional

Example

SELECT u.id FROM user u, person p WHERE p.id = u.person_id and p.contractId = ?

Col Token List (colTokenList)

Description

The name of the database column holding binary token list data. The type of this database column must be able to hold binary data (see plugin description to estimate the size of this field).

Attributes

String

Mandatory

Suggested values

matrix_current_list

Col New Token List (colNewTokenList)

Description

The name of the database column holding binary token list data of the new (or next) token list. The type of this database column must be able to hold binary data (see plugin description to estimate the size of this field).

Attributes

String

Mandatory

Suggested values

matrix_next_list

Col Generation Time Stamp (colGenerationTimeStamp)

Description

The name of the database column with the timestamp of the latest token list generation. This column type is either DATETIME or TIMESTAMP

Attributes

String

Optional

Suggested values

matrix_gen_date

Col Delivery Time Stamp (colDeliveryTimeStamp)

Description

The name of the database column with the timestamp of the latest token list delivery. This column type is either DATETIME or TIMESTAMP

Attributes

String

Optional

Suggested values

matrix_del_date

Col Other Credentials Delivery Timestamps (colOtherCredentialsDeliveryTimestamps)

Description

Comma-separated list of column names with the delivery dates of other credentials. This information may be used in order to delay the delivery time for token lists so no two credentials of the same user are delivered the same day.

Attributes

String

Optional

Example

password_delivery

Example

tokenDeliveryTimestamp

Col List Active (colListActive)

Description

The name of the database column with the flag indicating whether the token list is active or not. Inactive token lists may not be used by the callers. This column type is either CHAR or NUMBER. The value "0" (zero) is treated as false, any other value is treated as true.
If the column is not specified, all token lists are considered to be active.

Attributes

String

Optional

Suggested values

active, matrix_active

Col Challenge Open Since (colChallengeOpenSince)

Description

Name of the database column with the timestamp of the start of an ongoing challenge. The type of this column is either DATETIME or TIMESTAMP.

Attributes

String

Optional

Suggested values

matrix_chal_open_since, MATRIX_CHAL_OPEN_SINCE

Col Unanswered Challenges (colUnansweredChallenges)

Description

Name of the database column with the number of unanswered challenges. The type of this column is NUMBER.

Attributes

String

Optional

Suggested values

matrix_open_chals, MATRIX_OPEN_CHALS

Col New List Ordered (colNewListOrdered)

Description

The name of the database column with the flag indicating whether a new token list should be generated for a user. This column type is either CHAR or NUMBER. The value "0" is treated as false, any other value is treated as true.

Attributes

String

Mandatory

Suggested values

matrix_order_new

Col New List Ordered User (colNewListOrderedUser)

Description

The name of the database column with the user by whom a new token list was ordered. This column type is VARCHAR.

Attributes

String

Optional

Suggested values

matrix_order_user

Col New List Ordered Date (colNewListOrderedDate)

Description

The name of the database column with date of when a new token list was ordered. This column type is either DATETIME or TIMESTAMP.

Attributes

String

Optional

Suggested values

matrix_order_date

Additional Where Clause (additionalWhereClause)

Description

Optional SQL query part that is added to the where clause when searching the token lists by user name.
The SQL query without an additional where clause is "SELECT * FROM token-list-table WHERE colusername = 'username'"(real values for "token-list-table", "colusername" are taken from the configuration and "username" is taken from the token list object).
The SQL query with an additional where clause "xyz" is: "SELECT * FROM token-list-table WHERE colusername = 'username' AND xyz".

Example: If the value of this configuration setting is "GROUP = 'a' AND COD = 1" the resulting query is "SELECT * FROM token-list-table WHERE colusername = 'username' AND GROUP = 'a' AND COD = 1"

Attributes

String

Optional

Example

deleted = 0

Example

group = 'remoteUsers' AND verified = 1

Additional Iterator Where Clause (additionalIteratorWhereClause)

Description

Same as property "additional-where-clause" except that it is used as where part when iterating over the token lists.

Attributes

String

Optional

Example

deleted = 0

Example

group = 'remoteUsers' AND verified = 1

Iterator Query (iteratorQuery)

Description

This query is used to get all user ids (or all matching user ids) instead of the default generated query defined by the user table, the username column and the context data fields.

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!

Attributes

String

Optional

Example

SELECT p.id from PERSON p, User u where u.person_id = p.id

Context Data Items (contextDataItems)

Description

A list of context data items that are fetched and returned to the caller together with the token list.

Attributes

Plugin-List

Optional

Assignable plugins

Boolean Context Data Item Date And Time Context Data Item Date Context Data Item Integer Context Data Item Legacy Context Data Item String Context Data Item

Col Version Id (colVersionId)

Description

Name of a database column containing a numerical version id that is automatically incremented by one when a record is changed.
Such a technical column is used by some applications or libraries (such as Hibernate) to implement optimistic locking.

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.

Attributes

String

Optional

Suggested values

rowVersionId

Col Record Modification Date (colRecordModificationDate)

Description

Name of a database column with the date and time this record was modified. The timestamp is written by this plugin at the time the record is modified by this plugin.

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.

Attributes

String

Optional

Suggested values

rowUpdateDate

Col Record Modification User (colRecordModificationUser)

Description

Name of a database column with the name of the system that modified the record. The name is determined by configuration property "record-modification-user" and is written by this plugin at the time the record is modified by this plugin.

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

Attributes

String

Optional

Suggested values

rowUpdateUser

Record Modification User (recordModificationUser)

Description

Specifies a string (typically the name associated with the system using this plugin) that is written to the database fields specified by properties "col-record-insertion-user" and "col-record-modification-user" when this plugin creates or modifies a user record.

Attributes

String

Optional

Default value

Medusa

Suggested values

Airlock

YAML Template (with default values)


type: DatabaseTokenListPersister
id: DatabaseTokenListPersister-xxxxxx
displayName: 
comment: 
properties:
  additionalIteratorWhereClause:
  additionalWhereClause:
  colChallengeOpenSince:
  colDeliveryTimeStamp:
  colGenerationTimeStamp:
  colListActive:
  colNewListOrdered:
  colNewListOrderedDate:
  colNewListOrderedUser:
  colNewTokenList:
  colOtherCredentialsDeliveryTimestamps:
  colRecordModificationDate:
  colRecordModificationUser:
  colTokenList:
  colUnansweredChallenges:
  colUserName:
  colVersionId:
  contextDataItems:
  iteratorQuery:
  recordModificationUser: Medusa
  sqlDataSource:
  tokenListTableName:
  userNameResolveQuery: