CENM Databases

There are currently four types of CENM database schemas:

  • The Identity Manager database schema is used by the Identity Manager Service. It contains information relating to:

    • Certificate signing requests of nodes wanting to join the network.
    • Requests to revocation of nodes on the network.
  • The Network Map database schema is used by the Network Map Service. It contains information relating to:

    • The current participants on the network.
    • The current network parameters.
    • Any pending network parameter updates.
  • The Zone database schema is used by the Zone Service. It contains information relating to:

    • External addresses of services on the network.
    • Configurations of other services on the network.
  • The Auth database schema is used by the Auth Service to store RBAC data (users, permissions, groups).

The services must use separate database schemas (either in the same database instance or in completely separate instances) due to the way the migrations are defined. If you try and run an Identity Manager Service, a Network Map Service, a Zone Service, or an Auth Service that shares the same database schema, it will result in errors.

CENM currently supports the following databases:

  • PostgreSQL 9.6 (JDBC 42.2.8)
  • PostgreSQL 10.10 (JDBC 42.2.8)
  • PostgreSQL 12.2 (JDBC 42.2.8)
  • Azure SQL (Microsoft JDBC Driver 6.4)
  • SQL Server 2017 (Microsoft JDBC Driver 6.4)
  • Oracle 11gR2 (Oracle JDBC 6)
  • Oracle 12cR2 (Oracle JDBC 8)

The appropriate JDBC driver JAR file must be provided and its location should be specified in the service configuration.

This section describes the processes for:

  • Creating database schemas such as:
    • User permissions
    • The CENM service tables
    • Other database objects
  • Configuring CENM services to connect to a database with restricted permissions for production use.

To set up a database that the service will use, and to configure the service to connect to it after that, follow the steps below.

A database administrator must create a database user and a schema namespace with restricted permissions. This grants the user access to Data Manipulation Language (DML) execution only (to manipulate data itself - for example, select/delete rows). This permission set is recommended for production environments.

Variants of Data Definition Language (DDL) scripts are provided for each supported database vendor. The example permissions scripts have no group roles and do not specify physical database settings (such as the maximum disk space quota allocated for the database user). The scripts and service configuration snippets contain placeholder values, as follows:

  • my_login for login
  • my_user / my_admin_user for users
  • my_password for password
  • my_schema for the schema name

Create database users with schema permissions for:

Create two database users where the first one has permissions to create schema objects and the second one has restricted permissions for a CENM service instance. The schema objects are created by a separate user rather than a default database administrator. This ensures the correct schema namespace is used.

  1. Connect to the master database as an administrator - for example:
jdbc:sqlserver://<database_server>.database.windows.net:1433;databaseName=master;[]
  1. Run the following script to create both users and their logins:
CREATE LOGIN my_admin_login WITH PASSWORD = 'my_password';
CREATE USER my_admin_user FOR LOGIN my_admin_login;
CREATE LOGIN my_login WITH PASSWORD = 'my_password';
CREATE USER my_user FOR LOGIN my_login;

Passwords must contain characters from three of the following four sets: uppercase letters, lowercase letters, digits, and symbols. For example, C3NMP4ssword is a valid password. Passwords are delimited with single quotes. Use different passwords for my_admin_user and my_user.

  1. Connect to a user database as database administrator (replace master with a user database in the connection string).

  2. Run the following script to create a schema:

CREATE SCHEMA my_schema;

After creating the schema you may need to commit the change - for example, with the GO command on Microsoft tools, or commit; on other tools.

  1. Run the following script to assign user permissions:
CREATE USER my_admin_user FOR LOGIN my_admin_login WITH DEFAULT_SCHEMA = my_schema;
GRANT ALTER ON SCHEMA::my_schema TO my_admin_user;
GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION, REFERENCES ON SCHEMA::my_schema TO my_admin_user;
GRANT CREATE TABLE TO my_admin_user;
GRANT CREATE VIEW TO my_admin_user;
CREATE USER my_user FOR LOGIN my_login WITH DEFAULT_SCHEMA = my_schema;
GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION, REFERENCES ON SCHEMA::my_schema TO my_user;

Create two database users where the first user has administrative permissions to create schema objects, and the second user has restricted permissions for a CENM service instance. The schema objects are created by a separate user rather than a default database administrator. This ensures that the correct schema namespace is used.

  1. Connect to a master database as an administrator - for example:
jdbc:sqlserver://<host>:<port>;databaseName=master
  1. Run the following script to create a database, a user, and a login:
CREATE DATABASE my_database;
CREATE LOGIN my_admin_login WITH PASSWORD = 'my_password', DEFAULT_DATABASE = my_database;
CREATE USER my_admin_user FOR LOGIN my_admin_login;
CREATE LOGIN my_login WITH PASSWORD = 'my_password', DEFAULT_DATABASE = my_database;
CREATE USER my_user FOR LOGIN my_login;

Passwords must contain characters from three of the following four sets: uppercase letters, lowercase letters, digits, and symbols. For example, C3NMP4ssword is a valid password. Passwords are delimited with single quotes. Use different passwords for my_admin_user and my_user.

You can create schemas for several instances of CENM services within the same database (my_database). In that case, run the first DDL statement (CREATE DATABASE my_database;) only once.

  1. Connect to a user database as the administrator (replace master with my_database in the connection string).

  2. Run the following script to create a schema:

CREATE SCHEMA my_schema;

After creating the schema you may need to commit the change - for example, with the GO command on Microsoft tools, or commit; on other tools.

  1. Run the following script to assign user permissions:
CREATE USER my_admin_user FOR LOGIN my_admin_login WITH DEFAULT_SCHEMA = my_schema;
GRANT ALTER ON SCHEMA::my_schema TO my_admin_user;
GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION, REFERENCES ON SCHEMA::my_schema TO my_admin_user;
GRANT CREATE TABLE TO my_admin_user;
GRANT CREATE VIEW TO my_admin_user;
CREATE USER my_user FOR LOGIN my_login WITH DEFAULT_SCHEMA = my_schema;
GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION, REFERENCES ON SCHEMA::my_schema TO my_user;

As CENM databases require some VARCHAR2 or NVARCHAR2 column types to store more than 2000 characters, ensure that the database instance is configured to use extended data types. For example, for Oracle 12.1, see MAX_STRING_SIZE.

The recommended configuration for CENM with Oracle is a one-to-one relationship between schemas and user accounts, so the user has full control over that schema.

In order to restrict the permissions to the database:

  1. Create two users where one user has administrative permissions (my_admin_user in the SQL script) and the other user just has read only permissions (my_user in the SQL script).

A database administrator can create schema objects (tables/sequences) via a user with administrative permissions. The CENM service instance accesses the schema created by the administrator via a user with restricted permissions, allowing them to only select/insert/delete data. For Oracle databases, these permissions (SELECT, INSERT, UPDATE, DELETE) need to be granted explicitly for each table.

As the tablespace size in the sample script below is unlimited, adjust the value (for example, 100M, 1 GB) depending on the sizing requirements for your nodes. The script uses the default tablespace users with unlimited database space quota assigned to the user. Revise these settings depending on the sizing requirements for your nodes.

  1. Run this script as a database administrator:
CREATE USER my_admin_user IDENTIFIED BY my_password DEFAULT TABLESPACE users QUOTA unlimited ON users;
GRANT CREATE SESSION TO my_admin_user;
GRANT CREATE TABLE TO my_admin_user;
GRANT CREATE VIEW TO my_admin_user;
GRANT CREATE SEQUENCE TO my_admin_user;
GRANT SELECT ON v_$parameter TO my_admin_user;

The assignment of permissions, required for the CENM service instance user to access database objects, is done after the database objects are created. These steps are described in the next section.

The last permission for the v_$parameter view is needed when a database is running in Database Compatibility mode.

To create a CENM service instance user in PostgreSQL, connect to the database as an administrator and run the following script:

CREATE USER "my_user" WITH LOGIN PASSWORD 'my_password';
CREATE SCHEMA "my_schema";
GRANT USAGE ON SCHEMA "my_schema" TO "my_user";
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON ALL tables IN SCHEMA "my_schema" TO "my_user";
ALTER DEFAULT privileges IN SCHEMA "my_schema" GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON tables TO "my_user";
GRANT USAGE, SELECT ON ALL sequences IN SCHEMA "my_schema" TO "my_user";
ALTER DEFAULT privileges IN SCHEMA "my_schema" GRANT USAGE, SELECT ON sequences TO "my_user";
ALTER ROLE "my_user" SET search_path = "my_schema";

If you provide a custom schema name (different from the user name), then the last statement in the script - setting the search_path - prevents querying the differing (default schema search path).

The general steps for creating database schemas are listed below, followed by specific instructions for Oracle.

  1. Deploy the CENM services first with database administrator credentials, specified in the database.user and database.password configuration files. If the schema exists and you have administrative permissions, the Liquibase migrations will run on start-up and automatically create the tables under the schema.

  2. After you create the tables, substitute the database user and password settings in the service configuration file with the CENM service instance user credentials with restricted permissions.

For Oracle databases, you must also add permissions to use tables.

To do so, connect to the Oracle database as a database administrator and run the following DDL scripts:

Identity Manager

CREATE USER my_user identified by my_password;
GRANT CREATE SESSION TO my_user;
GRANT SELECT ON my_admin_user.DATABASECHANGELOG to my_user;
GRANT SELECT ON my_admin_user.DATABASECHANGELOGLOCK to my_user;
GRANT SELECT ON my_admin_user.HIBERNATE_SEQUENCE TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.certificate_revocation_list TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.certificate_revocation_request TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.certificate_data TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.certificate_signing_request TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.private_network_global TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.migration TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.REVINFO TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.workflow_csr TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.workflow_crr TO my_user;

Network Map Service

CREATE USER my_user identified by my_password;
GRANT CREATE SESSION TO my_user;
GRANT SELECT ON my_admin_user.DATABASECHANGELOG TO my_user;
GRANT SELECT ON my_admin_user.DATABASECHANGELOGLOCK TO my_user;
GRANT SELECT ON my_admin_user.HIBERNATE_SEQUENCE TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.certificate_chain TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.network_map TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.network_parameters TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.network_parameters_update TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.node_info TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.private_network TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.migration TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.node_info_staging TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.node_info_quarantine TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.REVINFO TO my_user;

Zone Service

CREATE USER my_user identified by my_password;
GRANT CREATE SESSION TO my_user;
GRANT SELECT ON my_admin_user.DATABASECHANGELOG to my_user;
GRANT SELECT ON my_admin_user.DATABASECHANGELOGLOCK to my_user;
GRANT SELECT ON my_admin_user.HIBERNATE_SEQUENCE TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.CONFIGURATION_DEPLOYMENT;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.CONFIGURATION_METADATA to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.DATABASECHANGELOG to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.DATABASECHANGELOGLOCK to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.DATABASE_CONFIG to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.DATABASE_ADDITIONAL_PROPERTIES to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.IDENTITY_MANAGER_CONFIG to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.KEY_STORE to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.LOCAL_SIGNER to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.MIGRATION to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NETWORK_MAP_CONFIG to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NETWORK_PARAMETERS_CONFIG to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NOTARY_CONFIG to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.PACKAGE_OWNERSHIP_CONFIG to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.PARAMETERS_UPDATE_CONFIG to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.SHELL_CONFIG to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.SIGNER_CONFIG to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.SIGNER_CONFIG_HSM to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.SIGNER_CONFIG_KEY to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.SIGNER_CONFIG_LOCAL_KEYSTORE to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.SIGNER_CONFIG_TASK to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.SOCKET_CONFIG to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.SSL_CONFIG to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.SUBZONE to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.TRUST_STORE to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.WHITELIST_CONTRACTS_CONFIG to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.WHITELIST_CONTRACTS_CORDAPPS to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.WHITELIST_CONTRACTS_EXCLUDE to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.WHITELIST_CONTRACT_ATTACH_IDS to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.WHITELIST_CONTRACT_CONFIG to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.WORKFLOW_CONFIG to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.WORKFLOW_CONFIG_CRL_FILES to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.ZONE to my_user;

Auth Service

CREATE USER my_user identified by my_password;
GRANT CREATE SESSION TO my_user;
GRANT SELECT ON my_admin_user.DATABASECHANGELOG to my_user;
GRANT SELECT ON my_admin_user.DATABASECHANGELOGLOCK to my_user;
GRANT SELECT ON my_admin_user.SEQ_AUDIT_EVENTS to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.AUDIT_EVENTS to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.AUDIT_EVENT_DATA to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.BASELINE_CHANGES to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.BASELINE_LOCK to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.DATABASECHANGELOG to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.DATABASECHANGELOGLOCK to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.GROUPS to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.GROUP_DATA to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.OAUTH_REFRESH_TOKENS to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.PERMISSIONS to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.PERMISSION_ASSIGNMENTS to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.ROLES to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.ROLE_ASSIGNMENTS to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.USERS to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.USERS_IN_GROUPS to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.USER_DATA to my_user;

The required updates to the file system of a CENM service instance are described below.

The CENM service configuration file - identitymanager.conf or networkmap.conf, respectively - must contain JDBC connection properties in the database entry, along with other database properties (passed to a CENM service JPA persistence provider or schema creation/upgrade flag). The database entry format is shown below:

database = {
        jdbcDriver = <path to JDBC driver JAR file>
        driverClassName = <JDBC driver class name>
        url = <JDBC database URL>
        user = <Database user>
        password = <Database password>
        transactionIsolationLevel = <Transaction isolation level>
        schema = <Database schema name>
        runMigration = false
    }

runMigration is set to false because the restricted CENM service instance database user does not have permissions to alter a database schema. See CENM Database Configuration for a complete list of database-specific properties.

Corda uses Hikari Pool for creating connection pools. To configure a connection pool, set custom properties in the database section - for example:

database = {
   ...
   additionalProperties = {
       maximumPoolSize = 10
       connectionTimeout = 50000
   }
}

Configuration templates for each database vendor are shown below:

See below an example CENM services configuration file for Azure SQL - initial deployment with administrative permissions:

database = {
    jdbcDriver = path/to/mssql-jdbc-x.x.x.jre8.jar
    driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    url = "jdbc:sqlserver://<database_server>.database.windows.net:1433;databaseName=<my_database>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"
    user = my_admin_login
    password = "my_admin_password"
    schema = my_schema
    runMigration = true
}

See below an example CENM service configuration file for Azure SQL restrictive permissions - CENM service instance database user with restrictive permissions:

database = {
    jdbcDriver = path/to/mssql-jdbc-x.x.x.jre8.jar
    driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    url = "jdbc:sqlserver://<database_server>.database.windows.net:1433;databaseName=<my_database>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"
    user = my_login
    password = "my_password"
    schema = my_schema
    runMigration = false
}

Replace the placeholders <database_server> and <my_database> with appropriate values (<my_database> is a user database). The database.schema is the database schema name assigned to the user.

You can download the Microsoft SQL JDBC driver from Microsoft Download Center - extract the downloaded archive and copy the file mssql-jdbc-6.2.2.jre8.jar (the archive comes with two JAR files). The Database configuration section further below explains the correct location for the driver JAR file in the CENM service installation structure.

See below an example CENM services configuration file for SQL Server - initial deployment with administrative permissions:

database = {
    jdbcDriver = path/to/mssql-jdbc-x.x.x.jre8.jar
    driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    url = "jdbc:sqlserver://<host>:<port>;databaseName=my_database"
    user = my_admin_login
    password = "my_admin_password"
    schema = my_schema
    runMigration = true
}

See below an example CENM service configuration file for SQL Server - CENM service instance database user with restricted permissions:

database = {
    jdbcDriver = path/to/mssql-jdbc-x.x.x.jre8.jar
    driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    url = "jdbc:sqlserver://<host>:<port>;databaseName=my_database"
    user = my_login
    password = "my_password"
    schema = my_schema
    runMigration = false
}

Replace the placeholders and with appropriate values (the default SQL Server port is 1433). By default, the connection to the database is not SSL. To secure the JDBC connection, refer to Securing JDBC Driver Applications.

You can download the Microsoft JDBC 6.2 driver from Microsoft Download Center - extract the downloaded archive and copy the file mssql-jdbc-6.2.2.jre8.jar (the archive comes with two JAR files). The Database configuration section further below explains the correct location for the driver JAR file in the CENM service installation structure.

Ensure that the JDBC connection properties match the SQL Server setup, especially when trying to reuse Azure SQL JDBC URLs, which are invalid for SQL Server. This may lead to CENM failing to start with the following message:

Caused by: org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when ‘hibernate.dialect’ not set

See below an example CENM service configuration file for Oracle database - initial deployment with administrative permissions:

database = {
    jdbcDriver = path/to/ojdbcx.jar
    driverClassName = "oracle.jdbc.driver.OracleDriver"
    url = "jdbc:oracle:thin:@<host>:<port>:<sid>"
    user = my_admin_user
    password = "my_admin_password"
    schema = my_admin_user
}

See below an example CENM service configuration file for Oracle database - CENM service instance database user with restrictive permissions:

database = {
    jdbcDriver = path/to/ojdbcx.jar
    driverClassName = "oracle.jdbc.driver.OracleDriver"
    url = "jdbc:oracle:thin:@<host>:<port>:<sid>"
    user = my_user
    password = "my_password"
    runMigration = false
    schema = my_admin_user
    additionalProperties {
        connectionInitSql="alter session set current_schema=my_admin_user"
    }
}

Replace the placeholders , , and with appropriate values. For a basic Oracle installation, the default value is xe.

Set the database.schema value to the username of the admin user (in the example above - my_admin_user). CENM does not guarantee prefixing all SQL queries with the schema namespace. The additional configuration entry connectionInitSql sets the current schema to the username of the admin user (in the example above - my_admin_user) on connection to the database.

The transaction isolation level is set by CENM to READ_COMMITTED - an attempt to set another isolation level in the configuration will result in an error. This is intentional behaviour, as READ_UNCOMMITTED results in inconsistent data reads, and REPEATABLE_READ and SERIALIZABLE are not compatible with Corda.

Use Oracle JDBC driver ojdbc6.jar for 11g RC2 or ojdbc8.jar for Oracle 12c. You can find links to the appropriate drivers on Oracle website. The database schema name can be set in a JDBC URL string - for example, currentSchema=my_schema.

See below an example CENM service configuration for PostgreSQL:

database = {
    jdbcDriver = path/to/postgresql-xx.x.x.jar
    driverClassName = "org.postgresql.Driver"
    url = "jdbc:postgresql://<host>:<port>/<database>"
    user = my_user
    password = "my_password"
    schema = my_schema
}

Replace the placeholders , , and with appropriate values. The database.schema is the database schema name assigned to you (the user). The value of database.schema is automatically wrapped in double quotes to preserve case-sensitivity (without quotes, PostgresSQL would treat AliceCorp as the value alicecorp).

This section provides additional vendor-specific database configuration details.

The database collation for SQL Server should be case insensitive - see Server Configuration documentation for more information.

To allow VARCHAR2 and NVARCHAR2 column types to store more than 2000 characters, ensure the database instance is configured to use extended data types.

Note that <SCHEMA_NAME> below is a placeholder value representing the actual name for the appropriate schema.

The list of tables created by the Identity Manager Service follows below:

<SCHEMA_NAME>.DATABASECHANGELOG
<SCHEMA_NAME>.DATABASECHANGELOGLOCK
<SCHEMA_NAME>.certificate_data
<SCHEMA_NAME>.certificate_revocation_list
<SCHEMA_NAME>.certificate_revocation_request
<SCHEMA_NAME>.certificate_signing_request
<SCHEMA_NAME>.private_network_global
<SCHEMA_NAME>.workflow_csr
<SCHEMA_NAME>.workflow_crr
<SCHEMA_NAME>.migration
<SCHEMA_NAME>.REVINFO

The list of tables created by the Network Map Service follows below:

<SCHEMA_NAME>.DATABASECHANGELOG
<SCHEMA_NAME>.DATABASECHANGELOGLOCK
<SCHEMA_NAME>.certificate_chain
<SCHEMA_NAME>.network_map
<SCHEMA_NAME>.network_parameters
<SCHEMA_NAME>.network_parameters_update
<SCHEMA_NAME>.node_info
<SCHEMA_NAME>.private_network
<SCHEMA_NAME>.node_info_staging
<SCHEMA_NAME>.node_info_quarantine
<SCHEMA_NAME>.migration
<SCHEMA_NAME>.REVINFO

The list of tables created by the Zone Service follows below:

<SCHEMA_NAME>.CONFIGURATION_DEPLOYMENT
<SCHEMA_NAME>.CONFIGURATION_METADATA
<SCHEMA_NAME>.DATABASECHANGELOG
<SCHEMA_NAME>.DATABASECHANGELOGLOCK
<SCHEMA_NAME>.DATABASE_CONFIG
<SCHEMA_NAME>.IDENTITY_MANAGER_CONFIG
<SCHEMA_NAME>.KEY_STORE
<SCHEMA_NAME>.LOCAL_SIGNER
<SCHEMA_NAME>.MIGRATION
<SCHEMA_NAME>.NETWORK_MAP_CONFIG
<SCHEMA_NAME>.NETWORK_PARAMETERS_CONFIG
<SCHEMA_NAME>.NOTARY_CONFIG
<SCHEMA_NAME>.PACKAGE_OWNERSHIP_CONFIG
<SCHEMA_NAME>.PARAMETERS_UPDATE_CONFIG
<SCHEMA_NAME>.SHELL_CONFIG
<SCHEMA_NAME>.SIGNER_CONFIG
<SCHEMA_NAME>.SIGNER_CONFIG_HSM
<SCHEMA_NAME>.SIGNER_CONFIG_KEY
<SCHEMA_NAME>.SIGNER_CONFIG_LOCAL_KEYSTORE
<SCHEMA_NAME>.SIGNER_CONFIG_TASK
<SCHEMA_NAME>.SOCKET_CONFIG
<SCHEMA_NAME>.SSL_CONFIG
<SCHEMA_NAME>.SUBZONE
<SCHEMA_NAME>.TRUST_STORE
<SCHEMA_NAME>.WHITELIST_CONTRACTS_CONFIG
<SCHEMA_NAME>.WHITELIST_CONTRACTS_CORDAPPS
<SCHEMA_NAME>.WHITELIST_CONTRACTS_EXCLUDE
<SCHEMA_NAME>.WHITELIST_CONTRACT_ATTACH_IDS
<SCHEMA_NAME>.WHITELIST_CONTRACT_CONFIG
<SCHEMA_NAME>.WORKFLOW_CONFIG
<SCHEMA_NAME>.WORKFLOW_CONFIG_CRL_FILES
<SCHEMA_NAME>.ZONE

The list of tables created by the Auth Service follows below:

<SCHEMA_NAME>.AUDIT_EVENTS
<SCHEMA_NAME>.AUDIT_EVENT_DATA
<SCHEMA_NAME>.BASELINE_CHANGES
<SCHEMA_NAME>.BASELINE_LOCK
<SCHEMA_NAME>.DATABASECHANGELOG
<SCHEMA_NAME>.DATABASECHANGELOGLOCK
<SCHEMA_NAME>.GROUPS
<SCHEMA_NAME>.GROUP_DATA
<SCHEMA_NAME>.OAUTH_REFRESH_TOKENS
<SCHEMA_NAME>.PERMISSIONS
<SCHEMA_NAME>.PERMISSION_ASSIGNMENTS
<SCHEMA_NAME>.ROLES
<SCHEMA_NAME>.ROLE_ASSIGNMENTS
<SCHEMA_NAME>.USERS
<SCHEMA_NAME>.USERS_IN_GROUPS
<SCHEMA_NAME>.USER_DATA

Clearing the database will depend on the exact database that you are running.

The general scripts for clearing the Identity Manager Service database and Network Map Service database are provided below.

DROP TABLE IF EXISTS <SCHEMA_NAME>.DATABASECHANGELOG;
DROP TABLE IF EXISTS <SCHEMA_NAME>.DATABASECHANGELOGLOCK;
DROP TABLE IF EXISTS <SCHEMA_NAME>.certificate_revocation_list;
DROP TABLE IF EXISTS <SCHEMA_NAME>.certificate_revocation_request;
DROP TABLE IF EXISTS <SCHEMA_NAME>.certificate_data;
DROP TABLE IF EXISTS <SCHEMA_NAME>.certificate_signing_request;
DROP TABLE IF EXISTS <SCHEMA_NAME>.private_network_global;
DROP TABLE IF EXISTS <SCHEMA_NAME>.workflow_csr;
DROP TABLE IF EXISTS <SCHEMA_NAME>.workflow_crr;
DROP TABLE IF EXISTS <SCHEMA_NAME>.migration;
DROP TABLE IF EXISTS <SCHEMA_NAME>.REVINFO;
DROP SEQUENCE IF EXISTS <SCHEMA_NAME>.hibernate_sequence;
DROP TABLE IF EXISTS <SCHEMA_NAME>.DATABASECHANGELOG;
DROP TABLE IF EXISTS <SCHEMA_NAME>.DATABASECHANGELOGLOCK;
DROP TABLE IF EXISTS <SCHEMA_NAME>.certificate_chain;
DROP TABLE IF EXISTS <SCHEMA_NAME>.network_map;
DROP TABLE IF EXISTS <SCHEMA_NAME>.network_parameters;
DROP TABLE IF EXISTS <SCHEMA_NAME>.network_parameters_update;
DROP TABLE IF EXISTS <SCHEMA_NAME>.node_info;
DROP TABLE IF EXISTS <SCHEMA_NAME>.private_network;
DROP TABLE IF EXISTS <SCHEMA_NAME>.migration;
DROP TABLE IF EXISTS <SCHEMA_NAME>.node_info_staging;
DROP TABLE IF EXISTS <SCHEMA_NAME>.node_info_quarantine;
DROP TABLE IF EXISTS <SCHEMA_NAME>.REVINFO;
DROP SEQUENCE IF EXISTS <SCHEMA_NAME>.hibernate_sequence;
DROP TABLE IF EXISTS <SCHEMA_NAME>.CONFIGURATION_DEPLOYMENT;
DROP TABLE IF EXISTS <SCHEMA_NAME>.CONFIGURATION_METADATA;
DROP TABLE IF EXISTS <SCHEMA_NAME>.DATABASECHANGELOG;
DROP TABLE IF EXISTS <SCHEMA_NAME>.DATABASECHANGELOGLOCK;
DROP TABLE IF EXISTS <SCHEMA_NAME>.DATABASE_CONFIG;
DROP TABLE IF EXISTS <SCHEMA_NAME>.IDENTITY_MANAGER_CONFIG;
DROP TABLE IF EXISTS <SCHEMA_NAME>.KEY_STORE;
DROP TABLE IF EXISTS <SCHEMA_NAME>.LOCAL_SIGNER;
DROP TABLE IF EXISTS <SCHEMA_NAME>.MIGRATION;
DROP TABLE IF EXISTS <SCHEMA_NAME>.NETWORK_MAP_CONFIG;
DROP TABLE IF EXISTS <SCHEMA_NAME>.NETWORK_PARAMETERS_CONFIG;
DROP TABLE IF EXISTS <SCHEMA_NAME>.NOTARY_CONFIG;
DROP TABLE IF EXISTS <SCHEMA_NAME>.PACKAGE_OWNERSHIP_CONFIG;
DROP TABLE IF EXISTS <SCHEMA_NAME>.PARAMETERS_UPDATE_CONFIG;
DROP TABLE IF EXISTS <SCHEMA_NAME>.SHELL_CONFIG;
DROP TABLE IF EXISTS <SCHEMA_NAME>.SIGNER_CONFIG;
DROP TABLE IF EXISTS <SCHEMA_NAME>.SIGNER_CONFIG_HSM;
DROP TABLE IF EXISTS <SCHEMA_NAME>.SIGNER_CONFIG_KEY;
DROP TABLE IF EXISTS <SCHEMA_NAME>.SIGNER_CONFIG_LOCAL_KEYSTORE;
DROP TABLE IF EXISTS <SCHEMA_NAME>.SIGNER_CONFIG_TASK;
DROP TABLE IF EXISTS <SCHEMA_NAME>.SOCKET_CONFIG;
DROP TABLE IF EXISTS <SCHEMA_NAME>.SSL_CONFIG;
DROP TABLE IF EXISTS <SCHEMA_NAME>.SUBZONE;
DROP TABLE IF EXISTS <SCHEMA_NAME>.TRUST_STORE;
DROP TABLE IF EXISTS <SCHEMA_NAME>.WHITELIST_CONTRACTS_CONFIG;
DROP TABLE IF EXISTS <SCHEMA_NAME>.WHITELIST_CONTRACTS_CORDAPPS;
DROP TABLE IF EXISTS <SCHEMA_NAME>.WHITELIST_CONTRACTS_EXCLUDE;
DROP TABLE IF EXISTS <SCHEMA_NAME>.WHITELIST_CONTRACT_ATTACH_IDS;
DROP TABLE IF EXISTS <SCHEMA_NAME>.WHITELIST_CONTRACT_CONFIG;
DROP TABLE IF EXISTS <SCHEMA_NAME>.WORKFLOW_CONFIG;
DROP TABLE IF EXISTS <SCHEMA_NAME>.WORKFLOW_CONFIG_CRL_FILES;
DROP TABLE IF EXISTS <SCHEMA_NAME>.ZONE;
DROP SEQUENCE IF EXISTS <SCHEMA_NAME>.hibernate_sequence;
DROP TABLE IF EXISTS <SCHEMA_NAME>.AUDIT_EVENTS;
DROP TABLE IF EXISTS <SCHEMA_NAME>.AUDIT_EVENT_DATA;
DROP TABLE IF EXISTS <SCHEMA_NAME>.BASELINE_CHANGES;
DROP TABLE IF EXISTS <SCHEMA_NAME>.BASELINE_LOCK;
DROP TABLE IF EXISTS <SCHEMA_NAME>.DATABASECHANGELOG;
DROP TABLE IF EXISTS <SCHEMA_NAME>.DATABASECHANGELOGLOCK;
DROP TABLE IF EXISTS <SCHEMA_NAME>.GROUPS;
DROP TABLE IF EXISTS <SCHEMA_NAME>.GROUP_DATA;
DROP TABLE IF EXISTS <SCHEMA_NAME>.OAUTH_REFRESH_TOKENS;
DROP TABLE IF EXISTS <SCHEMA_NAME>.PERMISSIONS;
DROP TABLE IF EXISTS <SCHEMA_NAME>.PERMISSION_ASSIGNMENTS;
DROP TABLE IF EXISTS <SCHEMA_NAME>.ROLES;
DROP TABLE IF EXISTS <SCHEMA_NAME>.ROLE_ASSIGNMENTS;
DROP TABLE IF EXISTS <SCHEMA_NAME>.USERS;
DROP TABLE IF EXISTS <SCHEMA_NAME>.USERS_IN_GROUPS;
DROP TABLE IF EXISTS <SCHEMA_NAME>.USER_DATA;
DROP SEQUENCE IF EXISTS <SCHEMA_NAME>.SEQ_AUDIT_EVENTS;
DROP TABLE <IM_ADMIN_USER>.DATABASECHANGELOG CASCADE CONSTRAINTS;
DROP TABLE <IM_ADMIN_USER>.DATABASECHANGELOGLOCK CASCADE CONSTRAINTS;
DROP TABLE <IM_ADMIN_USER>.certificate_revocation_list CASCADE CONSTRAINTS;
DROP TABLE <IM_ADMIN_USER>.certificate_revocation_request CASCADE CONSTRAINTS;
DROP TABLE <IM_ADMIN_USER>.certificate_data CASCADE CONSTRAINTS;
DROP TABLE <IM_ADMIN_USER>.certificate_signing_request CASCADE CONSTRAINTS;
DROP TABLE <IM_ADMIN_USER>.private_network_global CASCADE CONSTRAINTS;
DROP TABLE <IM_ADMIN_USER>.workflow_csr;
DROP TABLE <IM_ADMIN_USER>.workflow_crr;
DROP TABLE <IM_ADMIN_USER>.migration CASCADE CONSTRAINTS;
DROP TABLE <IM_ADMIN_USER>.REVINFO;
DROP SEQUENCE <IM_ADMIN_USER>.hibernate_sequence;
DROP TABLE <NM_ADMIN_USER>.DATABASECHANGELOG CASCADE CONSTRAINTS;
DROP TABLE <NM_ADMIN_USER>.DATABASECHANGELOGLOCK CASCADE CONSTRAINTS;
DROP TABLE <NM_ADMIN_USER>.certificate_chain CASCADE CONSTRAINTS;
DROP TABLE <NM_ADMIN_USER>.network_map CASCADE CONSTRAINTS;
DROP TABLE <NM_ADMIN_USER>.network_parameters CASCADE CONSTRAINTS;
DROP TABLE <NM_ADMIN_USER>.network_parameters_update CASCADE CONSTRAINTS;
DROP TABLE <NM_ADMIN_USER>.node_info CASCADE CONSTRAINTS;
DROP TABLE <NM_ADMIN_USER>.private_network CASCADE CONSTRAINTS;
DROP TABLE <NM_ADMIN_USER>.migration CASCADE CONSTRAINTS;
DROP TABLE <NM_ADMIN_USER>.node_info_staging CASCADE CONSTRAINTS;
DROP TABLE <NM_ADMIN_USER>.node_info_quarantine CASCADE CONSTRAINTS;
DROP TABLE <NM_ADMIN_USER>.REVINFO;
DROP SEQUENCE <NM_ADMIN_USER>.hibernate_sequence;
DROP TABLE <ZONE_ADMIN_USER>.CONFIGURATION_DEPLOYMENT CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.CONFIGURATION_METADATA CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.DATABASECHANGELOG CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.DATABASECHANGELOGLOCK CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.DATABASE_CONFIG CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.IDENTITY_MANAGER_CONFIG CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.KEY_STORE CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.LOCAL_SIGNER CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.MIGRATION CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.NETWORK_MAP_CONFIG CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.NETWORK_PARAMETERS_CONFIG CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.NOTARY_CONFIG CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.PACKAGE_OWNERSHIP_CONFIG CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.PARAMETERS_UPDATE_CONFIG CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.SHELL_CONFIG CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.SIGNER_CONFIG CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.SIGNER_CONFIG_HSM CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.SIGNER_CONFIG_KEY CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.SIGNER_CONFIG_LOCAL_KEYSTORE CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.SIGNER_CONFIG_TASK CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.SOCKET_CONFIG CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.SSL_CONFIG CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.SUBZONE CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.TRUST_STORE CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.WHITELIST_CONTRACTS_CONFIG CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.WHITELIST_CONTRACTS_CORDAPPS CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.WHITELIST_CONTRACTS_EXCLUDE CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.WHITELIST_CONTRACT_ATTACH_IDS CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.WHITELIST_CONTRACT_CONFIG CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.WORKFLOW_CONFIG CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.WORKFLOW_CONFIG_CRL_FILES CASCADE CONSTRAINTS;
DROP TABLE <ZONE_ADMIN_USER>.ZONE CASCADE CONSTRAINTS;
DROP SEQUENCE <ZONE_ADMIN_USER>.hibernate_sequence;
DROP TABLE <AUTH_ADMIN_USER>.AUDIT_EVENTS CASCADE CONSTRAINTS;
DROP TABLE <AUTH_ADMIN_USER>.AUDIT_EVENT_DATA CASCADE CONSTRAINTS;
DROP TABLE <AUTH_ADMIN_USER>.BASELINE_CHANGES CASCADE CONSTRAINTS;
DROP TABLE <AUTH_ADMIN_USER>.BASELINE_LOCK CASCADE CONSTRAINTS;
DROP TABLE <AUTH_ADMIN_USER>.DATABASECHANGELOG CASCADE CONSTRAINTS;
DROP TABLE <AUTH_ADMIN_USER>.DATABASECHANGELOGLOCK CASCADE CONSTRAINTS;
DROP TABLE <AUTH_ADMIN_USER>.GROUPS CASCADE CONSTRAINTS;
DROP TABLE <AUTH_ADMIN_USER>.GROUP_DATA CASCADE CONSTRAINTS;
DROP TABLE <AUTH_ADMIN_USER>.OAUTH_REFRESH_TOKENS CASCADE CONSTRAINTS;
DROP TABLE <AUTH_ADMIN_USER>.PERMISSIONS CASCADE CONSTRAINTS;
DROP TABLE <AUTH_ADMIN_USER>.PERMISSION_ASSIGNMENTS CASCADE CONSTRAINTS;
DROP TABLE <AUTH_ADMIN_USER>.ROLES CASCADE CONSTRAINTS;
DROP TABLE <AUTH_ADMIN_USER>.ROLE_ASSIGNMENTS CASCADE CONSTRAINTS;
DROP TABLE <AUTH_ADMIN_USER>.USERS CASCADE CONSTRAINTS;
DROP TABLE <AUTH_ADMIN_USER>.USERS_IN_GROUPS CASCADE CONSTRAINTS;
DROP TABLE <AUTH_ADMIN_USER>.USER_DATA CASCADE CONSTRAINTS;
DROP SEQUENCE <AUTH_ADMIN_USER>.SEQ_AUDIT_EVENTS;

To remove service tables, run the following SQL script:

DROP SCHEMA IF EXISTS "my_schema" CASCADE;

When upgrading a CENM service, any required database schema changes are applied by the services rather than by a standalone tool. As a best practice, R3 recommends that the services are configured with a database user without permission to make schema modifications, when running normally (this is the setup described above).

The typical service migration process is described below. Please read the release notes for any version-specific processes, especially when upgrading between major versions.

To migrate as service:

  1. Shut down the service.
  2. Back up the service database.
  3. Back up the service configuration.
  4. Update the JAR file.
  5. Edit the service configuration to:
  • Set the user to connect to the database as an account with schema migration permissions.
  • Set runMigration = true in the database configuration.
  1. Start the service.
  2. Wait for the service to start completely and ensure that it is healthy.
  3. Shut down the service.
  4. Restore the configuration backup from earlier. Alternatively, edit the configuration to connect to the database as a user without schema migration permissions, and to set runMigration = false in the database configuration.

Was this page helpful?

Thanks for your feedback!

Chat with us

Chat with us on our #docs channel on slack. You can also join a lot of other slack channels there and have access to 1-on-1 communication with members of the R3 team and the online community.

Propose documentation improvements directly

Help us to improve the docs by contributing directly. It's simple - just fork this repository and raise a PR of your own - R3's Technical Writers will review it and apply the relevant suggestions.

We're sorry this page wasn't helpful. Let us know how we can make it better!

Chat with us

Chat with us on our #docs channel on slack. You can also join a lot of other slack channels there and have access to 1-on-1 communication with members of the R3 team and the online community.

Create an issue

Create a new GitHub issue in this repository - submit technical feedback, draw attention to a potential documentation bug, or share ideas for improvement and general feedback.

Propose documentation improvements directly

Help us to improve the docs by contributing directly. It's simple - just fork this repository and raise a PR of your own - R3's Technical Writers will review it and apply the relevant suggestions.