Oracle Database Privileges

The following privileges apply to Oracle database.

Privilege	Extract	Replicat All Modes	Purpose
`CREATE SESSION`	No	No	Connect to the database
`CREATE VIEW`	No	No	Required to add the heartbeat table view.
`RESOURCE`	No	No	Create objects. In Oracle Database 12cR1 and later, instead of `RESOURCE`, grant the following privilege: `ALTER USER user QUOTA {size \| UNLIMITED} ON tablespace;`
`ALTER SYSTEM`	No	No	Perform administrative changes, such as enabling logging.
`ALTER USER`	No	No	Required for multitenant architecture and `GGADMIN` should be a valid Oracle GoldenGate administrator schema.
`EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE ('REPUSER', CONTAINER=>'PDB1');`	Yes	Yes	This is required for Autonomous Databases (ATP and ADW) Extract and Replicat. Extracts in the Root container (`CDB$ROOT)`) might require a value of `ALL` or a specific PDB.
Privileges granted through `DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE`	No	No	(Extract) Grants privileges for Extract, including the logmining server. (Replicat) Grants privileges for both non-integrated and integrated Replicat, including the database inbound server.
Any or all of optional privileges of `DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE`	No	No	Capture from Virtual Private Database Capture redacted data
Grant the following privileges connected as `SYS` user to Extract and Replicat users: `EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggadmin user','',GRANT_OPTIONAL_PRIVILEGES=>'');` `GRANT DV_GOLDENGATE_ADMIN, DV_GOLDENGATE_REDO_ACCESS to 'ggadmin user';`	No	No	Capture from Data Vault
Grant Replicat the privileges in `DBMS_MACADM.ADD_AUTH_TO_REALM` if applying to a realm. `Connect as Database Vault owner and execute the following sctipts,` `BEGIN` `DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM( REALM_NAME => 'Oracle Default Component Protection Realm',GRANTEE => 'GGADMIN USER',AUTH_OPTIONS => 1) ;` `END ;` `/` `EXECUTE DBMS_MACADM.AUTHORIZE_DDL('SYS', 'SYSTEM');`	No	No	Capture from Data Vault
If DDL replication is performed, grant the following as Database Vault owner: `EXECUTE DBMS_MACADM.AUTHORIZE_DDL (‘GGADMIN USER', ‘SCHEMA FOR DDL’);`	No	No	Capture from Data Vault
`INSERT`, `UPDATE`, `DELETE` on target tables	NA	No	Apply replicated DML to target objects
`GRANT INSERT ANY TO...`, `GRANT UPDATE ANY TO...` and `GRANT DELETE ANY TO...`	NA	No	Use these privileges for the Replicat user, instead of granting `INSERT`, `UPDATE`, `DELETE` to every table, if replicating every table.
DDL privileges on target objects (if using DDL support)	NA	No	Issue replicated DDL on target objects
`LOCK ANY TABLE`	NA	No	Lock target tables. Only required for initial load using direct bulk load to SQL*Loader.
`SELECT ANY DICTIONARY`	No	No	Allow all privileges to work properly on dictionary tables.
`SELECT ANY TRANSACTION`	No	NA	Use a newer Oracle ASM API.

Here's an example of the list of permissions granted for the Oracle database root container:

DROP USER c##ggadmin CASCADE;
CREATE USER c##ggadmin IDENTIFIED BY passw0rd CONTAINER=all DEFAULT
TABLESPACE GG_DATA TEMPORARY TABLESPACE temp;
ALTER USER c##ggadmin SET CONTAINER_DATA=all CONTAINER=current;
GRANT CREATE SESSION to c##ggadmin;
GRANT CREATE VIEW to c##ggadmin;
GRANT CONNECT to c##ggadmin CONTAINER=all;
GRANT RESOURCE to c##ggadmin;
GRANT ALTER SYSTEM to c##ggadmin ;
GRANT SELECT ANY DICTIONARY to c##ggadmin ;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('c##ggadmin');
ALTER USER c##ggadmin QUOTA unlimited ON GG_DATA;

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='c##ggadmin' ORDER BY 2;

In this example, DBA privilege is not provided but the user will be able to access the DBA_SYS_PRIVS package.