5.8 Creating the Databases before Installing Identity Governance

You must first create the required databases if you use the SQL scripts. If you use the Identity Governance installer to create the databases, you can skip this section.

You can install the database server and create the databases for Identity Governance if you do not want the installation program to create the databases for you. If you do manually create the databases, you must add the schema for the different databases and create a temporary administrator. The installation program uses this information to create the schemas, tables, views, and other artifacts in the database unless you select Generate SQL for later in the Database details section of the installation program.

IMPORTANT:The databases must not contain anything but the schema, or the installation of Identity Governance fails.

The Identity Governance installer needs the name of the databases to represent the operations, archive, data collection, provisioning workflow, and analytics databases for Identity Governance. For more information, see Section 5.1, Understanding the Databases.

However, your database administrator might prefer to create the schema for the databases, as well as the database artifacts, rather than allowing the installation process to do so. If that is the case, then you would use the SQL scripts to create the schema and populate the databases. Use the steps for the appropriate database version to manually create the databases before starting the Identity Governance installation.

5.8.1 Creating the Microsoft SQL Server Databases before Installing

If your database administrator uses the SQL scripts to create and populate the databases for Identity Governance, Identity Reporting, and Workflow Engine, you must manually create the databases. Use the following information to create the databases for Identity Governance, Identity Reporting, and Workflow Engine.

Creating the Microsoft SQL Server Databases before the Identity Governance Installation

Use the following procedure to create databases for Identity Governance on a Microsoft SQL Server. The database administrator would perform these steps if you did not want the Identity Governance installer to create the databases for you.

  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.

Creating the Microsoft SQL Server Database before Installing Identity Reporting

As a system administrator, create a database, such as igrpt. Alternatively, you can allow the installation program to create a database for you. Specify an account for the database owner that the installation process can use. For more information, see Creating a Temporary Microsoft SQL Server Database Administrator for the installation process.

  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.

Creating the Microsoft SQL Server Database before Installing the Workflow Engine

The Identity Governance installer creates the databases for you. However, you have the option to create the database for the Workflow Engine. Use the following procedure to create the databases on an Microsoft SQL Server.

  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.

5.8.2 Creating the Oracle Schema Before Installing Identity Governance

Oracle has you create schemas instead of separate databases like the other supported database types. Use the following procedure to create the schema for Identity Governance, Identity Reporting, and Workflow Engine on an Oracle database server. The database administrator would perform these steps if you did not want the Identity Governance installer to create the schema for you. The installation program will create the schemas, tables, views, and other artifacts in the database unless you select Generate SQL for later in the Database details section of the installation program. The program needs the name of the database, user tablespace (USERS by default), the temporary tablespace (TEMP by default), and the user schemas to represent the operations, archive, data collection, provisioning workflow, and analytics tables for Identity Governance and Identity Reporting.

Creating the Oracle Schemas before Installing Identity Governance

This procedure assumes that you will use the default names for the schemas. For more information, see Section 5.1, Understanding the Databases.

IMPORTANT:You must turn on the SQL Tuning Advisor to optimize queries in the Oracle database.

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.

Creating the Oracle Schema before Installing Identity Reporting

This procedure assumes that you will use the default name for the schema. For more information, see Section 5.1, Understanding the Databases.

IMPORTANT:You must turn on the SQL Tuning Advisor to optimize queries in the Oracle database.

  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.

Creating the Oracle Schema before Installing the Workflow Engine

This procedure assumes that you will use the default names for the schemas. For more information, see Section 5.1, Understanding the Databases.

IMPORTANT:You must turn on the SQL Tuning Advisor to optimize queries in the Oracle database.

  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

5.8.3 Creating the PostgreSQL Databases Before Installing

Use the following procedures to create the databases for Identity Governance, Identity Reporting, and Workflow Engine on a PostgreSQL database server. The database administrator performs these steps if you did not want the Identity Governance installer to create the databases for you. The installation program creates the schemas, tables, views, and other artifacts in the database unless you select Generate SQL for later in the Database details section of the installation program.

Creating the PostgreSQL Databases before Installing Identity Governance

Use the following procedure to create the PostgreSQL databases for Identity Governance. You would use these if you do not want the Identity Governance installer to create the databases for you.

  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.

Creating the PostgreSQL Database before Installing Identity Reporting

Use the following procedure to create the PostgreSQL database for Identity Reporting. You would use these if you do not want the Identity Governance installer to create the database for you.

  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.

Creating the PostgreSQL Database before Installing the Workflow Engine

Use the following procedure to create the PostgreSQL database for the Workflow Engine. You can follow these steps if you do not want the Identity Governance installer to create the database for you.

  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

5.8.4 Using Vertica

You can send the information in the SQL databases to Vertica for further analysis. You can configure Vertica to obtain the information from the supported database that you choose to use in Identity Governance. Use the Vertica documentation for the procedure to integrate Vertica with the specific database type. For more information, see the Vertica Documentation.