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 selected Configure Database Now during the installation, you can skip this section.
Identity Governance and Identity Reporting need the following SQL scripts, located by default in:
Linux: /opt/netiq/idm/apps/idgov/sql and /opt/netiq/idm/apps/idrpt/sql
Windows: c:\netiq\idm\apps\idgov\sql and c:\netiq\idm\apps\idrpt\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
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 11.2, Manually Generating the Database Schema after the Installation.
To populate the user schema in the database, have the database administrator run a command 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
(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 script as GLOBAL properties.
(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"
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"
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 11.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/sqlplu 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%" -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"
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 11.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%;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"
If you chose Generate SQL for later during installation, have the database administrator run the SQL script to configure the Identity Reporting database. 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 11.2, Manually Generating the Database Schema after the Installation.
Ensure that the script is located on the database server. The following is a list of example commands to run on the different databases to generate the Identity Reporting 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 idm_rpt_cfg igrpt -f /tmp/sql/rpt-init-01-idm_rpt_cfg.sql
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/sqlplu use the following command:
sqlplus -L idm_rpt_cfg/"password"@<server>:1521/pdborcl @ /tmp/sql/rpt-init-01-idm_rpt_cfg.sql
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