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.

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

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.

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 34 Clean up nonce table
1
2
USE se_curity_store;
DELETE FROM nonces WHERE status = 'used';
Listing 35 Clean up sessions table
1
2
USE se_curity_store;
DELETE FROM sessions WHERE expires < unix_timestamp(now()))
Listing 36 Clean up tokens table
1
2
USE se_curity_store;
DELETE FROM tokens WHERE expires < unix_timestamp(now()))
Listing 37 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 38 Clean up nonce table
1
2
USE se_curity_store;
DELETE FROM nonces WHERE status = 'used';
Listing 39 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 40 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 41 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 42 Clean up nonce table
1
DELETE FROM "nonces" WHERE "status" = 'used';
Listing 43 Clean up sessions table
1
DELETE FROM "sessions" WHERE "expires" < (SELECT ROUND((CURRENT_DATE - date '1970-01-01' ) * 60 * 60 * 24) FROM dual)
Listing 44 Clean up tokens table
1
DELETE FROM "tokens" WHERE "expires" < (SELECT ROUND((CURRENT_DATE - date '1970-01-01' ) * 60 * 60 * 24) FROM dual)
Listing 45 Clean up delegations table
1
DELETE FROM "delegations" WHERE expires < (SELECT ROUND((CURRENT_DATE - date '1970-01-01' ) * 60 * 60 * 24) FROM dual)

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.