Skip to main content
Version: 2.82.x.x LTS

Database Preparing

Setting Up the Database​

nevisIDM requires a local or a remote SQL database. This chapter describes the initial steps to set up the database for nevisIDM. Which steps you have to perform depends on the type of database. It is highly recommended to place the database close to the application to avoid performance loss because of network latency. The IDM uses JPA/Hibernate technology with many @OneToMany relations to collect related data that trigges a lot of short-time interactions with the database. Therefore, the long network distance drastically degrades the performance of IDM.

Oracle DB​

To run nevisIDM with Oracle, you need a remote Oracle server installation. In addition, you need an Oracle client for the initial database setup.

Database Configuration​

Follow the next steps to set up the Oracle database for nevisIDM.

  1. Install Oracle and set up an empty instance and schema.
  2. Configure the National Language Support (NLS) settings of the new instance. Pay attention to the recommendations in Oracle NLS settings below.

Oracle NLS settings​

It is recommended using the UTF-8 charset.

NLS_LENGTH_SEMANTICS:

  • If the selected character set is UTF-8, we recommend setting NLS_LENGTH_SEMANTICS to "CHAR". Together with ISO Latin character sets, the default ("BYTE") is fine.
ALTER SYSTEM SET NLS_LENGTH_SEMANTICS = CHAR

NLS_SORT, NLS_COMP:

  • nevisIDM lets the database do the sorting of search results. The default sort order of Oracle is binary, meaning that sorting is case-sensitive, umlauts (äöü) are sorted in after Z, etc. If you want to change the default behavior to Swiss sorting (nls_sort) and filtering/comparing (nls_comp), change the server-side Oracle parameters like this (requires a database restart ).

    alter system set nls_sort=SWISS scope=spfile;
    alter system set nls_comp=LINGUISTIC scope=spfile;

    This solution affects the whole database instance and thus may be a problem if the same instance is used by other applications that want a different behavior. It is the customer's decision whether correct Swiss sorting is a policy and should be used. As with every database server parameter, applying this change might have a negative impact on performance of database queries and thus needs to be verified with performance tests

    Make sure that all indexes of the nevisIDM database are recreated if the NLS settings are changed in an existing nevisIDM setup.

Initial Database Setup​

Creating the table spaces​

The nevisIDM database needs two table spaces:

  • A table space for the data (default: DATA_IDM), and
  • A table space for the index (default: INDEX_IDM).

When make a decision on the size of table spaces, note that most tables are historized. In particular, the system will add history records for users and credentials for each login. For more information, see Database sizing below.

  1. Log in to the Oracle database as system user.

  2. Execute the steps described in the next code block to create the table spaces:

    -- Create table spaces
    CREATE TABLESPACE DATA_IDM DATAFILE SIZE 1000M AUTOEXTEND ON;
    CREATE TABLESPACE INDEX_IDM DATAFILE SIZE 500M AUTOEXTEND ON;

Creating the database users and roles​

The nevisIDM database needs two different database users:

  • A database owner (default: UIDM01) used by the nevisidmdb tool to perform migrations of the database schema, and
  • A database user (default: UIDM02) used by the nevisIDM application to access the database.

In addition to group database privileges, the nevisIDM database needs two different roles assigned to the corresponding users:

  • A role assigned to the database owner (default: RL_IDM_ADM)
  • A role assigned to the database user (default: RL_IDM_APPL)

  1. Log in to the Oracle database as system user.

  2. Execute the steps described in the following code block to create the required users and corresponding privileges:

    -- create database owner
    CREATE USER UIDM01 IDENTIFIED BY "REPLACE-WITH-PASSWORD"
     DEFAULT TABLESPACE DATA_IDM
     QUOTA UNLIMITED ON DATA_IDM
     QUOTA UNLIMITED ON INDEX_IDM;
    -- create role for database owner
    CREATE ROLE RL_IDM_ADM;

    GRANT CREATE SESSION TO RL_IDM_ADM;
    GRANT ALTER SESSION TO RL_IDM_ADM;
    GRANT CREATE TABLE TO RL_IDM_ADM;
    GRANT CREATE VIEW TO RL_IDM_ADM;
    GRANT CREATE SEQUENCE TO RL_IDM_ADM;
    GRANT CREATE MATERIALIZED VIEW TO RL_IDM_ADM;
    GRANT CREATE PROCEDURE TO RL_IDM_ADM;
    GRANT CREATE TRIGGER TO RL_IDM_ADM;
    GRANT QUERY REWRITE TO RL_IDM_ADM;

    GRANT RL_IDM_ADM TO UIDM01;

    -- create database user
    CREATE USER UIDM02 IDENTIFIED BY "REPLACE-WITH-PASSWORD"
     DEFAULT TABLESPACE DATA_IDM
     QUOTA 10M ON DATA_IDM;

    -- create role for database user
    CREATE ROLE RL_IDM_APPL;

    GRANT CREATE SESSION TO RL_IDM_APPL;
    GRANT ALTER SESSION TO RL_IDM_APPL;

    GRANT RL_IDM_APPL TO UIDM02;

Creating the logon trigger​

The nevisIDM database user needs a logon trigger to access the schema of the database owner.

  1. Log in to the Oracle database as system user.
  2. Execute the steps described in the following code block to create the logon trigger for the database user:
-- create logon trigger for database owner
CREATE OR REPLACE TRIGGER UIDM02.after_logon_trg AFTER LOGON ON UIDM02.SCHEMA
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = UIDM01';
END;
/

It is possible to choose custom table space, user and role names. However, standard names will simplify installation and support. It is recommended changing the default passwords of the database owner and user in the code snippet above (IDENTIFIED BY "REPLACE-WITH-PASSWORD"). For more information, see Database identifiers and passwords below.

Setup Oracle driver​

Starting with nevisIDM version 2.77, the Oracle driver (jar) is no longer bundled into the application for legal reasons. Therefore, provide it externally. Perform the following steps:

  • Head to the Oracle JDBC driver download page and download the appropriate driver. The driver file will be called something like ojdbc8.jar.
  • Create a lib folder (if it does not exist) under the nevisIDM instance folder (mkdir -p /var/opt/nevisidm/$INSTANCE/lib).
  • Copy the downloaded driver to the newly created lib folder (/var/opt/nevisidm/$INSTANCE/lib).
  • Copy the downloaded driver to the lib folder of nevisidmdb tool (/var/opt/nevisidmdb/lib).

MariaDB​

To run nevisIDM with MariaDB, you need a local or remote MariaDB server installation. In addition, a MariaDB client is required for the initial database setup.

Database Configuration​

Perform the following steps to configure the MariaDB.

  1. nevisIDM requires the MariaDB server to set certain configuration parameters. The MariaDB configuration file is usually located at:

    /etc/my.cnf

    Alternatively, you can create a new configuration file in the folder /etc/my.cnf.d/.

  2. Configure MariaDB to turn off autocommit. First, change the MySQL InnoDB default transaction isolation level from "repeatable read" to "read committed". This setting reduces the probability of locking issues on the MariaDB and thus increases the maximum concurrent load supported by nevisIDM. Additionally, enable the configuration log_bin_trust_function_creators. This is necessary because the nevisIDM database requires to create triggers and procedures. To implement all this, add the following lines to the section [mariadb] of the my.cnf configuration file:

    autocommit=0
    transaction-isolation = READ-COMMITTED
    log_bin_trust_function_creators = ON

    If you forget to disable autocommit, you will potentially run into the following error when applying DML statements:

    ERROR 1048 (23000): Column 'TRX_ID' cannot be null

    This error occurs because the nevisidm database contains history triggers that need information about the current transaction. When the autocommit mode is enabled, there is no transaction available.

    Be sure to use InnoDB engines, to have transactional features. InnoDB is the default engine since MariaDB 10.2. The isolation level "read committed" is the default transaction isolation level on Oracle databases.

  3. Now configure the MariaDB such that it uses case-insensitive table names. It is also recommended using the character set "utf8mb4", which supports 4 bytes characters.

    lower_case_table_names = 1
    character-set-server = utf8mb4

  4. If you use a local MariaDB, it is good practice to bind the MariaDB to the local host for better security. Therefore, add the following line to the section [mariadb] of the my.cnfconfiguration file:

    Do not add this line to the my.cnf configuration file if you use a remote MariaDB.

    bind-address = localhost

  5. If you use a remote MariaDB, modify the privileges of the "root" user to be able to access the database remotely. Log in to the MariaDB and execute the following commands:

    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '<mysql-root-pwd>' WITH GRANT OPTION;
    FLUSH PRIVILEGES;

  6. Restart the database after you finished updating my.cnf.

    The following code block shows the sample configuration file my.cnf:

    [mariadb]
    autocommit=0
    transaction-isolation = READ-COMMITTED
    log_bin_trust_function_creators = ON
    lower_case_table_names = 1
    character-set-server = utf8mb4

Initial Database Setup​

Creating the database​

  1. Log in to the MariaDB as root.

  2. Run the next command to create the database:

    CREATE DATABASE nevisidm character set UTF8mb4 collate utf8mb4_bin;

Creating the database users​

The nevisIDM database needs two different database users:

  • A database owner (default: UIDM01) used by the nevisidmdb tool to perform migrations of the database schema, and
  • A database user (default: UIDM02) used by the nevisIDM application to access the database.

  1. Log in to the MariaDB as root.

  2. Execute the steps described in the following code block to create the required users and corresponding privileges:

    -- create database owner
    CREATE USER UIDM01@localhost IDENTIFIED BY 'REPLACE-WITH-PASSWORD';

    -- grant required privileges to owner
    GRANT CREATE ON nevisidm.* TO UIDM01@localhost;
    GRANT ALTER ON nevisidm.* TO UIDM01@localhost;
    GRANT DROP ON nevisidm.* TO UIDM01@localhost;
    GRANT SELECT ON nevisidm.* TO UIDM01@localhost;
    GRANT INSERT ON nevisidm.* TO UIDM01@localhost;
    GRANT DELETE ON nevisidm.* TO UIDM01@localhost;
    GRANT INDEX ON nevisidm.* TO UIDM01@localhost;
    GRANT UPDATE ON nevisidm.* TO UIDM01@localhost;
    GRANT CREATE VIEW ON nevisidm.* TO UIDM01@localhost;
    GRANT GRANT OPTION ON nevisidm.* TO UIDM01@localhost;
    GRANT CREATE ROUTINE ON nevisidm.* TO UIDM01@localhost;
    GRANT EXECUTE ON nevisidm.* TO UIDM01@localhost;
    GRANT TRIGGER ON nevisidm.* TO UIDM01@localhost;
    GRANT ALTER ROUTINE ON nevisidm.* TO UIDM01@localhost;

    -- create database user
    CREATE USER UIDM02@localhost IDENTIFIED BY 'REPLACE-WITH-PASSWORD';

    -- grant privileges to database user
    GRANT SELECT ON nevisidm.* TO UIDM02@localhost;
    GRANT INSERT ON nevisidm.* TO UIDM02@localhost;
    GRANT DELETE ON nevisidm.* TO UIDM02@localhost;
    GRANT UPDATE ON nevisidm.* TO UIDM02@localhost;
    GRANT EXECUTE ON nevisidm.* TO UIDM02@localhost;

    FLUSH PRIVILEGES;

To be able to access a remote MariaDB, replace the localhost in the above code snippet. Replace it by % to allow access from all origins. If you want to restrict access further, replace it with a specific hostname or IP address (range). For more information, refer to the MariaDB documentation.

It is possible to choose custom database and user names. However, standard names will simplify installation and support. It is recommended changing the default passwords of the database owner and user in the code snippet above (IDENTIFIED BY 'REPLACE-WITH-PASSWORD'). For more information see Database identifiers and passwords below.

Azure Database for MariaDB​

To run nevisIDM with Azure Database for MariaDB (MariaDB as a service), you need an Azure subscription with a Azure Database for MariaDB. In addition, a MariaDB client is required for the initial database setup.

Database Configuration​

Perform the following steps to configure the Azure Database for MariaDB. Set the following server parameters:

  • tx_isolation=READ_COMMITTED (transaction isolation level)
  • log_bin_trust_function_creators=ON (binary logging)
  • character_set_server=UTF8MB4 (default server character set)
  • lower_case_table_names=1 (store and compare table names in lowercase)
  • autocommit=0 (Azure does not allow setting this parameter via the configuration GUI. Therefore, you need to pass the parameter via the connection URL.)

Initial Database Setup​

Creating the database users​

The nevisIDM database needs two different database users:

  • A database owner (default: UIDM01) used by the nevisidmdb tool to perform migrations of the database schema, and
  • A database user (default: UIDM02) used by the nevisIDM application to access the database.

  1. Log in to the Azure Database for MariaDB as root.

  2. Execute the steps described in the following code block to create the required users and corresponding privileges:

    -- create database owner
    CREATE USER UIDM01@'%' IDENTIFIED BY 'REPLACE-WITH-PASSWORD';

    -- grant required privileges to owner
    GRANT CREATE ON nevisidm.* TO UIDM01@'%';
    GRANT ALTER ON nevisidm.* TO UIDM01@'%';
    GRANT DROP ON nevisidm.* TO UIDM01@'%';
    GRANT SELECT ON nevisidm.* TO UIDM01@'%';
    GRANT INSERT ON nevisidm.* TO UIDM01@'%';
    GRANT DELETE ON nevisidm.* TO UIDM01@'%';
    GRANT INDEX ON nevisidm.* TO UIDM01@'%';
    GRANT UPDATE ON nevisidm.* TO UIDM01@'%';
    GRANT CREATE VIEW ON nevisidm.* TO UIDM01@'%';
    GRANT GRANT OPTION ON nevisidm.* TO UIDM01@'%';
    GRANT CREATE ROUTINE ON nevisidm.* TO UIDM01@'%';
    GRANT EXECUTE ON nevisidm.* TO UIDM01@'%';
    GRANT TRIGGER ON nevisidm.* TO UIDM01@'%';
    GRANT ALTER ROUTINE ON nevisidm.* TO UIDM01@'%';

    -- create database user
    CREATE USER UIDM02@'%' IDENTIFIED BY 'REPLACE-WITH-PASSWORD';

    -- grant privileges to database user
    GRANT SELECT ON nevisidm.* TO UIDM02@'%';
    GRANT INSERT ON nevisidm.* TO UIDM02@'%';
    GRANT DELETE ON nevisidm.* TO UIDM02@'%';
    GRANT UPDATE ON nevisidm.* TO UIDM02@'%';
    GRANT EXECUTE ON nevisidm.* TO UIDM02@'%';

    FLUSH PRIVILEGES;

The parameter % allows access to the database from the localhost and anywhere inside Azure, if there are no additional IP- or hostname-based access restrictions.

It is possible to choose custom database and user names. However, standard names will simplify installation and support. It is recommended changing the default passwords of the database owner and user in the code snippet above (IDENTIFIED BY 'REPLACE-WITH-PASSWORD'). For more information see Database Identifiers and Passwords below.

Setting Up the Initial Database Schema​

Execute the following steps to set up the initial database schema:

  1. Install the nevisidmdb package:

    rpm -U <nevisidmdb-RPM>

  2. The installation of the package will create a default configuration file located at /var/opt/nevisidmdb/conf/nevisidmdb.properties. Edit the configuration file such that its entries match the configuration names used in the chapters Initial Database Setup.

    1. For the Oracle database, the configuration file may look similar to this:

      database.connection.url=jdbc:oracle:thin:@//<hostname>:<port>/<service-name>
      database.owner.name=UIDM01
      database.owner.password=REPLACE-WITH-PASSWORD
      database.ts.index=INDEX_IDM
      database.ts.data=DATA_IDM
      database.role.appl=RL_IDM_APPL

    2. For a local MariaDB, the configuration file may look similar to this:

      database.connection.url=jdbc:mysql://localhost:3306/nevisidm
      database.owner.name=UIDM01
      database.owner.password=REPLACE-WITH-PASSWORD

    3. For a remote Azure Database for MariaDB, the configuration file may look similar to this:

      database.connection.url=jdbc:mysql://<database_server_name>.mariadb.database.azure.com:3306/nevisidm?autocommit=0
      database.owner.name=UIDM01
      database.owner.password=REPLACE-WITH-PASSWORD

  3. To set up the initial database schema and load the nevisIDM reference data, run the following command with the nevisidmdb tool:

    nevisidmdb migrate

  4. (Optional) If you want to check user passwords against a password dictionary with about 1.7 million of entries, you have to load the dictionary into the database with the following command:

    nevisidmdb loadpwddictionary

This command can take about 30 minutes, depending on the speed of your database.

Updating the Oracle database statistics for the newly created schemas at the end of this initialization process is recommended. We also suggest that you update database statistics periodically.

Database Sizing​

This chapter holds some coarse rules about how much disk space a nevisIDM database will use. Note that the effective size will strongly depend on your database, specific data characteristics and your use cases. The following numbers can be taken as a good starting point:

  • ca. 3 MB per 100 users for the table space
  • ca. 4 MB per 100 users for the index space
  • = total of ca. 7 MB per 100 users

These numbers were measured on an Oracle database with 100'000 users, with extensive history data.

We recommend giving at least 5GB of storage to the nevisIDM database. When defining the initial size, think about what the number of users administrated in nevisIDM will be in the coming years and how long history data should be kept in the nevisIDM database. The amount of history data will increase depending on the number of mutations (inserts, updates, deletes) per time. Consider using batch jobs to drop old history data.

Database Identifiers and Passwords​

It is recommended only using alphanumeric characters for identifiers (such as user names, database name, table space names, role names and similar) applied in the chapters "Initial Database Setup". The password of the database owner and user can, with limitations, contain special characters. Some of these limitations are introduced by Oracle or MariaDB and others by the nevisidmdb tool. The nevisidmdb tool allows (un-) quoted alphanumeric and _ - # $ @ % . ' characters.

Further restrictions may be imposed by the used database type. For more information on allowed characters for identifiers, refer to the corresponding documentation of the chosen database type.

Pre-loading other data​

As most systems need some data in the database at the start of using the system, it could be a cumbersome task to create that data by hand on the UI. It is possible to load data at system startup into the database from JSON files placed at the <instance directory>/conf/import/<data type> directory. For now, custom properties are supported. The format of the JSON file should be:

{
 "properti es": [
  {
   "name": "custom_property_name",
   // "type": "STRING", //cannot be set, it's always STRING for now
   "scope": "APP_GLOBAL", //APP_GLOBAL, UNIT_GLOBAL, PROFILE_GLOBAL, PROFILE_FOR_APP_GLOBAL, PROFILE_FOR_APP, ROLE_FOR_APP, USER_GLOBAL, EROLE_GLOBAL, CREDENTIAL_CERTIFICATE_GLOBAL, CREDENTIAL_GENERIC_GLOBAL, CREDENTIAL_MOBILESIGNATURE_GLOBAL, CREDENTIAL_SAMLFEDERATION_GLOBAL, CREDENTIAL_SECURITYQUESTIONS_GLOBAL
   "encrypted": false,
   "propagated": false,
   "description": "This is a custom property loaded from JSON", // optional
   "precedence": 9,
   "mandatoryOnGui": false, // optional
   "uniquenessScope": "ABSOLUTE", // ABSOLUTE, ABSOLUTE_USER, RELATIVE_UNIT; optional
   "maxLength": 128, // optional
   "regex": "\\w+[123, // optional
   "accessCreate": "READ_ONLY", //READ_ONLY, READ_WRITE, OFF
   "accessModify": "READ_WRITE", //READ_ONLY, READ_WRITE, OFF
   "applicationExtId": "99999166", // optional
   "clientExtId": "100" //optional
  }
 ]
}

If one imported file contains a property with propertyName which is already defined, import mechanism modifies the original property according to the new definitions.

Modification Issues

Only modify properties if your changes make restriction on values less strict (For example: less strict regex, longer max length). Do not modify the following field:

  • uniquenessScope
  • encrypted
  • scope
  • applicationExtId
  • clientExtId And don't remove values from the definition. :::