Grant User Privileges for Oracle Database 21c and Lower

The user privileges that are required for connecting to Oracle database from Oracle GoldenGate depend on the type of user.

Privileges should be granted depending on the actions that the user needs to perform as the GoldenGate Administrator User on the source and target databases. For example, to grant DML operation privileges to insert, update, and delete transactions to a user, use the GRANT ANY INSERT/UPDATE/DELETE privileges and to further allow users to work with tables and indexes as part of DML operations, use the GRANT CREATE/DROP/ALTER ANY TABLE/INDEX privileges.

If the GoldenGate Administrator user has the DBA role, additional object privileges are not needed. However, there might be security constraints granting the DBA role to the GoldenGate Administration user. The DBA role is not necessarily required for Oracle GoldenGate.

If there are many objects being replicated, you might consider using the ANY privilege for DML and DDL operations. This simplifies the provision of privileges to the GoldenGate Administrator users, as you only need to grant a few privileges depending on the database operations.

The following table describes some of the essential privileges for GoldenGate Administrator user for Oracle database. For explanation purposes, the table uses c##ggadmin as an example of a common user for a multitenant container database and ggadmin as the pluggable database (PDB) user. PDBEAST and PDBWEST are used as examples of PDB names.

The following table describes the essential privileges for GoldenGate Administrator user for using Oracle GoldenGate with on source and target Oracle databases:

Privilege	Extract	Replicat All Modes	Purpose
`RESOURCE`	Yes	Yes	Required to create objects In Oracle Database 12cR1 and later, instead of `RESOURCE`, grant the following privilege: `ALTER USER user QUOTA {size \| UNLIMITED} ON tablespace;`
`CONNECT`	Yes	Yes	Common user `SYSTEM` connects to the root container. This privilege is essential when the DBA role is not assigned to the user. See an example of Permissions granted to an Oracle mutitenant database common user.
`CREATE PROCEDURE`	Yes	Yes	Required to add heartbeat tables.
`CREATE SESSION`	Yes	Yes	Required to connect to the database.
`CREATE VIEW`	Yes	Yes	Required to add the heartbeat table view. If you want to be specific to each object, you can also provide the privileges for each object individually. You may consider creating a specific database role to maintain such privileges.
`ALTER SYSTEM`	Yes	Yes	Perform administrative changes, such as enabling logging.
`ALTER USER`	Yes	Yes	Required for multitenant architecture and `GGADMIN` should be a valid Oracle GoldenGate administrator schema.
`EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE ('REPUSER', CONTAINER=>'PDBEAST');`	Yes	Yes	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 (example: `pdbeast`). Grant privileges for Extract and Replicat users. See Example: Grant privileges using the DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE package Grant privileges to capture from Virtual Private Database Grants privileges to capture redacted data
Grant `DV_GOLDENGATE_ADMIN` and `DV_GOLDENGATE_REDO_ACCESS` privileges connected as `SYS` user to the Extract and the Replicat user.	Yes	Yes	Capture from Data Vault. See Privileges for Capturing from Oracle Data Vault.
Grant Replicat privileges in `DBMS_MACADM.ADD_AUTH_TO_REALM` if applying to a realm.	NA	Yes	Capture from Data Vault. See Privileges for Capturing from Oracle Data Vault.
`INSERT`, `UPDATE`, `DELETE` on target tables	NA	Yes	Apply replicated DML to target objects. See Details of Support for Objects and Operations in Oracle DML
`GRANT INSERT ANY TO...` `GRANT UPDATE ANY TO...` `GRANT DELETE ANY TO...`	NA	Yes	Grant these privileges to the Replicat user, instead of granting `INSERT`, `UPDATE`, `DELETE` to every table, if replicating every table.
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. See Privileges for Capturing from Oracle Data Vault.
DDL privileges on target objects (if using DDL support)	NA	Yes	Issue replicated DDL on target objects. See Details of Support for Objects and Operations in Oracle DDL.
`GRANT [CREATE\|ALTER\|DROP] ANY [TABLE\|INDEX\|VIEW\|PROCEDURE] to GGADMIN;`	Yes	Yes	Grants privileges for DDL Replication for tables.
`CREATE ANY TABLE`	Yes	Yes	Grants privileges for creating table in any schema. To allow creating tables only in a specific schema, use the `CREATE TABLE` privilege.
`CREATE ANY VIEW`	Yes	Yes	Grants privileges to create view in any database schema. To allow creating views in a specific schema, use the `CREATE VIEW` privilege.
`SELECT ANY DICTIONARY`	Yes	Yes	Allow all privileges to work properly on dictionary tables.

Example: Permissions granted for the Oracle database common user

Privileges granted for the Oracle database common user, which is c##ggadmin in the following example:

CREATE USER c##ggadmin IDENTIFIED BY passw0rd CONTAINER=all DEFAULT
TABLESPACE GG_DATA TEMPORARY TABLESPACE temp;
GRANT RESOURCE to c##ggadmin;
GRANT CREATE SESSION to c##ggadmin;
GRANT CREATE VIEW to c##ggadmin;
GRANT CREATE TABLE to c##ggadmin;
GRANT CONNECT to c##ggadmin CONTAINER=all; 
GRANT DV_GOLDENGATE_ADMIN; –-- for data vault user
GRANT DV_GOLDENGATE_REDO_ACCESS; –-- for data vault user
GRANT ALTER SYSTEM to c##ggadmin;
GRANT ALTER USER to c##ggadmin;
ALTER USER c##ggadmin SET CONTAINER_DATA=all CONTAINER=current;
ALTER USER c##ggadmin QUOTA unlimited ON GG_DATA;
GRANT SELECT ANY DICTIONARY to c##ggadmin;
GRANT SELECT ANY TRANSACTION to c##ggadmin;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('c##ggadmin');

In this example, DBA privilege is not provided. If privileges are missing, then the DBA has to grant necessary privileges additionally.

Privileges granted for PDB user ggadmin are provided in the following example:

ALTER SESSION SET CONTAINER=dbwest; 
CREATE USER ggadmin IDENTIFIED BY PASSWORD CONTAINER=CURRENT; 
GRANT CONNECT, RESOURCE, DBA TO ggadmin CONTAINER=CURRENT; 
GRANT CREATE SESSION TO ggadmin CONTAINER=CURRENT; 
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggadmin');

Note:

Granting DBA role is not mandatory for every user. Privileges should be granted depending on the actions that the user needs to perform on the database. For example, to grant DML operation privileges to insert, update, and delete transactions to ggadmin, use the GRANT ANY INSERT/UPDATE/DELETE privileges and to further allow users to work with tables and indexes as part of DML operations, use the

GRANT
          CREATE/DROP/ALTER ANY TABLE/INDEX

privileges.

Example: Grant privileges using the DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE package

This procedure grants the privileges needed by a user to be an Oracle GoldenGate administrator The following example grants explicit privileges for Extract on Oracle multitenant database:

BEGIN  
DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE 
(GRANTEE => 'c##ggadmin',  PRIVILEGE_TYPE => 'CAPTURE',  GRANT_SELECT_PRIVILEGES => TRUE,  DO_GRANTS => TRUE,  CONTAINER => 'ALL'  ); 
END;

See DBMS_GOLDENGATE_AUTH in Oracle Database PL/SQL Packages and Types Reference for more information.