Version: 2.75.x.x LTS

Database Preparing

Oracle DB

If you have an Oracle DB, the DB can run on a separate server. You need to configure where that is and which schema nevisIDM should access. Furthermore, nevisIDM will create the tablespaces neither for the data nor for the index. 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.

Installation steps for Oracle

Follow these installation steps to set up a DB for nevisIDM:

Install Oracle and set up an empty instance and schema.
Install the nevisidmdb RPM package:
```
rpm -i <nevisidmdb-RPM>
```
Create a configuration instance in the package:
```
nevisidmdb handover {<param>=<value> }
```
The script will try to find your Oracle installation using the ORACLE_HOME environment variable. If this is not successful, the script expects the command sqlplus to be in the PATH. You can set the parameters manually. The following command will issue a list of them:
```
nevisidmdb inst create test help
```
Check the configuration of nevisidmdb:
```
nevisidmdb cfg
```
Newer versions (like Oracle 12.x) might require the configuration of a custom LD_LIBRARY_PATH so that nevisidmdb properly works. Refer to your Oracle InstantClient documentation for the right settings.

Create users and objects:

nevisidmdb createusers
nevisidmdb createobjs

(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.
This command can take about 30 minutes, depending on the speed of your Oracle database.
```
nevisidmdb loadpwddictionary
```
If you want to customize the default password dictionary provided with nevisIDM, you can add or remove entries from the file /opt/nevisidm/sql/oracle/nevisidm_refdata_pwd_dictionary.sql before executing the "loadpwddictionary" command. Alternatively, you can insert or remove dictionary entries by means of SQLplus directly. Make sure to use an unused PASSWORD_DICTIONARY_ID (primary key) and to provide a value all in lower-case characters. Note that he dictionary check will be executed in a case-insensitive way nevertheless. Example:
```
INSERT /* append */ INTO TIDMR_PASSWORD_DICTIONARY VALUES (1800000,'forbiddenpwd');
```

Go to the nevisIDM installation and check the DB connection string:

nevisidm config # shows some configuration files
vi <prefix>/nevisidm-prod.properties

We recommend updating the Oracle DBstatistics for the newly created schemas at the end of this initialization process. We also suggest that you update DB statistics periodically.
If you use an Oracle RAC database, configure the DB connection parameters in nevisidmdb configuration like this:
```
ORACLE_SID=<dbservice>
DBHOST=<host-vip>
DBPORT=<port>
```

Oracle NLS settings

NLS_CHARACTERSET

nevisIDM supports DB instances with various character sets, such as WE8ISO8859P1 (ISO Latin 1), WE8ISO8859P15 (ISO Latin 9), UTF8, etc.

NLS_LENGTH_SEMANTICS

If the selected character set is UTF8, we recommend setting NLS_LENGTH_SEMANTICS to "CHAR". Together with ISO Latin character sets, the default ("BYTE") is fine. Alternatively, this parameter can be set on the client side before executing the nevisIDM schema creation scripts, e.g.,

ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR

For convenience, the "nevisidmdb config" command supports this parameter too.

NLS_LANG

Note that the SQL scripts with the default reference data for nevisIDM are encoded with ISO8859-1. Therefore, you need to set the environment variable NLS_LANG accordingly before starting the SQL*Plus client program for execution of the reference data scripts:

export NLS_LANG='american_america.WE8ISO8859P1';

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 DB restart ):

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

This solution affects the whole DB 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 db-server parameter, applying this change might have a negative impact on performance of DB queries and thus needs to be verified with performance tests.

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

To run nevisIDM with MariaDB, you need a local or remote MariaDB installation. The next sections explains how to configure the MariaDB server and how to create a nevisidmdb instance.

MariaDB configuration

Perform the next steps to configure the MariaDB.

nevisIDM requires a 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/ .
Be sure to use InnoDB engines, to have transactional features available. InnoDB is the default engine since MariaDB 10.2.
As a next step, turn off autocommit. Additionally, it is recommended changing the MySQL InnoDB default transaction isolation level "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. Therefore, add the following line to the section [mariadb] of the my.cnf configuration file:
```
autocommit=0
transaction-isolation = READ-COMMITTED
```
The isolation level "read committed" is the default transaction isolation level on Oracle databases.
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
```
If you use a local MySQL DB, it is good practice to bind the MySQL to the local host for better security. Therefore, add the following line to the section [mariadb] of the my.cnf configuration file:
Do not add this line to the my.cnf configuration file if you use a remote MySQL DB.
```
bind-address = localhost
```
If you use a remote MySQL DB, modify the permissions of the "root" user to be able to access the DB remotely. Log in to the MySQL DB and execute the following commands:
```
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '<mysql-root-pwd>' WITH GRANT OPTION;
FLUSH PRIVILEGES;
```
To check if the nevisidmdb package correctly configured the MariaDB server, execute the following command:
nevisidmdb mysql check <mysql-root-pwd>

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

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

Sample /etc/my.cnf.d/idm.cnf

nevisidmdb setup

Execute the following steps to prepare the DB objects:

Install the nevisidmdb package:
```
rpm -i <nevisidmdb-RPM>
```

Create a configuration instance in the package with the parameter DB_TYPE=mysql:

nevisidmdb handover DB_TYPE=mysql {<param>=<value>}

nevisidmdb inst create <instance name> DB_TYPE=mysql {<param>=<value>}

Set the following parameters in the nevisidmdb configuration:

nevisidmdb <instance_name> config
DBHOST_MYSQL=<host> if you use local MySQL DB, set it to 'localhost'
DBPORT_MYSQL=<port>
DBOWNER_NAME_MYSQL=<db_owner_name>
DBOWNER_PASSWORD_MYSQL=<db_owner_password>
DBUSER_NAME_MYSQL=<db_user_name>
DBUSER_PASSWORD_MYSQL=<db_user_password>

Create the nevisIDM DB and insert the complete reference data. Note that the reference data includes 1.7 million rows for the password dictionary table. This means that the execution of this command can take a few minutes, depending on the speed of your MySQL database.
```
nevisidmdb mysql setup <mysql-root-pwd>
```
If you use a remote MariaDB, modify the permissions of the nevisIDM user to allow remote access of the DB:
```
nevisidmdb mysql remote_enable <root_password>
```
If you use a remote MariaDB and want to remove the nevisidm DB, you have to disable the remote access first. Execute the following command before you drop the DB:
```
nevisidmdb mysql remote_disable <root_password>
```
The DB system password is taken from the provided argument. DB user/owner passwords are taken from the file nevisidm-prod.properties. This password is autogenerated during instance creation if not provided.

Tips and tricks for MariaDB

autocommit mode

As described above, the autocommit mode of MariaDB should be turned off by configuring the autocommit parameter:

autocommit=0

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

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

The reason for this error is that the nevisidm database contains history triggers that need information about the current transaction. If autocommit mode is enabled, there is no transaction available.

DB 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.

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.*DB password

You can set the password of the DB owner and user by setting the parameters "DBOWNER_PASSWORD" or "DBOWNER_PASSWORD_MYSQL" and "DBUSER_PASSWORD" or "DBUSER_PASSWORD_MYSQL" in the nevisidmdb config. These passwords can, with limitations, contain special characters. Some of these limitations are introduced by Oracle and others by the nevisidmdb admin script.

Here is the overall list of characters that are not supported:

@ ! $ & / ' " ( ) \ | ~ `` < > ;

Best effort 1-phase commit with database as inner transaction

Before the system sends the JMS message about the provisioning event, the database operation must be finished successfully (that is, the database commit had to be successful). In case of any error, no JMS message should be sent and the database transaction must be rolled back. Also if there is an error when sending the JMS message, both the JMS and the database transactions have to be rolled back.

In nevisIDM, the database and the JMS transaction commits and rollbacks are orchestrated. In case of any database error, the JMS message will not be sent and both operations will be rolled back. The same is true when sending the JMS message and an error arises. In this case, no data will be saved to the database and no message will be sent.

But there is one limitation: If there is an error during the commit of the JMS transaction (for example, the JMS queue is unavailable at the time of commit), then the database transaction will be committed but the JMS message will not be sent. For this to happen, the JMS queue has to go down after the JMS sending operation and before the JMS transaction commit. This is very rare.

Database Preparing

Oracle DB​

Installation steps for Oracle​

Oracle NLS settings​

NLS_CHARACTERSET​

NLS_LENGTH_SEMANTICS​

NLS_LANG​

MariaDB configuration​

nevisidmdb setup​

Tips and tricks for MariaDB​

autocommit mode​

DB sizing​

Best effort 1-phase commit with database as inner transaction​