6.8 Configuring Identity Reporting

Add the required roles to the database using the create_dcs_roles_and_schemas.sql and create_rpt_roles_and_schemas.sql SQLs located in /opt/netiq/idm/apps/IDMReporting/sql/.

Log in to PGAdmin as a postgres user.

Run the Query tool.

To create Create_rpt_roles_and_schemas and Create_dcs_roles_and_schemas procedures, copy the content from these SQLs to the Query tool and execute against the connected database.

To create IDM_RPT_DATA, IDM_RPT_CFG, and IDMRPTUSER roles, execute the following commands in the given order:

Select CREATE_DCS_ROLES_AND_SCHEMAS('<Set pwd for IDM_RPT_DATA>', '<Set pwd for IDMRPTUSER>');

Select CREATE_RPT_ROLES_AND_SCHEMAS('<Set pwd for IDM_RPT_CFG>');

For example, if the password for IDM_RPT_DATA, IDMRPTUSER, and IDM_RPT_CFG are password, password1, and password2 respectively, then you must execute the following commands:

Select CREATE_DCS_ROLES_AND_SCHEMAS('password', 'password1');

Select CREATE_RPT_ROLES_AND_SCHEMAS('password2');

Copy the content of get_formatted_user_dn.sql from /opt/netiq/idm/apps/IDMReporting/sql/ to the Query tool and execute against the connected database.

Log in to SQL Developer as a database admin (sysdba) user.

Create Create_rpt_roles_and_schemas by copying the content of the following SQL into SQL Developer and executing it against the connected database:

alter session set "_ORACLE_SCRIPT"=true;
 
CREATE OR REPLACE PROCEDURE create_rpt_roles_and_schemas(
   idm_rpt_cfg_password character varying)
AUTHID CURRENT_USER
AS
   cnt number;
BEGIN
   /* Create user IDM_RPT_CFG if it does not exist */
   select count(*) into cnt from ALL_USERS WHERE USERNAME = 'IDM_RPT_CFG'; 
   IF cnt = 0 THEN
       execute immediate 'CREATE USER idm_rpt_cfg IDENTIFIED BY ' || idm_rpt_cfg_password;
       DBMS_OUTPUT.put_line('Created user idm_rpt_cfg');
   END IF;
 
   /* Grant rights to the idm_rpt_cfg user */
   execute immediate 'GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TRIGGER, UNLIMITED TABLESPACE to idm_rpt_cfg';
   DBMS_OUTPUT.put_line('Granted rights to user idm_rpt_cfg');
   execute immediate 'alter session set "_ORACLE_SCRIPT"=true';
   execute immediate 'alter session set "_ORACLE_SCRIPT"=false';
END;
/
exec CREATE_RPT_ROLES_AND_SCHEMAS('Novell123');
/
alter session set "_ORACLE_SCRIPT"=false;
/

Log in to SQL Developer as a System user and create Create_dcs_roles and schemas by copying the content of the following SQL into SQL Developer and executing it against the connected database:

alter session set "_ORACLE_SCRIPT"=true;
 
CREATE OR REPLACE PROCEDURE create_dcs_roles_and_schemas(
   idm_rpt_data_password character varying,
   idmrptuser_password character varying)
AUTHID CURRENT_USER
AS
   cnt number;
BEGIN
/* Create user IDM_RPT_DATA if it does not exist already */
   select count(*) into cnt from ALL_USERS WHERE USERNAME = 'IDM_RPT_DATA'; 
   IF cnt = 0 THEN
       execute immediate 'CREATE USER idm_rpt_data IDENTIFIED BY ' || idm_rpt_data_password;
       DBMS_OUTPUT.put_line('Created user idm_rpt_data');
   END IF;
   
   /* Grant rights to the idm_rpt_data user */
   execute immediate 'GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TRIGGER, UNLIMITED TABLESPACE to idm_rpt_data';
   execute immediate 'GRANT CREATE PUBLIC SYNONYM to IDM_RPT_DATA';
   execute immediate 'GRANT SELECT ON DBA_SEGMENTS TO IDM_RPT_DATA';
   DBMS_OUTPUT.put_line('Granted rights to user idm_rpt_data');
   /* Create user IDMRPTUSER if it does not exist */
   select count(*) into cnt from ALL_USERS WHERE USERNAME = 'IDMRPTUSER'; 
   IF cnt = 0 THEN
       execute immediate 'CREATE USER idmrptuser IDENTIFIED BY ' || idmrptuser_password;
       DBMS_OUTPUT.put_line('Created user idmrptuser');
   END IF;
   
   /* Grant rights to the idmrptuser user */
   execute immediate 'GRANT CREATE SESSION to idmrptuser';
   execute immediate 'GRANT SELECT ON sys.all_tables TO idmrptuser';
   DBMS_OUTPUT.put_line('Granted rights to user idmrptuser');
END;
/

To create IDM_RPT_CFG role, execute the following command:

begin
CREATE_RPT_ROLES_AND_SCHEMAS('<Set pwd for IDM_RPT_CFG>');
end; 
 

For example, if the password for IDM_RPT_CFG is password, then you must execute the following command:

begin
CREATE_RPT_ROLES_AND_SCHEMAS('password');
end; 
 

Assign the following permission:

GRANT CREATE PUBLIC SYNONYM to IDM_RPT_CFG;

Copy the content of get_formatted_user_dn-oracle.sql to SQL Developer from /opt/netiq/idm/apps/IDMReporting/sql/ and execute against the connected database.

Execute delete_create_dcs_roles_and_schemas-mssql.sql and delete_get_formatted_user_dn-mssql.sql.

Add the required roles to the database using create_dcs_roles_and_schemas.mssql and create_rpt_roles_and_schemas.mssql from /opt/netiq/idm/apps/IDMReporting/sql/.

Log in to SQL Developer as a database admin user.

To create Create_rpt_roles_and_schemas and Create_dcs_roles_and_schemas procedures, copy the content from create_dcs_roles_and_schemas.mssql and create_rpt_roles_and_schemas.mssql to SQL Developer and execute against the connected database.

To create IDM_RPT_DATA, IDM_RPT_CFG, and IDMRPTUSER roles, execute the following commands in the given order:

execute CREATE_DCS_ROLES_AND_SCHEMAS '<Set pwd for IDM_RPT_DATA>', '<Set pwd for IDMRPTUSER>'

execute CREATE_RPT_ROLES_AND_SCHEMAS \'<Set pwd for IDM_RPT_CFG>\'

Copy the content of get_formatted_user_dn.sql to SQL Developer from /opt/netiq/idm/apps/IDMReporting/sql/ and execute against the connected database.

Locate the following .sql files in /opt/netiq/idm/apps/IDMReporting/sql.

Clear the database checksums

Commit the changes to the database.