Best Practices from Oracle Development's A‑Team

Database Permissions Required For OGG Users in a Database Vault Environment


In a Database Vault Environment, the Oracle GoldenGate Extract and Replicat users will require additional permissions in order to access Redo Log Information, System views and Table Data.

Main Article

Both Extract and Replicat will require access to the table data.  Extract may need to fetch certain data and Replicat will need to apply data to the target tables.  In order to do this, the OGG user must have Access to the Database Vault (DV) Realm.  The DV Realm is where the actual data exists.  Even a user with sysdba permissions cannot access the data in the DV Realm if that user was not also granted access to the DV Realm.  The procedure to grant access to the DV Realm is dvsys.dbms_macadm.ADD_AUTH_TO_REALM.  The Extract and Replicat user will need access to metadata so they will both need the role dv_goldengate_admin granted to them.  If this is an ASM environment, the Extract user will need the additional role dv_goldengate_redo_access granted to them.  These permissions need to be granted by the DV Owner.  Any other user trying to grant to roles and execute the require procedure will get a permissions error.  In Oracle 12c a new permission is required to execute DDL and is granted with the procedure dbms_macadm.AUTHORIZE_DDL.

Below is a sample script that will grant the required permissions for an Extract user, ORAGG, in an ASM environment for the 'Fusion Realm':

connect dvowner/&&3@&&1 select sys_context('userenv','db_name') from dual; grant dv_goldengate_admin, dv_goldengate_redo_access to oragg; declare l_realm_name VARCHAR2(30) := 'Fusion Realm'; begin dvsys.dbms_macadm.ADD_AUTH_TO_REALM( realm_name => l_realm_name ,auth_options => 1 ,grantee => 'ORAGG'); end; /
--- This example enables user ORAGG to execute DDL against --- DV objects in any schema for Oracle 12c Environments: EXEC DBMS_MACADM.AUTHORIZE_DDL('ORAGG', '%'); --- This example enables user ORAGG to execute DDL against --- DV objects in the HR schema only for Oracle 12c Environments: EXEC DBMS_MACADM.AUTHORIZE_DDL('ORAGG', 'HR');

It is recommended when creating the OGG user that it be separate from the realm.  If the OGG user is created as part of the realm, then additional permissions will need to be given to user 'SYS' on any target system where a replicat process will be registered and created.  There are objects that are created for the replicat by 'SYS' .  If  'SYS' does not have DDL access to the realm, then the replicat will fail with a permissions issue.

--- This example enables SYS user to create objects in the realm for the OGG user. --- Only required if ORAGG is created as part of the realm. --- It can be revoked once replicat has been started. EXEC DBMS_MACADM.AUTHORIZE_DDL('SYS','ORAGG');



Once these permissions have been granted to the OGG User, the extract and/or replicat processes will be able to process and apply data in a Database Vault Environment.  If you need more specific information on the roles and procedure discussed in this post, please refer to the Oracle Database Vault Administrators Guide.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha