Database Setup Steps for Event-driven Data Load

When creating or updating an Oracle Access Governance orchestrated system you can enable the event-driven data load option. This option switches Day-N data load from the default snapshot-based model, to an event-driven one. A prerequisite for this option requires you to create a read-only user in the OIG database and grant required roles and system privileges.

To add a read-only user in the OIG database for the event-driven data load option, complete the following steps:

1. Connect to the OIG database as SYS and create a read-only user in the OIG database that will be used by Oracle Access Governance to connect to access change events:

   create user <username> identified by <password>;

   For example:

   create user ag2oigro identified by mypassword;

2. Connect to the OIG database as SYS and grant the required roles and system privileges to the read-only user you created in the previous step:

   GRANT CREATE SESSION TO <read-only user>;
   GRANT SELECT ANY TABLE TO <read-only user>;
   GRANT CREATE ANY TRIGGER TO <read-only user>;
   GRANT ADMINISTER DATABASE TRIGGER TO <read-only user>;
   GRANT CREATE TABLE TO <read-only user>;
   GRANT CREATE SYNONYM TO <read-only user>;
   GRANT UNLIMITED TABLESPACE TO <read-only user>;
   
   GRANT CONNECT TO <read-only user>;
   GRANT RESOURCE TO <read-only user>;

   For example:

   GRANT CREATE SESSION TO ag2oigro;
   GRANT SELECT ANY TABLE TO ag2oigro;
   GRANT CREATE ANY TRIGGER TO ag2oigro;
   GRANT ADMINISTER DATABASE TRIGGER TO ag2oigro;
   GRANT CREATE TABLE TO ag2oigro;
   GRANT CREATE SYNONYM TO ag2oigro;
   GRANT UNLIMITED TABLESPACE TO ag2oigro;
   
   GRANT CONNECT TO ag2oigro;
   GRANT RESOURCE TO ag2oigro;

3. Connect to the OIG database as the OIG DB Schema Owner and run the following command to create a script that will create synonyms for OIG tables for the read-only user:

   setheading on
   setlinesize 1500
   setnumformat 99999999999999999999
   setpagesize 25000
   spool synon.out
   SELECT 'create synonym <read-only user>.'||TNAME||' for <OIG_SCHEMA_USER_NAME>.'||TNAME||';'
   FROM TAB
   WHERE tabtype = 'TABLE';
   spool off

   For example:

   setheading on
   setlinesize 1500
   setnumformat 99999999999999999999
   setpagesize 25000
   spool synon.sql
   SELECT 'create synonym ag2oigro.'||TNAME||' for <OIG_SCHEMA_USER_NAME>.'||TNAME||';'
   FROM TAB
   WHERE tabtype = 'TABLE';
   spool off

4. Connect to the OIG database as the read-only user, and create the synonyms using the script created in the previous step:

   @<scriptname>

   For example:

   @synon.sql