JDBC

The JDBC data source supports a number of backing SQL databases out of the box. Depending on driver and connection string the JDBC data source will change the dialect to match the target system.

  • MariaDB
  • MySQL
  • PostgreSQL
  • Microsoft SQL Server
  • Oracle
  • HsqlDb
  • CockroachDB

Before configuring a data source see the details for each vendor below.

Table management

Curity is shipped with initialization scripts for each supported database type. These contain the table definitions and indexing needed for a standard Curity setup. Depending on target system and target usage additional indexing might be required. Please contact Curity support if you are uncertain if additional setup is required.

The table definitions are found in the installation under <IDSVR>/etc. Each target system is represented by it’s own setup script:

  • mysql-create_database.sql
  • mssql-create_database.sql
  • postgres-create_database.sql
  • oracle-create_database.sql
  • hsqldb-create_database.sql
  • cockroach-create_database.sql

Make sure these are executed on the target database before connecting Curity.

Database maintenance

Curity relies on external maintenance. Therefore it is important to setup janitor procedures before going into production. The following tables need to be maintained and cleaned up.

Table Data Lifetime
delegations Long lived. Each entry is set to expire when Refresh Token expires. See configuration
tokens Mixed. Refresh tokens may live long but access tokens shorter
nonces Short. Usually valid in the range of minutes
sessions Short. Usually valid in the range of hours.

The interval for cleanup and how old data needs to be before cleaning depends on the data management policy of the organization. Some organizations are required to store data longer and others are prevented from doing so. Please consult with your local database administrators or data officers when setting a maintenance strategy.

See each vendor type below for examples on how to clean up the DB.

Important

Cleaning up tokens and delegations can have a legal impact since possible auditable data is lost. Therefore consult your organisations policy on data storage when deciding how to clean data.

Quoted identifiers

As the list of databases supported by Curity has grown, so has the need to enforce common behaviour in some of the areas the SQL standard has left to the various implementations to decide on. One such item is case sensitivity, where the SQL standard mandates (unquoted) identifiers to be treated in a case insensitive manner. This however does not translate well when used in case sensitive contexts like tokens (or any attributes stored in JSON) where case sensitivity is mandated. For this reason as well as to avoid special treatment of certain databases (some identifiers used by Curity have special meaning in some databases but not in others) Curity consistently uses quoting around identifiers, both in the table definitions provided as well as the queries sent from Curity to the databases it communicates with.

Configuration

The SQL datasource is based on a JDBC datasource. This means that at a minimum it requires a configured connection-string, driver, username and password.

Tip

The full configuration reference is found in the configuration section

Connection Pool

By default, the server uses a connection pool to manage the connections to a SQL backend. The default pool settings provide a starting point for many deployments, but there are many options to tweak the behaviour of the connection pool, for example the minimum and maximum size of the connection pool, connection time-out settings, etc. See the data-source reference for a full overview of the configuration options.

Multi-Tenancy

To configure multi-tenancy at authentication profile level, the Multi Tenant Mode must first be enabled on the data source. Read more about multi-tenancy

Custom Queries

Each SQL datasource type also has the option to configure a number of custom queries, that you can use to integrate a custom database schema with the Curity Identity Server. These queries are listed in the table below.

Query Parameters Description
custom-credential-query* :subjectId, :password Either verifies a password for a user-id, or resolves the user’s password credential to verify using a Credential Manager’s configured Password Translator. All the attributes returned by this query can be used for further processing, i.e. as custom claims in issued tokens
custom-attribute-query :subject Retrieves all the attributes for a given user-id

* Custom credential queries are only supported in the default credentials mode.

The custom attribute query can be useful when certain attributes for the account are found in another table or database. Setup a new data source with a custom query for the attributes, and use it in a transformer or token procedure to retrieve the extra details about the user.

Audit

A SQL datasource can be configured to collect Audit Events by toggling the use-for-audit configuration parameter. Only a single SQL datasource can have this feature enabled at a time.

Note

If you are using PostgreSQL or CockroachDB datasource to log audit events, please see PostgreSQL and CockroachDB Audit Log Configuration.

Clustering

Depending on the backend type the clustering can look different. However the most common setup is to use an Active-Active cluster where the data is replicated in real-time. Curity should be configured to either communicate with a load balancer for the DB or to load balance using the connection string configuration. It is not recommended to use a round robin load-balancing since that is known to cause race conditions in Active-Active clusters. A better approach is to use a failover setup where one DB node gets more traffic and then Curity fails over to the secondary nodes when needed. See the data base vendor’s documentation for how to setup the failover and loadbalancing using the JDBC connection string.

Connection Pool Metrics

The data source can be configured to expose metrics of the underlying connection pool via the enable-pool-metrics setting. The available metrics are described in the following table:

Metric Name Meaning
idsvr_datasource_jdbc_pool_connection_acquired How long (in seconds) requesting threads are waiting for a connection from the pool.
idsvr_datasource_jdbc_pool_connection_usage How long (in seconds) each connection is used before being returned to the pool.
idsvr_datasource_jdbc_pool_connection_timeout_total The number of timeouts while waiting for a connection from the pool.
idsvr_datasource_jdbc_pool_connections The total number of connections in the pool.
idsvr_datasource_jdbc_pool_active_connections The number of active (in-use) connections in the pool.
idsvr_datasource_jdbc_pool_pending_threads The number of threads awaiting connections from the pool.

All metrics include the ds_id label and are exposed alongside other node metrics, as described in Monitoring.

Credential Data Access

By default, the query used to retrieve credential data checks the account status, and no data is returned for inactive accounts. In such cases, the credential verification done by the Curity Identity Server always fails.

Credential Modes

In recent versions of the Curity Identity Server the database schema was subject to some changes to better fit scenarios where user accounts and their passwords are stored in different databases/data-sources and to support new features around credential management (namely password policies). The JDBC data source can be configured with a “credentials mode”, which determines the expected schema and how the data source interacts with the database for credential data access. If nothing is configured, the previous behavior is kept.

The possible modes are described in the following table:

Mode Description
credentials-in-accounts-table The current default mode, using the schema from before credential modes were introduced. The main account data and the corresponding password are stored in the accounts table. When credentials data access targets a different database, the accounts table is still used in that database. The table contains the password, but some columns are left null/empty.
standard In this mode a new credentials table is added to the schema. The main account data is stored in the accounts table, but the password column is not used. Instead, passwords are stored in the credentials table. When credentials data access targets a different database, only the credentials table is used. This is the recommended mode. In an upcoming release of the Curity Identity Server, this mode will become the default when a new instance of the data source is added.
credentials-migration This mode is meant for migrating data from the schema used by the credentials-in-accounts-table mode to what’s expected by the standard mode. Passwords are copied on-the-fly from accounts into credentials and the two tables are kept in sync, to also allow rollbacks.

Important notes:

  • The bundled SQL files containing the schema definition were updated to be compatible with all the modes. Refer to upgrade guides for more details.
  • The standard mode must be configured in order to use Credential Policies, as this is the only mode that supports storing credential attributes.

MySQL and MariaDB

When using MySQL or MariaDB the driver needs to be downloaded from Oracle or MariaDB.

Installation

  1. Run the init db script from $IDSVR_HOME/etc/mysql-create_database.sql to setup the tables
  2. Download the driver from the MySQL website and place the JAR file in $IDSVR_HOME/lib/plugins/data.access.jdbc. (all nodes)
  3. Restart the server
  4. Configure the data-source

Minimal configuration:

Parameter Value
connection-string jdbc:mysql://MYSQL_HOST:3306/se_curity_store?useSSL=false
driver com.mysql.jdbc.Driver
username The db user
password The db user’s password

Note

The connection string is not optimized and only provided as an example.

Table maintenance examples

Note

If done infrequently these procedures should limit the number of entries they operate on since they often lock the table.

Listing 31 Clean up nonce table
1
2
USE se_curity_store;
DELETE FROM nonces WHERE status = 'used';
Listing 32 Clean up sessions table
1
2
USE se_curity_store;
DELETE FROM sessions WHERE expires < unix_timestamp(now()))
Listing 33 Clean up tokens table
1
2
USE se_curity_store;
DELETE FROM tokens WHERE expires < unix_timestamp(now()))
Listing 34 Clean up delegations table
1
2
USE se_curity_store;
DELETE FROM delegations WHERE expires < unix_timestamp(now()))

Microsoft SQL Server

Microsoft SQL server can be run either as on premise or in Azure as a cloud instance. Both are configured with the same drivers.

The driver is shipped with Curity, but if you need to upgrade the driver to a later version it is found in $IDSVR_HOME/lib/plugins/data.access.jdbc. Simply repace the existing jar with the new one. It is important to remove the current driver so that there only exists one. Then restart the nodes.

To configure the following is a minimal non-optimized configuration for SQL Server.

Parameter Value
connection-string jdbc:sqlserver://MSSQL_HOST:MSSQL_PORT;databaseName=se_curity_store;sendStringParametersAsUnicode=false;
driver com.microsoft.sqlserver.jdbc.SQLServerDriver
username The db user
password The db user’s password

Note

The connection string is not optimized and only provided as an example. We recommend that you consult the vendors documentation when configuring the JDBC driver.

VARCHAR vs NVARCHAR

Curity uses VARCHAR for compatibility reasons. There are some known performance issues whith prepared statements in the SQLServer driver from Microsoft, where the server converts unicode strings to ascii-strings before running the statement. This causes indexes to not be used as expected and can cause table scans. To avoid this the jdbc driver can be configured with sendStringParametersAsUnicode=false. This works well for western european languages, but is not tested with asian or non-latin character languages. If that is needed we suggest that to use another database or consult Microsoft.

Tip

Enable sendStringParametersAsUnicode=false in the jdbc driver settings to avoid performance issues with indexes.

Table maintenance examples

Note

If done infrequently these procedures should limit the number of entries they operate on since they often lock the table.

Listing 35 Clean up nonce table
1
2
USE se_curity_store;
DELETE FROM nonces WHERE status = 'used';
Listing 36 Clean up sessions table (with a limit)
1
2
DELETE TOP (10000) FROM se_curity_store.dbo.sessions
WHERE expires < DATEDIFF(s, '1970-01-01 00:00:00', CURRENT_TIMESTAMP)
Listing 37 Clean up tokens table (with a limit)
1
2
DELETE TOP (10000) FROM se_curity_store.dbo.tokens
WHERE expires < DATEDIFF(s, '1970-01-01 00:00:00', CURRENT_TIMESTAMP)
Listing 38 Clean up delegations table (with a limit)
1
2
DELETE TOP (10000) FROM se_curity_store.dbo.delegations
WHERE expires < DATEDIFF(s, '1970-01-01 00:00:00', CURRENT_TIMESTAMP)

PostgreSQL and CockroachDB

PostgreSQL and compatible databases such as CockroachDB can be configured with the driver shipped with Curity.

If you need to upgrade the driver to a later version it is found in $IDSVR_HOME/lib/plugins/data.access.jdbc. Simply replace the existing jar with the new one. It is important to remove the current driver so that there only exists one. Then restart the nodes.

To configure the following is a minimal non-optimized configuration for PostgreSQL and CockroachDB.

Note

If using CockroachDB, a flag needs to be added to the connection-string cockroach=true, i.e. jdbc:postgresql://POSTGRES_HOST:POSTGRES_PORT/se_curity_store;cockroach=true.

Parameter Value
connection-string jdbc:postgresql://POSTGRES_HOST:POSTGRES_PORT/se_curity_store jdbc:postgresql://POSTGRES_HOST:POSTGRES_PORT/se_curity_store;cockroach=true (if using CockroachDB)
driver org.postgresql.Driver
username The db user
password The db user’s password

Note

The connection string is not optimized and only provided as an example.

Oracle

When using Oracle the driver needs to be downloaded from Oracle.

Installation

  1. Run the init db script from $IDSVR_HOME/etc/oracle-create_database.sql to setup the tables
  2. Download the driver from the Oracle website and place the JAR file in $IDSVR_HOME/lib/plugins/data.access.jdbc. (all nodes)
  3. Restart the server
  4. Configure the data-source

Minimal configuration:

Parameter Value
connection-string jdbc:oracle:thin:@ORACLE_HOST:1521:ORCLCDB
driver oracle.jdbc.OracleDriver
username The db user
password The db user’s password

Note

The connection string is not optimized and only provided as an example.

Table maintenance examples

Note

If done infrequently these procedures should limit the number of entries they operate on since they often lock the table.

Listing 39 Clean up nonce table
1
DELETE FROM "nonces" WHERE "status" = 'used';
Listing 40 Clean up sessions table
1
DELETE FROM "sessions" WHERE "expires" < (SELECT ROUND((CURRENT_DATE - date '1970-01-01' ) * 60 * 60 * 24) FROM dual)
Listing 41 Clean up tokens table
1
DELETE FROM "tokens" WHERE "expires" < (SELECT ROUND((CURRENT_DATE - date '1970-01-01' ) * 60 * 60 * 24) FROM dual)
Listing 42 Clean up delegations table
1
DELETE FROM "delegations" WHERE expires < (SELECT ROUND((CURRENT_DATE - date '1970-01-01' ) * 60 * 60 * 24) FROM dual)

Oracle Driver and class serialization

The Oracle driver internally requires serializability of some of its classes. Since Curity restricts which Java types can be serialized by default, this will break the Oracle driver. To fix this, Java must be told to allow serializability of the Oracle driver’s classes. This can be done by including the Java system property se.curity:identity-server:serialFilter=oracle.jdbc.** when starting the Curity Identity Server, which can be done by setting the JAVA_OPTS environment variable (e.g., JAVA_OPTS=-Dse.curity:identity-server:serialFilter=oracle.jdbc.** idsvr)

See enable Java serialization for more details.

HsqlDB

HsqlDB is a file based database that cannot be used in production. It is shipped as a small getting started DB only. To setup HsqlDB on a new system use the following settings:

Parameter Value
connection-string jdbc:hsqldb:file:${se.curity:identity-server:db};ifexists=true;hsqldb.lock_file=false
driver org.hsqldb.jdbc.JDBCDriver
username SA

Curity will replace the inline variable in the jdbc string with the file based db delivered with the system.

The database does not support running Curity in a clustered mode, only single node operation is supported.

Warning

HsqlDB cannot be used in production. It is available for development purposes only.