5.8 Creating the Databases before Installing Identity Governance

1. Install a supported version of SQL Server. For more information, see Database Requirements.

2. Ensure that the database server and the Identity Governance server run on the same subnetwork in your IT environment.

3. Create the databases, logins, users, and roles using the following commands:

   USE [master];
   CREATE DATABASE [igops];
   CREATE DATABASE [igarc];
   CREATE DATABASE [igdcs];
   CREATE DATABASE [igwf];
   CREATE DATABASE [igara];
   
   ALTER DATABASE [igops] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;
   ALTER DATABASE [igarc] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;
   ALTER DATABASE [igdcs] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;
   ALTER DATABASE [igwf] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;
   ALTER DATABASE [igara] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;
   
   CREATE LOGIN [igops] WITH PASSWORD = 'password';
   CREATE LOGIN [igarc] WITH PASSWORD = 'password';
   CREATE LOGIN [igdcs] WITH PASSWORD = 'password';
   CREATE LOGIN [igwf] WITH PASSWORD = 'password';
   CREATE LOGIN [igara] WITH PASSWORD = 'password';
   GO
   
   USE [igops]; 
   CREATE USER [igops] FOR LOGIN [igops];
   ALTER ROLE [db_owner] ADD MEMBER [igops];
   CREATE ROLE [IG_REPORT_ROLE];
   CREATE LOGIN [igrptuser] WITH PASSWORD = 'password';
   CREATE USER [igrptuser] FOR LOGIN [igrptuser];
   ALTER ROLE [IG_REPORT_ROLE] ADD MEMBER [igrptuser];
   GO
   
   USE [master];
   GRANT VIEW SERVER STATE TO igops;
   GO
   
   USE [igarc];
   CREATE USER [igarc] FOR LOGIN [igarc];
   ALTER ROLE [db_owner] ADD MEMBER [igarc];
   CREATE ROLE [IG_REPORT_ROLE];
   GO
   
   USE [igdcs]; 
   CREATE USER [igdcs] FOR LOGIN [igdcs];
   ALTER ROLE [db_owner] ADD MEMBER [igdcs];
   GO
    
   USE [igwf]; 
   CREATE USER [igwf] FOR LOGIN [igwf];
   ALTER ROLE [db_owner] ADD MEMBER [igwf];
   GO
    
   USE [igara]; 
   CREATE USER [igara] FOR LOGIN [igara];
   ALTER ROLE [db_owner] ADD MEMBER [igara];
   GO

4. Specify the same password for all databases.

   NOTE:The installation process for Identity Governance requires you to specify one password that becomes the password for all of the databases. After installing Identity Governance, you can modify the passwords to be unique for each database.

5. When installing Identity Governance, specify one of the following settings:

   • Configure database now > Update, if you want the installation program to generate or update the schemas, tables, and views when you migrate from the previous release of Identity Governance

   • Configure database now > Use only existing, if your database is already set up correctly with all schemas, roles, and users

   • Generate SQL for later, if your database administrator wants to generate the schemas, tables, and views

   For more information about using SQL scripts, see Section 5.6, Manually Creating and Populating the Databases.

1. (Optional) If you are installing Identity Reporting, also use the following commands:

   USE [master];
   CREATE DATABASE [igrpt];
   ALTER DATABASE [igrpt] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;
   CREATE LOGIN [idm_rpt_cfg] WITH PASSWORD = 'password';
   GO
   
   USE [igrpt]; 
   CREATE USER [idm_rpt_cfg] FOR LOGIN [idm_rpt_cfg];
   CREATE SCHEMA [IDM_RPT_CFG] AUTHORIZATION [idm_rpt_cfg];
   ALTER AUTHORIZATION ON SCHEMA::[IDM_RPT_CFG] TO [idm_rpt_cfg];
   ALTER ROLE [db_owner] ADD MEMBER [idm_rpt_cfg];
   GO

2. When installing Identity Reporting, specify one of the following settings:

   • Configure database now > Update, if you want the installation program to generate or update the schemas, tables, and views when you migrate from the previous release of Identity Governance

   • Configure database now > Use only existing, if your database is already set up correctly with all schemas, roles, and users

   • Generate SQL for later, if your database administrator wants to generate the schemas, tables, and views

   For more information about using SQL scripts, see Section 5.6, Manually Creating and Populating the Databases.

1. Install a supported version of the SQL Server. For more information, see Section 2.4.2, Database Requirements.

2. Ensure that the database server and the Identity Governance server run on the same subnetwork in your IT environment.

3. Create the database, login, user, and role using the following commands:

   USE [master];
   CREATE DATABASE [igaworkflowdb];
   ALTER DATABASE [igaworkflowdb] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;
   CREATE LOGIN [igawfadmin] WITH PASSWORD = '<password>';
   
   USE [igaworkflowdb];
   CREATE USER [igawfadmin] FOR LOGIN [igawfadmin];
   ALTER ROLE [db_owner] ADD MEMBER [igawfadmin];

4. When installing Workflow Engine, specify one of the following settings:

   • Configure database now > Update, if you want the installation program to generate or update the schemas, tables, and views

   • Configure database now > Use only existing, if your database is already set up correctly with all schemas, roles, and users

   • Generate SQL for later, if your database administrator wants to generate the schemas, tables, and views

   For more information about using SQL scripts, see Section 5.6, Manually Creating and Populating the Databases.

To create the Oracle schema for the Identity Governance databases:

1. Install a supported version of Oracle. For more information, see Section 2.4.2, Database Requirements.

2. Ensure that the database server and the Identity Governance server run on the same subnetwork in your IT environment.

3. Create or identify the database that you want Identity Governance to use.

4. In the database, create the schemas for igops, igarc, igdcs, igwf, and igara with the following privileges:

   • select_catalog_role

   • Create session

   • Create table

   • Create view

   • Create sequence

   • Create procedure

   • Create trigger

   • Create type (igops and igarc only)

   • Create materialized view (igops only)

   • Analyze any (igops and igarc only)

   • Create public synonym (igops and igarc only)

   • Drop public synonym (igops and igarc only)

5. Specify the same password for all schemas.

   NOTE:The installation process for Identity Governance requires you to specify one password that applies to all of the schemas. After installing Identity Governance, you can modify the passwords to be unique for each schema.

6. Issue the following commands:

   NOTE:If you use the default values of users and temp, skip these commands:

   • alter user dbName default tablespace users;

   • alter user dbName temporary tablespace temp;

   ALTER USER igops DEFAULT TABLESPACE USERS;
   ALTER USER igops TEMPORARY TABLESPACE TEMP;
   ALTER USER igops QUOTA UNLIMITED ON USERS;
   ALTER USER igarc DEFAULT TABLESPACE USERS;
   ALTER USER igarc TEMPORARY TABLESPACE TEMP;
   ALTER USER igarc QUOTA UNLIMITED ON USERS;
   ALTER USER igdcs DEFAULT TABLESPACE USERS;
   ALTER USER igdcs TEMPORARY TABLESPACE TEMP;
   ALTER USER igdcs QUOTA UNLIMITED ON USERS;
   ALTER USER igwf DEFAULT TABLESPACE USERS;
   ALTER USER igwf TEMPORARY TABLESPACE TEMP;
   ALTER USER igwf QUOTA UNLIMITED ON USERS;
   ALTER USER igara DEFAULT TABLESPACE USERS;
   ALTER USER igara TEMPORARY TABLESPACE TEMP;
   ALTER USER igara QUOTA UNLIMITED ON USERS;
   CREATE USER igrptuser IDENTIFIED BY "igrptuser_password";
   GRANT CREATE SESSION TO igrptuser;
   ALTER USER igrptuser DEFAULT TABLESPACE USERS;
   ALTER USER igrptuser TEMPORARY TABLESPACE TEMP;
   CREATE ROLE IG_REPORT_ROLE NOT IDENTIFIED;
   GRANT IG_REPORT_ROLE TO igrptuser;
   

7. Create the Identity Governance user, igrptuser, that has access to reporting information.

   CREATE USER igrptuser IDENTIFIED BY "igrptuser_password";

8. Grant the reporting role to the reporting user plus additional privileges.

   GRANT IG_REPORT_ROLE TO igrptuser;
   GRANT CREATE SESSION TO igrptuser;
   GRANT EXECUTE ON igops.MAX_RISK_LEVEL TO igrptuser;
   GRANT EXECUTE ON igops.MIN_RISK_LEVEL TO igrptuser;
   GRANT EXECUTE ON igops.RISK_VALUE TO igrptuser;
   ALTER USER igrptuser DEFAULT TABLESPACE USERS;
   ALTER USER igrptuser TEMPORARY TABLESPACE TEMP;
   

9. When installing Identity Governance, specify one of the following settings:

   • Configure database now > Update, if you want the installation program to generate or update the schemas, tables, and views when you migrate from the previous release of Identity Governance

   • Configure database now > Use only existing, if your database is already set up correctly with all schemas, roles, and users

   • Generate SQL for later, if your database administrator wants to generate the schemas, tables, and views

   For more information about using SQL statements after installation, see Section 5.11, Configuring the Databases Using the SQL Scripts.

1. Install a supported version of Oracle.

   For more information, see Section 2.4.2, Database Requirements.

   IMPORTANT:You must create the database (SID) in AL32UTF-8 (Unicode UTF-8 Universal character set) before installing Identity Reporting.

2. Ensure that the database server, Identity Governance, and Identity Reporting run in the same subnetwork.

3. Use the following commands to create the database:

   CREATE USER idm_rpt_cfg IDENTIFIED BY idm_rpt_cfg_password;
   GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TRIGGER, UNLIMITED TABLESPACE TO idm_rpt_cfg

4. To prepare the database, complete the following steps:

   1. Create or identify the database that you want Identity Reporting to use, such as igrpt.

   2. In the database, create the schema for idm_rpt_cfg with the connect privilege.

      or

      You can allow the installation program to create the schema for you.

   3. Specify a password for the schema.

5. When installing Identity Reporting, specify one of the following settings:

   • Configure database now > Update, if you want the installation program to generate or update the schemas, tables, and views when you migrate from the previous release of Identity Governance

   • Configure database now > Use only existing, if your database is already set up correctly with all schemas, roles, and users

   • Generate SQL for later, if your database administrator wants to generate the schemas, tables, and views

   For more information about using SQL statements after installation, see Section 5.11, Configuring the Databases Using the SQL Scripts.

1. Install a supported version of Oracle. For more information, see Section 2.4.2, Database Requirements.

2. Ensure that the database server and the Identity Governance server run on the same subnetwork in your IT environment.

3. Create or identify the database that you want the Workflow Engine to use.

4. In the database, create the schema for igworkflowdb with the following privileges:

   • select_catalog_role

   • Create procedure

   • Create sequence

   • Create session

   • Create table

   • Create trigger

   • Create view

5. Issue the following commands:

   NOTE:If you use the default values of users and temp, skip these commands:

   • alter user dbname default tablespace users;

   • alter user dbname temporary tablespace temp;

   ALTER USER igawfadmin DEFAULT TABLESPACE USERS;
   ALTER USER igawfadmin TEMPORARY TABLESPACE TEMP;
   ALTER USER igawfadmin QUOTA UNLIMITED ON USERS;

6. When installing Workflow Engine, specify one of the following settings:

   • Configure database now > Update, if you want the installation program to generate or update the schemas, tables, and views

   • Configure database now > Use only existing, if your database is already set up correctly with all schemas, roles, and users

   • Generate SQL for later, if your database administrator wants to generate the schemas, tables, and views

1. Install a supported version of PostgreSQL. For more information, see Section 2.4.2, Database Requirements.

2. Ensure that the database server and the Identity Governance server run on the same subnetwork in your IT environment.

3. Create the databases and roles for igops, igdcs, igwf, and igara using the following commands:

   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;
   --(Optional if installing the cloud (AWS or Azure)
   GRANT "igops" TO current_user;
   GRANT "igarc" TO current_user;
   GRANT "igdcs" TO current_user;
   GRANT "igwf" TO current_user;
   GRANT "igara" TO current_user;
   --(End of optional)
   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';

4. Grant database connection privileges to the reporting user.

   GRANT CONNECT ON DATABASE "igops" TO "igrptuser";

5. Create the reporting user igrptuser.

   CREATE ROLE "igrptuser" PASSWORD 'igrptuser_password' LOGIN;

6. Grant the reporting role to the reporting user.

   GRANT IG_REPORT_ROLE TO "igrptuser";

7. Specify the same password for all databases.

   NOTE:The installation process for Identity Governance requires you to specify one password that applies to all databases. After installing Identity Governance, you can modify the passwords to be unique for each database.

8. When you install Identity Governance, specify one of the following settings:

   • Configure database now > Update, if you want the installation program to either create missing or update existing schemas, tables, and views, as when you migrate from the previous release of Identity Governance

   • Configure database now > Use only existing, if your database is already set up correctly with all schemas, roles, and users

   • Generate SQL for later, if your database administrator wants to generate the schemas, tables, and views

   For more information about using SQL statements after installation, see Section 5.11, Configuring the Databases Using the SQL Scripts.

1. Use the following commands to create the database for Identity Reporting:

   CREATE DATABASE "igrpt" WITH OWNER "pg_admin_user" TEMPLATE = template0 ENCODING = 'UTF8';
   CREATE ROLE idm_rpt_cfg WITH LOGIN PASSWORD 'idm_rpt_cfg_password';
   CREATE SCHEMA idm_rpt_cfg AUTHORIZATION idm_rpt_cfg;
   GRANT CREATE ON SCHEMA public TO idm_rpt_cfg;
   CREATE TABLE idm_rpt_cfg.databasechangelog (LIKE public.databasechangelog INCLUDING DEFAULTS INCLUDING INDEXES INCLUDING CONSTRAINTS);
   ALTER TABLE idm_rpt_cfg.databasechangelog OWNER TO idm_rpt_cfg;
   INSERT INTO idm_rpt_cfg.databasechangelog SELECT * from public.databasechangelog WHERE filename != 'IdmRptDataSchemaChangeLog.xml' AND filename != 'IdmRptDataOTBDataChangeLog.xml';
   UPDATE idm_rpt_cfg.databasechangelog set author = 'idmrpt' where author = 'idmrptsrv';
   CREATE TABLE idm_rpt_cfg.databasechangeloglock (LIKE public.databasechangeloglock INCLUDING DEFAULTS INCLUDING INDEXES INCLUDING CONSTRAINTS);
   ALTER TABLE idm_rpt_cfg.databasechangeloglock OWNER TO idm_rpt_cfg;
   INSERT INTO idm_rpt_cfg.databasechangeloglock SELECT * from public.databasechangeloglock;
   FOR table_info IN SELECT * from pg_tables where schemaname = 'idm_rpt_cfg' and tableowner != 'idm_rpt_cfg' LOOP
   cmd := 'ALTER TABLE idm_rpt_cfg.' || table_info.tablename || ' OWNER TO idm_rpt_cfg';
   EXECUTE cmd;
   END LOOP;

2. Specify the same password for the Identity Reporting database as the Identity Governance databases.

   NOTE:The installation process for Identity Governance requires you to specify one password that applies to all databases. After installing Identity Governance, you can modify the passwords to be unique for each database.

3. When you install Identity Reporting or during the Identity Governance installation if you install Identity Reporting on the Identity Governance server, specify one of the following settings:

   • Configure database now > Update, if you want the installation program to either create missing or update existing schemas, tables, and views, as when you migrate from the previous release of Identity Reporting

   • Configure database now > Use only existing, if your database is already set up correctly with all schemas, roles, and users

   • Generate SQL for later, if your database administrator wants to generate the schemas, tables, and views

   For more information about using SQL statements after installation, see Section 5.11, Configuring the Databases Using the SQL Scripts.

1. Install a supported version of PostgreSQL. For more information, see Section 2.4.2, Database Requirements.

2. Ensure that the database server and the Identity Governance server run on the same subnetwork in your IT environment.

3. Use the following commands to create the database for Workflow Engine:

   CREATE ROLE igawfadmin PASSWORD '<password>' LOGIN;
   (if cloud) GRANT "igawfadmin" TO current_user;
   CREATE DATABASE "igaworkflowdb" WITH OWNER "igawfadmin" TEMPLATE = template0 ENCODING = 'UTF8';
   GRANT ALL PRIVILEGES ON DATABASE "igaworkflowdb" TO "igawfadmin";
   GRANT TEMPORARY, CONNECT ON DATABASE "igaworkflowdb" TO PUBLIC;

   NOTE:The installation process for Identity Governance requires you to specify one password that applies to all databases. After installing the Workflow Engine, you can modify the passwords to be unique for each database.

4. When installing Workflow Engine, specify one of the following settings:

   • Configure database now > Update, if you want the installation program to generate or update the schemas, tables, and views

   • Configure database now > Use only existing, if your database is already set up correctly with all schemas, roles, and users

   • Generate SQL for later, if your database administrator wants to generate the schemas, tables, and views