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

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

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.

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.

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.

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.

* Custom credential queries are only supported in the default credentials mode, which is deprecated.

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.

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:

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 version 9.0.0 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 previous database schema is deprecated and support for its usage will be removed in a future major release.

To facilitate the transition to the newer database schema, the data source can be configured with a “credentials mode”. This mode 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 credential modes are described in the following table:

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:

Table maintenance examples#

Clean up nonce table:

USE se_curity_store;
DELETE FROM nonces WHERE status = 'used';

Clean up sessions table:

USE se_curity_store;
DELETE FROM sessions WHERE expires < unix_timestamp(now()))

Clean up tokens table:

USE se_curity_store;
DELETE FROM tokens WHERE expires < unix_timestamp(now()))

Clean up delegations table:

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.

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.

Table maintenance examples#

Clean up nonce table:

USE se_curity_store;
DELETE FROM nonces WHERE status = 'used';

Clean up sessions table (with a limit):

DELETE TOP (10000) FROM se_curity_store.dbo.sessions
WHERE expires < DATEDIFF(s, '1970-01-01 00:00:00', CURRENT_TIMESTAMP)

Clean up tokens table (with a limit):

DELETE TOP (10000) FROM se_curity_store.dbo.tokens
WHERE expires < DATEDIFF(s, '1970-01-01 00:00:00', CURRENT_TIMESTAMP)

Clean up delegations table (with a limit):

DELETE TOP (10000) FROM se_curity_store.dbo.delegations
WHERE expires < DATEDIFF(s, '1970-01-01 00:00:00', CURRENT_TIMESTAMP)

PostgreSQL#

PostgreSQL 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.

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:

Table maintenance examples#

Clean up nonce table:

DELETE FROM "nonces" WHERE "status" = 'used';

Clean up sessions table:

DELETE FROM "sessions" WHERE "expires" < (SELECT ROUND((CURRENT_DATE - date '1970-01-01' ) * 60 * 60 * 24) FROM dual)

Clean up tokens table:

DELETE FROM "tokens" WHERE "expires" < (SELECT ROUND((CURRENT_DATE - date '1970-01-01' ) * 60 * 60 * 24) FROM dual)

Clean up delegations table:

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:

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.