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:
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.
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';
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
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
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.0.jar" com.netiq.iac.config.util.IacConfigUtil -dbDriver org.postgresql.Driver -dbUser igops -dbPassword password -dbUrl "jdbc:postgresql://postgresql-server:port/igops-db" -keystore "/opt/netiq/idm/apps/tomcat/conf/encrypt-keys.pkcs12" -storepass encryption-keystore-password -script "/opt/netiq/idm/apps/idgov/scripts/all-import-configs.script"
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.
(Conditional) If you chose to generate SQL scripts, complete the following steps:
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
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
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" -keystore "/opt/netiq/idm/apps/tomcat/conf/encrypt-keys.pkcs12" -storepass encryption-keystore-password -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 1g0p5Pa55 -dbUrl "jdbc:oracle:thin:@myoracle.mycompany.com:1521/mysid" -keystore "/opt/netiq/idm/apps/tomcat/conf/encrypt-keys.pkcs12" -storepass K3yPa55 -script "/opt/netiq/idm/apps/idgov/scripts/all-import-configs.script"
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.
Create the appropriate logins, users, and roles in the database.
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
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
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;encrypt=false;databaseName=igops" -keystore "/opt/netiq/idm/apps/tomcat/conf/encrypt-keys.pkcs12" -storepass encryption-keystore-password -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 1g0p5Pa55 -dbUrl "jdbc:sqlserver://mysever.netiq.com:1433;encrypt=false;databaseName=igops" -keystore "/opt/netiq/idm/apps/tomcat/conf/encrypt-keys.pkcs12" -storepass K3y_Pa55 -script "/opt/netiq/idm/apps/idgov/scripts/all-import-configs.script"
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.
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 Server 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
(Conditional) When Identity Reporting is installed on a separate server than Identity Governance, enter the following command to populate the global configuration values in the database:
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.6.0.jar" com.netiq.iac.config.util.IacConfigUtil -dbDriver org.postgresql.Driver -dbUser igops-user -dbPassword password -dbUrl "jdbc:postgresql://postgresql-server:port/igops-db" -keystore "/opt/netiq/idm/apps/tomcat/conf/encrypt-keys.pkcs12" -storepass encryption-keystore-password -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.6.0.jar" com.netiq.iac.config.util.IacConfigUtil -dbDriver org.postgresql.Driver -dbUser igops -dbPassword 1g0p5Pa55 -dbUrl "jdbc:postgresql://myserver.netiq.com:5432/igops" -keystore "/opt/netiq/idm/apps/tomcat/conf/encrypt-keys.pkcs12" -storepass K3y_Pa55 -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" -keystore "/opt/netiq/idm/apps/tomcat/conf/encrypt-keys.pkcs12" -storepass encryption-keystore-password -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 1g0p5Pa55 -dbUrl "jdbc:oracle:thin:@myoracle.mycompany.com:1521/mysid" -keystore "/opt/netiq/idm/apps/tomcat/conf/encrypt-keys.pkcs12" -storepass K3y_Pa55 -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;encrypt=false;databaseName=igops" -keystore "/opt/netiq/idm/apps/tomcat/conf/encrypt-keys.pkcs12" -storepass encryption-keystore-password -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" -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 1g0p5Pa55 -dbUrl "jdbc:sqlserver://myserver.netiq.com:1433;encrypt=false;databaseName=igops" -keystore "/opt/netiq/idm/apps/tomcat/conf/encrypt-keys.pkcs12" -storepass K3y_Pa55 -script "/opt/netiq/idm/apps/idrpt/scripts/all-import-configs.script"
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.
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
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
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