Database schema setup
Corda Enterprise supports the commercial 3rd party databases: Azure SQL, SQL Server, Oracle, and PostgreSQL. This document provides instructions describing how to create database schemas (user permissions, the Corda node’s tables, and other database objects), and how to configure Corda nodes to connect to a database with restricted permissions for production use. If you just need a quick database setup for testing/development, please refer to Simplified database schema setup for development.
Setting up a Corda node to connect to a database requires:
- Creating a database user with schema permissions
- Database schema creation with Corda Database Management Tool
- Corda node configuration changes
- Database configuration
1. Creating a database user with schema permissions
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
A database administrator must create a database user and a schema namespace with restricted permissions. This grants the user access to DML execution only (to manipulate data itself e.g. select/delete rows). This permission set is recommended for Corda nodes hot-cold-deployment and production environments. The less restricted permission set for a database user with administrative permissions is described in Simplified database schema setup for development (this is recommended for development purposes only).
Creating database users with schema permissions for:
Azure SQL
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
Two database users need to be created; the first one with administrative permissions to create schema objects, and the second with restrictive permissions for a Corda node. The schema objects are created by a separate user rather than a default database administrator. This ensures the correct schema namespace is used (the Corda Database Management Tool may not add a schema namespace prefix to each DDL statement).
Connect to the master database as an administrator (e.g. jdbc:sqlserver://<database_server>.database.windows.net:1433;databaseName=master;[…]). 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;
By default the password must contain characters from three of the following four sets:
- Uppercase letters
- Lowercase letters
- Digits
- Symbols
For example, C0rdaAP4ssword is a valid password. Passwords are delimited with single quotes. See the Azure SQL password policy for more information.
Use different passwords for my_admin_user and my_user.
Connect to a user database as the administrator (replace master with a user database in the connection string). Run the following script to create a schema and assign user permissions:
CREATE SCHEMA my_schema;
GO
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;
SQL Server
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
Two database users need to be created; the first with administrative permissions to create schema objects, the second with restrictive permissions for a Corda node. The schema objects are created by a separate user rather than a default database administrator. This ensures the correct schema namespace is used (the Corda Database Management Tool may not add a schema namespace prefix to each DDL statement).
Connect to a master database as an administrator (e.g. jdbc:sqlserver://
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;
By default the password must contain characters from three of the following four sets: uppercase letters, lowercase letters, digits, and symbols, e.g. C0rdaAP4ssword 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 Corda nodes within the same database (my_database), in which case run the first DDL statement (CREATE DATABASE my_database;) only once.
Connect to a user database as the administrator (replace master with my_database in the connection string). Run the following script to create a schema and assign user permissions:
CREATE SCHEMA my_schema;
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;
Oracle
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
The design of Oracle is that a schema is essentially a user account. So the user has full control over that schema. In order to restrict the permissions to the database, two users need to be created, one with administrative permissions (my_admin_user in the SQL script) and the other with 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 Corda node accesses the schema created by the administrator via a user with restricted permissions, allowing them to select/insert/delete data only. For Oracle databases, those permissions (SELECT, INSERT, UPDATE, DELETE) need to be granted explicitly for each table.
The tablespace size is unlimited, set the value (e.g. 100M, 1 GB) depending on your nodes sizing requirements. The script uses the default tablespace users with unlimited database space quota assigned to the user. Revise these settings depending on your nodes sizing requirements.
Run this script as 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 permissions for the Corda node user to access database objects will be assigned in the following step after the database objects are created.
The last permission for the v_$parameter view is needed when a database is running in Database Compatibility mode. If the permission is not granted then Corda Database Management Tool will output the message ‘Could not set check compatibility mode on OracleDatabase, assuming not running in any sort of compatibility mode …’ in a log file, the message can be ignored.
PostgreSQL
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
Two database users need to be created; the first one with administrative permissions to create schema objects, and the second with restrictive permissions for a Corda node. Connect to the database as an administrator and run the following script to create both users:
CREATE USER "my_admin_user" WITH LOGIN PASSWORD 'my_password';
CREATE USER "my_user" WITH LOGIN PASSWORD 'my_password';
GRANT "my_user" TO "my_admin_user";
CREATE SCHEMA "my_schema";
GRANT USAGE, CREATE ON SCHEMA "my_schema" TO "my_admin_user";
ALTER DEFAULT privileges IN SCHEMA "my_schema" GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON tables TO "my_admin_user";
ALTER DEFAULT privileges IN SCHEMA "my_schema" GRANT USAGE, SELECT ON sequences TO "my_admin_user";
ALTER ROLE "my_admin_user" SET search_path = "my_schema",public;
GRANT USAGE ON SCHEMA "my_schema" TO "my_user";
ALTER ROLE "my_user" SET search_path = "my_schema";
If you provide a custom schema name (different to the user name), then the last statement, setting the search_path, prevents querying the different default schema search path (default schema search path).
Connect to the database with the newly created user with the schema administrative permissions (“my_admin_user”) and run:
ALTER DEFAULT privileges IN SCHEMA "my_schema" GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON tables TO "my_user";
ALTER DEFAULT privileges IN SCHEMA "my_schema" GRANT USAGE, SELECT ON sequences TO "my_user";
2. Database schema creation
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
All data structures (tables, indexes) must be created before the Corda node connects to a database with restricted permissions. Corda is released without a separate set of DDL scripts, instead a database administrator needs to use the Corda Database Management Tool to output the DDL scripts and run the scripts against a database. Each Corda release version has the associated Corda Database Management Tool release which outputs a compatible set of DDL scripts. The DDL scripts contain the history of a database evolution - series of table alterations leading to the current state, using the functionality of Liquibase which is used by Corda for database schema management.
2.1. Create Liquibase management tables
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
The Corda Database Management Tool needs to connect to a running database instance in order to output DDL script. The presence of the DATABASECHANGELOG and DATABASECHANGELOGLOCK Liquibase management tables is required as well. The database administrator needs to create these tables manually. Replace the schema namespace my_schema with the schema used by the node.
DDL script for management tables for:
Azure SQL and SQL Server
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
DDL script to create Liquibase control tables for Azure SQL and SQL Server:
CREATE TABLE my_schema.DATABASECHANGELOG (
ID nvarchar(255) NOT NULL,
AUTHOR nvarchar(255) NOT NULL,
FILENAME nvarchar(255) NOT NULL,
DATEEXECUTED datetime2(3) NOT NULL,
ORDEREXECUTED int NOT NULL,
EXECTYPE nvarchar(10) NOT NULL,
MD5SUM nvarchar(35) NULL,
DESCRIPTION nvarchar(255) NULL,
COMMENTS nvarchar(255) NULL,
TAG nvarchar(255) NULL,
LIQUIBASE nvarchar(20) NULL,
CONTEXTS nvarchar(255) NULL,
LABELS nvarchar(255) NULL,
DEPLOYMENT_ID nvarchar(10) NULL);
CREATE TABLE my_schema.DATABASECHANGELOGLOCK (
ID int NOT NULL,
LOCKED bit NOT NULL,
LOCKGRANTED datetime2(3) NULL,
LOCKEDBY nvarchar(255) NULL,
CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));
Oracle
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
DDL script to create Liquibase control tables for Oracle, change the users tablespace if necessary:
CREATE TABLE my_admin_user."DATABASECHANGELOG" (
"ID" VARCHAR2(255) NOT NULL ENABLE,
"AUTHOR" VARCHAR2(255) NOT NULL ENABLE,
"FILENAME" VARCHAR2(255) NOT NULL ENABLE,
"DATEEXECUTED" TIMESTAMP (6) NOT NULL ENABLE,
"ORDEREXECUTED" NUMBER(*,0) NOT NULL ENABLE,
"EXECTYPE" VARCHAR2(10) NOT NULL ENABLE,
"MD5SUM" VARCHAR2(35),
"DESCRIPTION" VARCHAR2(255),
"COMMENTS" VARCHAR2(255),
"TAG" VARCHAR2(255),
"LIQUIBASE" VARCHAR2(20),
"CONTEXTS" VARCHAR2(255),
"LABELS" VARCHAR2(255),
"DEPLOYMENT_ID" VARCHAR2(10)) TABLESPACE users;
CREATE TABLE my_admin_user."DATABASECHANGELOGLOCK" (
"ID" NUMBER(*,0) NOT NULL ENABLE,
"LOCKED" NUMBER(1,0) NOT NULL ENABLE,
"LOCKGRANTED" TIMESTAMP (6),
"LOCKEDBY" VARCHAR2(255),
CONSTRAINT "PK_DATABASECHANGELOGLOCK" PRIMARY KEY ("ID")) TABLESPACE users;
PostgreSQL
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
DDL script to create Liquibase control tables and allow the restricted user (my_user) read only access to the databasechangelog table. Run the script as user with the schema administrative permissions (my_admin_user):
CREATE TABLE "my_schema".databasechangelog (
id varchar(255) NOT NULL,
author varchar(255) NOT NULL,
filename varchar(255) NOT NULL,
dateexecuted timestamp NOT NULL,
orderexecuted int4 NOT NULL,
exectype varchar(10) NOT NULL,
md5sum varchar(35) NULL,
description varchar(255) NULL,
comments varchar(255) NULL,
tag varchar(255) NULL,
liquibase varchar(20) NULL,
contexts varchar(255) NULL,
labels varchar(255) NULL,
deployment_id varchar(10) NULL);
CREATE TABLE "my_schema".databasechangeloglock (
id int4 NOT NULL,
locked bool NOT NULL,
lockgranted timestamp NULL,
lockedby varchar(255) NULL,
CONSTRAINT pk_databasechangeloglock PRIMARY KEY (id));
REVOKE INSERT, UPDATE, DELETE ON TABLE "my_schema".databasechangelog FROM my_user;
2.2. Configure the Database Management Tool
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
The Corda Database Management Tool needs access to a running database. The tool is configured in a similar way to the Corda node. A base directory needs to be provided with the following content:
- a
node.conf
file with database connection settings containing a database user with restricted permissions - a
drivers
directory to place JDBC driver - a
cordapps
directory with CorDapps requiring custom tables.
Copy CorDapps to the cordapps subdirectory, this is required to collect and extract DDL for any custom database migration scripts.
Create a node.conf
with properties for your database, and copy the respective driver into the drivers
directory.
The node.conf
templates for each database vendor are shown below:
Azure SQL
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
The required node.conf
settings for the Database Management Tool using Azure SQL:
myLegalName=<node's Distinguished Name>
dataSourceProperties = {
dataSourceClassName = "com.microsoft.sqlserver.jdbc.SQLServerDataSource"
dataSource.url = "jdbc:sqlserver://<database_server>.database.windows.net:1433;databaseName=<my_database>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"
dataSource.user = my_login
dataSource.password = "my_password"
}
database = {
schema = my_schema
}
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 both administrative and restrictive users.
The Microsoft SQL JDBC driver can be downloaded from Microsoft Download Center,
extract the archive and copy the single file mssql-jdbc-6.4.0.jre8.jar into the drivers
directory.
SQL Server
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
The required node.conf
settings for the Database Management Tool using SQL Server:
dataSourceProperties = {
dataSourceClassName = "com.microsoft.sqlserver.jdbc.SQLServerDataSource"
dataSource.url = "jdbc:sqlserver://<host>:<port>;databaseName=my_database"
dataSource.user = my_login
dataSource.password = "my_password"
}
database = {
schema = my_schema
}
Replace placeholders
The Microsoft JDBC 6.4 driver can be downloaded from Microsoft Download Center,
extract the archive and copy the single file mssql-jdbc-6.4.0.jre8.jar into the drivers
directory.
Oracle
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
First, connect to the database as administrator (any user which can create other users, my_admin_user has no such privileges)
and run the following DDL script to create my_user
, which you will use in node.conf
:
CREATE USER my_user identified by my_password;
GRANT CREATE SESSION TO my_user;
GRANT SELECT ON v_$parameter TO my_user;
Configure the required node.conf
settings for the Database Management Tool using Oracle as shown below:
dataSourceProperties = {
dataSourceClassName = "oracle.jdbc.pool.OracleDataSource"
dataSource.url = "jdbc:oracle:thin:@<host>:<port>:<sid>"
dataSource.user = my_admin_user
dataSource.password = "my_password"
}
database = {
schema = my_admin_user
}
Replace the placeholders
Copy the Oracle JDBC driver ojdbc6.jar for 11g RC2 or ojdbc8.jar for Oracle 12c into the drivers
directory.
PostgreSQL
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
The required node.conf
settings for the Database Management Tool using PostgreSQL:
dataSourceProperties = {
dataSourceClassName = "org.postgresql.ds.PGSimpleDataSource"
dataSource.url = "jdbc:postgresql://<host>:<port>/<database>"
dataSource.user = my_user
dataSource.password = "my_password"
}
database = {
schema = my_schema
}
Replace the placeholders database.schema
is the database schema name assigned to the user.
The value of database.schema
is automatically wrapped in double quotes to preserve case-sensitivity.
Copy PostgreSQL JDBC Driver 42.2.9 version JDBC 4.2 into the drivers
directory.
2.3. Extract the DDL script using Database Management Tool
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
To run the tool use the following command:
java -jar tools-database-manager-|release|.jar dry-run -b path_to_configuration_directory
The option -b
points to the base directory with a node.conf
file and drivers and cordapps subdirectories.
A script will be generated named migration/.sql* in the base directory. This script contains all the statements to create/modify data structures (e.g. tables/indexes) and inserts to the Liquibase management table DATABASECHANGELOG. The command doesn’t alter any tables. Refer to the Corda Database Management Tool manual for more detail.
2.4. Apply DDL scripts on a database
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
The generated DDL script can be applied by the database administrator using their tooling of choice.
The script needs to be executed by a database user with the administrative permissions, having the default schema set to the schema used by the Corda node. This is assured by connecting as a user with the administrative permissions created in the first step:
- SQL Server and Azure SQL - connect as the user with my_admin_login login
- Oracle and PostgreSQL - connect as my_admin_user
- PostgreSQL - connect as my_admin_user
You may connect to the database as any user with administrative permissions to the schema, as long as you set the default schema for the sessions pointing to the schema where tables need to be created e.g. for PostgreSQL run the following statement before executing the DDL script:
SET SCHEMA 'my_schema';
The reason is that not all SQL statements in the generated DDL script contain the schema prefix. This is not relevant to Oracle setups.
For Oracle databases the script may contain the command for SQL Plus, if you are using another tool comment out the first SQL statement:
SET DEFINE OFF;
2.5. Add permission to use tables
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
For some databases the specific permissions can be assigned only after the tables are created. This step is required for Oracle databases only.
Oracle
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
my_user
in Configure the Database Management Tool.Connect to the database as my_admin_user and run the following DDL script, the first SQL statement may fail depending on your Oracle database installation type:
GRANT SELECT ON my_admin_user.DATABASECHANGELOG TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE 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.NODE_ATTACHMENTS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_ATTACHMENTS_CONTRACTS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_ATTACHMENTS_SIGNERS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_CHECKPOINTS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_CHECKPOINT_BLOBS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_CORDAPP_METADATA TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_CORDAPP_SIGNERS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_FLOW_RESULTS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_FLOW_EXCEPTIONS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_FLOW_METADATA TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_CONTRACT_UPGRADES TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_HASH_TO_KEY TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_IDENTITIES TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_IDENTITIES_NO_CERT TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_INFOS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_INFO_HOSTS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_INFO_PARTY_CERT TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_LINK_NODEINFO_PARTY TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_MESSAGE_IDS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_METERING_COMMANDS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_METERING_CORDAPPS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_METERING_DATA TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_MUTUAL_EXCLUSION TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_NAMED_IDENTITIES TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_NETWORK_PARAMETERS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_OUR_KEY_PAIRS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_PROPERTIES TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_SCHEDULED_STATES TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_TRANSACTIONS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.PK_HASH_TO_EXT_ID_MAP TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_RPC_AUDIT_DATA TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.STATE_PARTY TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.VAULT_FUNGIBLE_STATES TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.VAULT_FUNGIBLE_STATES_PARTS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.VAULT_LINEAR_STATES TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.VAULT_LINEAR_STATES_PARTS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.VAULT_STATES TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.VAULT_TRANSACTION_NOTES TO my_user;
GRANT SELECT ON my_admin_user.V_PKEY_HASH_EX_ID_MAP TO my_user;
Additional table permissions are required for a notary node, some of them are optional:
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_NOTARY_REQUEST_LOG TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_NOTARY_COMMITTED_STATES TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_NOTARY_COMMITTED_TXS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_BFT_COMMITTED_STATES TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_RAFT_COMMITTED_STATES TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_BFT_COMMITTED_TXS TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.NODE_RAFT_COMMITTED_TXS TO my_user;
Grant SELECT, INSERT, UPDATE, DELETE permissions to my_user for all custom CorDapp tables, e.g. Corda Finance CorDapp requires permissions for two tables:
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.CONTRACT_CASH_STATES TO my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_admin_user.CP_STATES TO my_user;
3. Corda node configuration
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
The following updates are required to the filesystem of a node:
- The Corda node configuration file
node.conf
needs to contain JDBC connection properties in thedataSourceProperties
entry and other database properties in thedatabase
entry (passed to a node JPA persistence provider or schema creation/upgrade flag).
dataSourceProperties = {
...
dataSourceClassName = <JDBC Data Source class name>
dataSource.url = <JDBC database URL>
dataSource.user = <Database user>
dataSource.password = <Database password>
}
database = {
schema = <Database schema name>
}
- The restricted node database user has no permissions to alter a database schema, so the
run-migration-scripts
sub-command cannot be run. - The Corda distribution does not include any JDBC drivers with the exception of the H2 driver.
It is the responsibility of the node administrator or a developer to install the appropriate JDBC driver.
Corda will search for valid JDBC drivers under the
./drivers
subdirectory of the node base directory. Alternatively the path can be also specified by thejarDirs
option in the node configuration. ThejarDirs
property is a list of paths, separated by commas and wrapped in single quotes e.g.jarDirs = [ '/lib/jdbc/driver' ]
. - Corda uses Hikari Pool for creating connection pools.
To configure a connection pool, the following custom properties can be set in the
dataSourceProperties
section, e.g.:
dataSourceProperties = {
...
maximumPoolSize = 10
connectionTimeout = 50000
}
maximumPoolSize
cannot be less than enterpriseConfiguration.tuning.flowThreadPoolSize + enterpriseConfiguration.tuning.rpcThreadPoolSize + 2
. See sizing-and-performance for more details. Their defaults depend on the machine they are being run, but if the maximumPoolSize
a error will appear showing what is the minimum required.Configuration templates for each database vendor are shown below:
Azure SQL
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
Example node configuration file for Azure SQL:
dataSourceProperties = {
dataSourceClassName = "com.microsoft.sqlserver.jdbc.SQLServerDataSource"
dataSource.url = "jdbc:sqlserver://<database_server>.database.windows.net:1433;databaseName=<my_database>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"
dataSource.user = my_login
dataSource.password = "my_password"
}
database = {
schema = my_schema
}
Replace 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.
The Microsoft SQL JDBC driver can be downloaded from Microsoft Download Center, extract the archive and copy the single file mssql-jdbc-6.4.0.jre8.jar (the archive comes with two JARs). The Common Configuration Steps paragraph explains the correct location for the driver JAR in the node installation structure.
SQL Server
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
Example node configuration file for SQL Server:
dataSourceProperties = {
dataSourceClassName = "com.microsoft.sqlserver.jdbc.SQLServerDataSource"
dataSource.url = "jdbc:sqlserver://<host>:<port>;databaseName=my_database"
dataSource.user = my_login
dataSource.password = "my_password"
}
database = {
schema = my_schema
}
Replace placeholders
The database.schema
is the database schema name assigned to the user.
The Microsoft JDBC 6.4 driver can be downloaded from Microsoft Download Center,
extract the archive and copy the single file mssql-jdbc-6.4.0.jre8.jar
(the archive comes with two JARs).
The Common Configuration Steps section explains the correct location for the driver JAR in the node installation structure.
Ensure 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 a Corda node failing to start with message: Caused by: org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when ‘hibernate.dialect’ not set.
Oracle
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
Example node configuration file for Oracle:
dataSourceProperties = {
dataSourceClassName = "oracle.jdbc.pool.OracleDataSource"
dataSource.url = "jdbc:oracle:thin:@<host>:<port>:<sid>"
dataSource.user = my_user
dataSource.password = "my_password"
connectionInitSql="alter session set current_schema=my_admin_user"
}
database = {
schema = my_admin_user
}
Replace the placeholders database.schema
equal to the user name (my_user).
When connecting with a database user with restricted permissions, all queries need to be prefixed with the other schema name.
Set the database.schema
value to my_admin_user.
The Corda node doesn’t guarantee to prefix all SQL queries with the schema namespace.
The additional configuration entry connectionInitSql
sets the current schema to the admin user (my_user) on connection to the database.
Place the Oracle JDBC driver ojdbc6.jar for 11g RC2 or ojdbc8.jar for Oracle 12c in the node directory drivers
described in Common Configuration Steps.
Database schema name can be set in JDBC URL string e.g. currentSchema=my_schema.
A Corda node can also connect to an Oracle database using credentials stored in an Oracle Wallet, with the following changes:
Assuming you have an Oracle Wallet set up in /home/<user>/wallet
, create an entry for the database in your tnsnames.ora
, with the
relevant <host-address>
, <host-port>
, and <service-name>
. For example:
my_database =
(DEscriptTION =
(ADDRESS = (PROTOCOL = TCP)(host = <host-address>)(port = <host-port>))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <service-name>)
)
)
Create a sqlnet.ora
in the same directory with the configuration for the wallet. For example:
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /home/<user>/wallet)
)
)
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0
Then, add the database credentials to your wallet using the following command (see here for more information on setting up Oracle Wallet):
mkstore -wrl /home/<user>/wallet -createCredential my_database <db-username> <db-password>
You will be prompted for the wallet password in order to update the wallet.
Then modify the connection string in your node.conf
to reference your TNS name, and set the username and password to null
(they are
required fields).
dataSourceProperties = {
dataSourceClassName = "oracle.jdbc.pool.OracleDataSource"
dataSource.url = "jdbc:oracle:thin:/@my_database"
dataSource.user = null
dataSource.password = null
}
database = {
schema = my_schema
}
Finally, start up the node with the following system properties set to the location of your wallet and the location of your tnsnames.ora
:
java -Doracle.net.wallet_location=/home/<user>/wallet -Doracle.net.tns_admin=<absolute-path-to-tnsnames> -jar corda.jar
PostgreSQL
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
Example node configuration file for PostgreSQL:
dataSourceProperties = {
dataSourceClassName = "org.postgresql.ds.PGSimpleDataSource"
dataSource.url = "jdbc:postgresql://<host>:<port>/<database>"
dataSource.user = my_user
dataSource.password = "my_password"
}
database = {
schema = my_schema
}
Replace the placeholders database.schema
is the database schema name assigned to 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 behaviour differs from Corda Open Source Edition where the value is not wrapped in double quotes.
Place the PostgreSQL JDBC Driver 42.2.9 version JDBC 4.2 in the node directory drivers
described in Common Configuration Steps.
4. Database configuration
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
Additional vendor specific database configuration.
SQL Server
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
The database collation should be case insensitive, refer to Server Configuration documentation.
Oracle
![Click to copy the section link. heading-link-icon](/icons/chain-icon.png)
To allow VARCHAR2 and NVARCHAR2 column types to store more than 2000 characters, ensure the database instance is configured to use extended data types. For example, for Oracle 12.1 refer to MAX_STRING_SIZE.
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.