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.