5.11 Configuring the Databases Using the SQL Scripts

During the installation process, you might have specified Generate SQL for later to configure the databases or schema after the installation. Your database administrator needs to run the SQL scripts that the installation created to populate the databases. For PostgreSQL, the administrator also needs to create the roles for the Identity Governance databases. For Microsoft SQL, the administrator also needs to create the logins, users, and roles for the Identity Governance databases. If you select Configure Database Now during the installation, you can skip this section.

Identity Governance, Identity Reporting, and Workflow Engine need the following SQL scripts, located by default in:

  • Linux: /opt/netiq/idm/apps/idgov/sql, /opt/netiq/idm/apps/idrpt/sql, and /opt/netiq/idm/apps/wfe/sql

  • Windows: c:\netiq\idm\apps\idgov\sql, c:\netiq\idm\apps\idrpt\sql, and c:\netiq\idm\apps\wfe\sql

These are files for the specific database or schema:

  • ops-init.sql for the igops database or schema

  • arc-init.sql for the igarc database or schema

  • dcs-init.sql for the igdcs database or schema

  • wf-init.sql for the igwf database or schema

  • ara-init.sql for the igara database or schema

  • rpt-init-01-idm_rpt_cfg.sql for the igrpt database or schema

  • wfe-00-workflow.sql for the wfe database or schema

The installation program uses an additional file in the reporting SQL directory, create_rpt_roles_and_schemas.sql, to initialize the reporting database. It remains so the database administrator can see how the installer would modify the reporting database.

To configure the Identity Governance and Identity Reporting databases, see the following sections:

5.11.1 Configuring the PostgreSQL Databases for Identity Governance

The database administrator must create the appropriate roles in the database for Identity Governance. The database administrator or database owners must run the SQL scripts that the installation program generated. It is best practice to have the database administrator review the SQL scripts. Also, you must populate the global configuration values in the database.

NOTE:You must create the roles with the igops, igdcs, igwf, igara, and igarc database passwords rather than the database administrator password.

Ensure that the scripts are located on the database server. If you cannot access the SQL scripts, see Section 12.2, Manually Generating the Database Schema after the Installation.

  1. To populate the user schema in the database, have the database administrator run commands similar to the following:

    CREATE ROLE operations_db_name LOGIN password 'password';
    CREATE ROLE archive_db_name LOGIN password 'password';
    CREATE ROLE data_collection_db_name LOGIN password 'password';
    CREATE ROLE workflow_db_name LOGIN password 'password';
    CREATE ROLE analytics_db_name LOGIN password 'password';
    CREATE ROLE ig_report_role NOLOGIN;
    CREATE DATABASE igops WITH OWNER = operations_db_name ENCODING = 'UTF8';
    CREATE DATABASE igarc WITH OWNER = archive_db_name ENCODING = 'UTF8';
    CREATE DATABASE igdcs WITH OWNER = data_collection_db_name ENCODING = 'UTF8';
    CREATE DATABASE igwf WITH OWNER = workflow_db_name ENCODING = 'UTF8';
    CREATE DATABASE igara WITH OWNER = analytics_db_name ENCODING = 'UTF8';

    For example:

    CREATE ROLE igops LOGIN PASSWORD 'netiq';
    CREATE ROLE igarc LOGIN PASSWORD 'netiq';
    CREATE ROLE igdcs LOGIN PASSWORD 'netiq';
    CREATE ROLE igwf LOGIN PASSWORD 'netiq';
    CREATE ROLE igara LOGIN PASSWORD 'netiq';
    CREATE ROLE ig_report_role NOLOGIN;
    
    CREATE DATABASE igops WITH OWNER = igops ENCODING = 'UTF8';
    CREATE DATABASE igarc WITH OWNER = igarc ENCODING = 'UTF8';
    CREATE DATABASE igdcs WITH OWNER = igdcs ENCODING = 'UTF8';
    CREATE DATABASE igwf WITH OWNER = igwf ENCODING = 'UTF8';
    CREATE DATABASE igara WITH OWNER = igara ENCODING = 'UTF8';
  2. Have the database administrator run the SQL scripts to create and configure the Identity Governance databases. These are located by default in the following directories:

    • Linux: /opt/netiq/idm/apps/idgov/sql

    • Windows: c:\netiq\idm\apps\idgov\sql

  3. Have the database administrator run the scripts in the order listed. For example, if you have the PostgreSQL utility and psql installed at /usr/lib/postgresql/bin/psql use the following commands:

    /usr/lib/postgresql/bin/psql -h localhost -p 5432 -U igops igops -f /tmp/sql/ops-init.sql
    /usr/lib/postgresql/bin/psql -h localhost -p 5432 -U igarc igarc -f /tmp/sql/arc-init.sql
    /usr/lib/postgresql/bin/psql -h localhost -p 5432 -U igdcs igdcs -f /tmp/sql/dcs-init.sql
    /usr/lib/postgresql/bin/psql -h localhost -p 5432 -U igwf  igwf  -f /tmp/sql/wf-init.sql
    /usr/lib/postgresql/bin/psql -h localhost -p 5432 -U igara igara -f /tmp/sql/ara-init.sql
  4. (Optional) If you do not want to connect to the Operations database, use the -useDb false command when running the script. If you use this option when running the script, the command omits the following options from the script:

      -dbDriver org.postgresql.Driver
      -dbUser igops
      -dbPassword password
      -dbUrl jdbc:postgresql://%db_server%:%db_port%/igops

    When you connect to the Operations database, the script automatically loads the properties in the database as GLOBAL properties.

  5. (Optional) To use non-default settings, and to change the owner and the database name use these commands:

    "/opt/netiq/idm/apps/jre/bin/java" -Djava.util.logging.config.file="/opt/netiq/idm/apps/idgov/conf/logging.properties"  -Dcom.netiq.ism.config="/opt/netiq/idm/apps/idgov/conf/unused.props" -classpath "/opt/netiq/idm/apps/idgov/lib/ig-configutil.jar":"/opt/netiq/idm/apps/idgov/lib/postgresql-42.1.4.jar" com.netiq.iac.config.util.IacConfigUtil -dbDriver org.postgresql.Driver -dbUser igops -dbPassword %igops-password% -dbUrl "jdbc:postgresql://%server%:%port%/igops" -script "/opt/netiq/idm/apps/idgov/scripts/all-import-configs.script"

    For example:

    "/opt/netiq/idm/apps/jre/bin/java" -Djava.util.logging.config.file="/opt/netiq/idm/apps/idgov/conf/logging.properties"  -Dcom.netiq.ism.config="/opt/netiq/idm/apps/idgov/conf/unused.props" -classpath "/opt/netiq/idm/apps/idgov/lib/ig-configutil.jar":"/opt/netiq/idm/apps/idgov/lib/postgresql-42.1.4.jar" com.netiq.iac.config.util.IacConfigUtil -dbDriver org.postgresql.Driver -dbUser igops -dbPassword netiq -dbUrl "jdbc:postgresql://localhost:5432/igops" -script "/opt/netiq/idm/apps/idgov/scripts/all-import-configs.script"
  6. To populate the global configuration values in the database, enter the following command:

    "/opt/netiq/idm/apps/jre/bin/java" -Djava.util.logging.config.file="/opt/netiq/idm/apps/idgov/conf/logging.properties" -Djava.security.egd=file:///dev/urandom -Dcom.netiq.ism.config="/opt/netiq/idm/apps/idgov/conf/unused.props" -classpath "/opt/netiq/idm/apps/idgov/lib/ig-configutil.jar":"/opt/netiq/idm/apps/idgov/lib/postgresql-42.2.6.jar" com.netiq.iac.config.util.IacConfigUtil -dbDriver org.postgresql.Driver -dbUser %igops-user% -dbPassword %password% -dbUrl "jdbc:postgresql://%postgresql-server%:%port%/%igops-db%" -script "/opt/netiq/idm/apps/idgov/scripts/all-import-configs.script"

5.11.2 Configuring the Oracle Database for Identity Governance

Your database administrator must run the SQL scripts to create the tables and views. Also, you must populate the global configuration values in the database.

Ensure that the scripts are located on the database server. If you cannot access the SQL scripts, see Section 12.2, Manually Generating the Database Schema after the Installation.

  1. (Conditional) If you chose to generate SQL scripts, complete the following steps:

    1. Locate the scripts for each schema to create the tables and views.

      The scripts are located by default in the following default directory:

      • Linux: /opt/netiq/idm/apps/idgov/sql

      • Windows: c:\netiq\idm\app\idgov\sql

    2. Have the database administrator run the scripts in the order listed. For example, if you have the Oracle sqlplus is on the $PATH at /home/oracle/app/oracle/product/12.1.0/db_rpt_1/bin/sqlplus use the following commands:

      sqlplus -L igops/"password"@<server>:1521/pdborcl @ /tmp/sql/ops-init.sql
      sqlplus -L igarc/"password"@<server>:1521/pdborcl @ /tmp/sql/arc-init.sql
      sqlplus -L igdcs/"password"@<server>:1521/pdborcl @ /tmp/sql/dcs-init.sql
      sqlplus -L igwf/"password"@<server>:1521/pdborcl @ /tmp/sql/wf-init.sql
      sqlplus -L igara/"password"@<server>:1521/pdborcl @ /tmp/sql/ara-init.sql
  2. To populate the global configuration values in the database, enter the following command:

    "/opt/netiq/idm/apps/jre/bin/java" -Djava.util.logging.config.file="/opt/netiq/idm/apps/idgov/conf/logging.properties" -Djava.security.egd=file:///dev/urandom -Dcom.netiq.ism.config="/opt/netiq/idm/apps/idgov/conf/unused.props" -classpath "/opt/netiq/idm/apps/idgov/lib/ig-configutil.jar":"/opt/netiq/idm/apps/idgov/lib/ojdbc.jar" com.netiq.iac.config.util.IacConfigUtil -dbDriver oracle.jdbc.OracleDriver -dbUser %igops-user% -dbPassword %password% -dbUrl "jdbc:oracle:thin:@%oracle-server%:%port%/%sid%" -script "/opt/netiq/idm/apps/idgov/scripts/all-import-configs.script"

    NOTE:This commands contains the default installation path of /opt/netiq/idm/apps.

    For example:

    "/opt/netiq/idm/apps/jre/bin/java" -Djava.util.logging.config.file="/opt/netiq/idm/apps/idgov/conf/logging.properties" -Djava.security.egd=file:///dev/urandom -Dcom.netiq.ism.config="/opt/netiq/idm/apps/idgov/conf/unused.props" -classpath "/opt/netiq/idm/apps/idgov/lib/ig-configutil.jar":"/opt/netiq/idm/apps/idgov/lib/ojdbc.jar" com.netiq.iac.config.util.IacConfigUtil -dbDriver oracle.jdbc.OracleDriver -dbUser igops -dbPassword netiq -dbUrl "jdbc:oracle:thin:@myoracle.mycompany.com:1521/mysid" -script "/opt/netiq/idm/apps/idgov/scripts/all-import-configs.script"

5.11.3 Configuring the Microsoft SQL Databases for Identity Governance

The database administrator must create the appropriate logins, users, and roles in the database for Identity Governance. The database administrator or database owners must run the SQL scripts that the installation program generated. It is best practice to have the database administrator review the SQL scripts. Also, you must populate the global configuration values in the database.

Ensure that the scripts are located on the database server. If you cannot access the SQL scripts, see Section 12.2, Manually Generating the Database Schema after the Installation.

NOTE:You must create the roles with the igops, igarc, igdcs, igwf, and igara database passwords rather than the database administrator password.

  1. Create the appropriate logins, users, and roles in the database.

  2. Have the database administrator run the SQL scripts to create and configure the Identity Governance databases. These are located by default in the following directories:

    • Linux: /opt/netiq/idm/apps/idgov/sql

    • Windows: c:\netiq\idm\apps\idgov\sql

  3. Have the database administrator run the scripts in the order listed. For example, if sqlcmd is on the %PATH% use the following commands:

    sqlcmd -S <server IP or DNS>,1433 -U igops -d igops -P "password" -i %TEMP%\sql\ops-init.sql
    sqlcmd -S <server IP or DNS>,1433 -U igarc -d igarc -P "password" -i %TEMP%\sql\arc-init.sql
    sqlcmd -S <server IP or DNS>,1433 -U igdcs -d igdcs -P "password" -i %TEMP%\sql\dcs-init.sql
    sqlcmd -S <server IP or DNS>,1433 -U igwf -d igwf -P "password" -i %TEMP%\sql\wf-init.sql
    sqlcmd -S <server IP or DNS>,1433 -U igara -d igara -P "password" -i %TEMP%\sql\ara-init.sql
  4. To populate the global configuration values in the database, enter the following command:

    "/opt/netiq/idm/apps/jre/bin/java" -Djava.util.logging.config.file="/opt/netiq/idm/apps/idgov/conf/logging.properties"  -Dcom.netiq.ism.config="/opt/netiq/idm/apps/idgov/conf/unused.props" -classpath "/opt/netiq/idm/apps/idgov/lib/ig-configutil.jar":"/opt/netiq/idm/apps/idgov/lib/msjdbc.jar" com.netiq.iac.config.util.IacConfigUtil -dbDriver com.microsoft.sqlserver.jdbc.SQLServerDriver -dbUser igops -dbPassword %igops-password% -dbUrl "jdbc:sqlserver://%server%:%port%;databaseName=igops" -script "/opt/netiq/idm/apps/idgov/scripts/all-import-configs.script"

    For example:

    "/opt/netiq/idm/apps/jre/bin/java" -Djava.util.logging.config.file="/opt/netiq/idm/apps/idgov/conf/logging.properties"  -Dcom.netiq.ism.config="/opt/netiq/idm/apps/idgov/conf/unused.props" -classpath "/opt/netiq/idm/apps/idgov/lib/ig-configutil.jar":"/opt/netiq/idm/apps/idgov/lib/msjdbc.jar" com.netiq.iac.config.util.IacConfigUtil -dbDriver com.microsoft.sqlserver.jdbc.SQLServerDriver -dbUser igops -dbPassword netiq -dbUrl "jdbc:sqlserver://mysever.netiq.com:1433;databaseName=igops" -script "/opt/netiq/idm/apps/idgov/scripts/all-import-configs.script"

5.11.4 Configuring the Identity Reporting Database

If you select Generate SQL for later during installation, have the database administrator run the SQL script to configure the Identity Reporting database, then configure global configuration values. The script is located by default in the following directory:

  • Linux: /opt/netiq/idm/apps/idrpt/sql

  • Windows: c:\netiq\idm\apps\idrpt\sql

If you cannot access the SQL scripts, see Section 12.2, Manually Generating the Database Schema after the Installation. Ensure that the script is located on the database server.

  1. Generate the Identity Reporting database. The following is a list of example commands to run on the different databases to generate the Identity Reporting database.

    PostgreSQL

    For example, if you have the PostgreSQL utility and psql installed at /usr/lib/postgresql/bin/psql use the following command:

    /usr/lib/postgresql/bin/psql -h localhost -p 5432 -U idm_rpt_cfg igrpt -f /tmp/sql/rpt-init-01-idm_rpt_cfg.sql

    Oracle

    sqlplus -L idm_rpt_cfg/"password"@<server>:1521/pdborcl @ /tmp/sql/rpt-init-01-idm_rpt_cfg.sql

    Microsoft SQL Serve For example, if sqlcmd is on the %PATH% use the following command:

    sqlcmd -S <server IP or DNS>,1433 -U idm_rpt_cfg -d igrpt -P "password" -i %TEMP%\sql\rpt-init-01-idm_rpt_cfg.sql
  2. To populate the global configuration values in the database, enter the following command:

    PostgreSQL

    /opt/netiq/idm/apps/jre/bin/java" -Djava.util.logging.config.file="/opt/netiq/idm/apps/idrpt/conf/logging.properties" -Djava.security.egd=file:///dev/urandom -Dcom.netiq.ism.config="/opt/netiq/idm/apps/idrpt/conf/unused.props" -classpath "/opt/netiq/idm/apps/idrpt/lib/ig-configutil.jar":"/opt/netiq/idm/apps/idrpt/lib/postgresql-42.2.6.jar" com.netiq.iac.config.util.IacConfigUtil -dbDriver org.postgresql.Driver -dbUser %igops-user% -dbPassword %password% -dbUrl "jdbc:postgresql://%postgresql-server%:%port%/%igops-db%" -script "/opt/netiq/idm/apps/idrpt/scripts/all-import-configs.script

    For example:

    /opt/netiq/idm/apps/jre/bin/java" -Djava.util.logging.config.file="/opt/netiq/idm/apps/idrpt/conf/logging.properties" -Djava.security.egd=file:///dev/urandom -Dcom.netiq.ism.config="/opt/netiq/idm/apps/idrpt/conf/unused.props" -classpath "/opt/netiq/idm/apps/idrpt/lib/ig-configutil.jar":"/opt/netiq/idm/apps/idrpt/lib/postgresql-42.2.6.jar" com.netiq.iac.config.util.IacConfigUtil -dbDriver org.postgresql.Driver -dbUser igops -dbPassword netiq -dbUrl "jdbc:postgresql://myserver.netiq.com:5432/igops" -script "/opt/netiq/idm/apps/idrpt/scripts/all-import-configs.script

    Oracle

    /opt/netiq/idm/apps/jre/bin/java" -Djava.util.logging.config.file="/opt/netiq/idm/apps/idrpt/conf/logging.properties" -Djava.security.egd=file:///dev/urandom -Dcom.netiq.ism.config="/opt/netiq/idm/apps/idrpt/conf/unused.props" -classpath "/opt/netiq/idm/apps/idrpt/lib/ig-configutil.jar":"/opt/netiq/idm/apps/idrpt/lib/ojdbc.jar" com.netiq.iac.config.util.IacConfigUtil -dbDriver oracle.jdbc.OracleDriver -dbUser %igops-user% -dbPassword %password% -dbUrl "jdbc:oracle:thin:@%oracle-server%:%port%/%sid%" -script "/opt/netiq/idm/apps/idrpt/scripts/all-import-configs.script

    For example:

    /opt/netiq/idm/apps/jre/bin/java" -Djava.util.logging.config.file="/opt/netiq/idm/apps/idrpt/conf/logging.properties" -Djava.security.egd=file:///dev/urandom -Dcom.netiq.ism.config="/opt/netiq/idm/apps/idrpt/conf/unused.props" -classpath "/opt/netiq/idm/apps/idrpt/lib/ig-configutil.jar":"/opt/netiq/idm/apps/idrpt/lib/ojdbc.jar" com.netiq.iac.config.util.IacConfigUtil -dbDriver oracle.jdbc.OracleDriver -dbUser igops -dbPassword netiq -dbUrl "jdbc:oracle:thin:@myoracle.mycompany.com:1521/mysid" -script "/opt/netiq/idm/apps/idrpt/scripts/all-import-configs.script

    Microsoft SQL Server

    /opt/netiq/idm/apps/jre/bin/java" -Djava.util.logging.config.file="/opt/netiq/idm/apps/idrpt/conf/logging.properties"  -Dcom.netiq.ism.config="/opt/netiq/idm/apps/idrpt/conf/unused.props" -classpath "/opt/netiq/idm/apps/idrpt/lib/ig-configutil.jar":"/opt/netiq/idm/apps/idrpt/lib/msjdbc.jar" com.netiq.iac.config.util.IacConfigUtil -dbDriver com.microsoft.sqlserver.jdbc.SQLServerDriver -dbUser igops -dbPassword %igops-password% -dbUrl "jdbc:sqlserver://%server%:%port%;databaseName=igops" -script "/opt/netiq/idm/apps/idrpt/scripts/all-import-configs.scrip

    For example:

    /opt/netiq/idm/apps/jre/bin/java" -Djava.util.logging.config.file="/opt/netiq/idm/apps/idrpt/conf/logging.properties"  -Dcom.netiq.ism.config="/opt/netiq/idm/apps/idrpt/conf/unused.props" -classpath "/opt/netiq/idm/apps/idrpt/lib/ig-configutil.jar":"/opt/netiq/idm/apps/idrpt/lib/msjdbc.jar" com.netiq.iac.config.util.IacConfigUtil -dbDriver com.microsoft.sqlserver.jdbc.SQLServerDriver -dbUser igops -dbPassword netiq -dbUrl "jdbc:sqlserver://myserver.netiq.com:1433;databaseName=igops" -script "/opt/netiq/idm/apps/idrpt/scripts/all-import-configs.script

5.11.5 Configuring the Workflow Engine Database

If you select Generate SQL for later during the installation, have the database administrator run the SQL script to configure the Workflow Engine database. The script is located by default in the following directory:

  • Linux: /opt/netiq/idm/apps/wfe/sql

  • Windows: c:\netiq\idm\apps\wfe\sql

If you cannot access the SQL scripts, see Section 12.2, Manually Generating the Database Schema after the Installation.

Ensure that the script is located in the database server. The examples listed below are the commands that the Database Administrator can run on specific databases to generate the Workflow Engine database.

PostgreSQL

For example, if you have the PostgreSQL utility and psql installed at /usr/lib/postgresql/bin/psql use the following command:

/usr/lib/postgresql/bin/psql -h localhost -p 5432 -U igawfadmin igaworkflowdb -f /tmp/sql/wfe-00-workflow.sql
Oracle

For example, if you have the Oracle sqlplus on the $PATH at /home/oracle/app/oracle/product/12.1.0/db_wfe_1/bin/sqlplus use the following command:

sqlplus -L igawfadmin /"password"@<server>:1521/pdborcl @ /tmp/sql/wfe-00-workflow.sql
Microsoft SQL Server

For example, if sqlcmd is on the %PATH% use the following command:

sqlcmd -S <server IP or DNS>,1433 -U igawfadmin -d igaworkflowdb -P "password" -f 65001 -i %TEMP%\sql\wfe-00-workflow.sql