Creating the Database

The following covers setting up your database and creating database indexes.

To maximize performance EJBCA runs with Optimistic Locking turned on, which sacrifices concurrent transactions to the same database row in place of shorter database roundtrips. If connecting multiple instances of EJBCA to the same database schema, ensure that these will not update the same end entities concurrently, or this will result in attempting to write data onto dirty table rows and thus failed transactions.

Database Setup

Create a database and database user according to the following examples for MariaDB/MySQL, Oracle XE, and PostgreSQL.

MariaDB/MySQL

In MariaDB, use the following commands to create the database, matching the DataSource in the next step, and add privileges to connect to the database:

$ mysql -u root -p
mysql> CREATE DATABASE ejbca CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
mysql> GRANT ALL PRIVILEGES ON ejbca.* TO 'ejbca'@'localhost' IDENTIFIED BY 'ejbca';

You should define secure passwords for your database in production. You can also improve security by limiting access to tables. For more information, see Database Privileges.

If you do not set utf8 as character set, then EJBCA may not start because of index limitations if using the default charset utf8mb4 in some configurations of MariaDB/MySQL. It will result in the following error: Specified key was too long; max key length is 767 bytes

Thus, you can use a command like the following instead:

CREATE DATABASE ejbca CHARACTER SET utf8 COLLATE utf8_unicode_ci;

In some configurations for InnoDB the binlog_format defaults to statement. Running EJBCA requires is to be set to row. For example: binlog_format=row

MySQL version 8 or later

As of MySQL version 8, users cannot be implicitly created using the GRANT command. If running MySQL version 8 or later, create the ejbca user first according to the following example:

mysql> CREATE USER 'ejbca'@'localhost' IDENTIFIED BY 'ejbca';
mysql> GRANT ALL PRIVILEGES ON ejbca.* TO 'ejbca'@'localhost';

Oracle DB

Connect to the Database

Connect to the Oracle database using sqlplus (or a similar tool) as sysdba.

# Depending on your setup, you would either use SID or ServiceName for connection URL.
# url:port:SID or url:port/ServiceName
 
# For XE version 11g for example, you can login to the database using following command.
sqlplus sys/<your password>@//localhost:1521/XE as sysdba

If you are unsure about the available services and SID in your installation, you can use the lsnrctl (Listener Control) commands "lsnrctl status" or "lsnrctl service" to get more information.

Create ejbca user

To create an ejbca user:

create user ejbca identified by ejbca;

Multi-tenant architecture was introduced as of Oracle Database 12c. If you are using Oracle 19c and having connection problems after creating the ejbca user, try the following:

# Use the following command to check connections and PDB's available:
show con_name;
show pdbs;
 
# Alternatively, create the user in container.
ALTER SESSION SET container = YOUR_CONTAINER_NAME;
create user ejbca identified by ejbca;

Grant permissions

# Permissions which must be granted to ejbca user:
 
GRANT create session TO ejbca;
GRANT create table TO ejbca;
GRANT create view TO ejbca;
GRANT create any trigger TO ejbca;
GRANT create any procedure TO ejbca;
GRANT create sequence TO ejbca;
GRANT create synonym TO ejbca;
 
GRANT RECOVERY_CATALOG_OWNER TO EJBCA WITH ADMIN OPTION;
GRANT GATHER_SYSTEM_STATISTICS TO EJBCA WITH ADMIN OPTION;
GRANT ADM_PARALLEL_EXECUTE_TASK TO EJBCA WITH ADMIN OPTION;
GRANT DBA TO EJBCA WITH ADMIN OPTION;
GRANT AQ_ADMINISTRATOR_ROLE TO EJBCA WITH ADMIN OPTION;
GRANT CONNECT TO EJBCA WITH ADMIN OPTION;
 
# Post Oracle 12c, DELETE_CATALOG_ROLE is deprecated. Please use for earlier versions.
GRANT DELETE_CATALOG_ROLE TO EJBCA WITH ADMIN OPTION;
 
GRANT OEM_MONITOR TO EJBCA WITH ADMIN OPTION;
GRANT HS_ADMIN_SELECT_ROLE TO EJBCA WITH ADMIN OPTION;
GRANT HS_ADMIN_EXECUTE_ROLE TO EJBCA WITH ADMIN OPTION;
GRANT LOGSTDBY_ADMINISTRATOR TO EJBCA WITH ADMIN OPTION;
GRANT EXECUTE_CATALOG_ROLE TO EJBCA WITH ADMIN OPTION;
GRANT SCHEDULER_ADMIN TO EJBCA WITH ADMIN OPTION;
GRANT IMP_FULL_DATABASE TO EJBCA WITH ADMIN OPTION;
GRANT EXP_FULL_DATABASE TO EJBCA WITH ADMIN OPTION;
GRANT DATAPUMP_IMP_FULL_DATABASE TO EJBCA WITH ADMIN OPTION;
GRANT HS_ADMIN_ROLE TO EJBCA WITH ADMIN OPTION;
GRANT RESOURCE TO EJBCA WITH ADMIN OPTION;
GRANT AQ_USER_ROLE TO EJBCA WITH ADMIN OPTION;
GRANT DATAPUMP_EXP_FULL_DATABASE TO EJBCA WITH ADMIN OPTION;
GRANT SELECT_CATALOG_ROLE TO EJBCA WITH ADMIN OPTION;
GRANT OEM_ADVISOR TO EJBCA WITH ADMIN OPTION;
GRANT DBFS_ROLE TO EJBCA WITH ADMIN OPTION;

Set up EJBCA

On the EJBCA side, in the database.properties file, set the following parameters:

database.name=oracle
 
# Use url:port:SID or url:port/ServiceName format mentioned above.
database.url=jdbc:oracle:thin:@oracledb:1521:XE
 
database.driver=oracle.jdbc.driver.OracleDriver
database.username=ejbca
database.password=ejbca

PostgreSQL

  1. After installing the PostgreSQL database, set the client authentication for local UNIX domain socket connections from peer to md5:

    $ sudo vi /etc/postgresql/10/main/pg_hba.conf
     
    # "local" is for Unix domain socket connections only
    local all all md5

    images/s/-2y7bau/8703/189cb2l/_/images/icons/emoticons/warning.svg If the pg_hba.conf file is not located under the path /etc/postgresql/10/main/pg_hba.conf, log in to the PostgreSQL CLI and use the following to find the location of the file:

    SHOW hba_file;

  2. Restart the PostgreSQL service:

    $ sudo systemctl restart postgresql

  3. Login to the user postgres and assign a password:

    $ sudo -i -u postgres
    $ createuser ejbca_user -P

  4. Create a new database and username:

    $ createdb ejbcadb -O ejbca_user
    $ logout

  5. Create EJBCA tables and indexes:

    $ psql -U ejbca_user -W ejbcadb
    > psql -hlocalhost -U ejbca_user -d ejbcadb < /opt/ejbca/doc/sql-scripts/create-tables-ejbca-postgres.sql
    > psql -hlocalhost -U ejbca_user -d ejbcadb < /opt/ejbca/doc/sql-scripts/create-index-ejbca.sql
    > \q

Create Database Indexes

As the database grows, it is important to have correct database indexes to maintain good performance.

The following SQL file that is ready to run on your database contains a set of recommended database indexes that can be applied to your database:

  • doc/sql-scripts/create-index-ejbca.sql.

If you are confident in what you are doing, you may also apply partitions and compression. The following SQL example file, provided for MariaDB / MySQL, should be analyzed for your specific workload and database maintenance processes and not applied blindly:

  • doc/sql-scripts/optimize-ejbca-mysql.sql

Applying partitions and compression may cause adverse effects if used incorrectly.

EJBCA will run well without this optimization and it can be ignored for most installations.

Next Step: Application Server Setup

For more information on configuring the application server, see Application Servers.