Prepare Database Users and Privileges for Oracle

Learn about creating database users and assigning privileges for Oracle GoldenGate for Oracle.

Grant User Privileges for Oracle Database 23ai and Higher

Oracle Database 23ai provides user roles to grant role-based privileges to a database user with SQL statements.

Oracle Database 23ai uses a role-based approach to grant privileges necessary for replication. The roles are a replacement of the GRANT_ADMIN_PRIVILEGE procedures from the DBMS_GOLDENGATE_AUTH package.

For example, to create an Oracle GoldenGate Administration user, you have to grant the appropriate OGG_* role to the user while creating the user. If you still use GRANT_ADMIN_PRIVILEGE procedures from the DBMS_GOLDENGATE_AUTH package in Oracle Database 23ai, no privileges are granted. Instead, a warning message is raised alerting that the procedure call is disabled.

Here are the user roles introduced in Oracle GoldenGate 23ai:

OGG_CAPTURE

OGG_CAPTURE has the privileges necessary for using and managing Extract processes.

For capturing DML and DDL with Extract, a user requires the following permissions:

GRANT CONNECT, RESOURCE to ggadmin;
GRANT OGG_CAPTURE to ggadmin;

If the Extract process is registered at the root container, then this additional step must be taken:

ALTER USER c##ggadmin SET CONTAINER_DATA = all CONTAINER = current;

OGG_APPLY

Role with privileges necessary for using Oracle GoldenGate Replicat.

To use a Replicat processes a user needs this role, as well as the permissions to execute DML and DDL at the target. For example, if a Replicat process is intended to perform DML operations on the EMPLOYEES table from the HR schema:

GRANT CONNECT, RESOURCE to ggadmin; 
GRANT OGG_APPLY to ggadmin; 
GRANT SELECT, INSERT, UPDATE, DELETE on HR.EMPLOYEES;

If the Replicat user is intended to apply DDL operations like CREATE TABLE, DROP TABLE and ALTER TABLE it should receive the system privileges necessary to execute such statements:

GRANT CREATE TABLE, ALTER TABLE, DROP TABLE to ggadmin;

OGG_APPLY_PROCREP

Role with privileges necessary to execute packages supported for procedural replication with Oracle GoldenGate. It only includes the execution permissions, therefore, this role should be used together with OGG_APPLY role to allow the user to run the Replicat process and to execute the procedures at the target.

For example, if a Replicat will apply procedure executions. The grant process should be as follows:

GRANT CONNECT, RESOURCE to ggadmin;
GRANT OGG_APPLY, OGG_APPLY_PROCREP to ggadmin;

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 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.

Privileges for Capturing from Oracle Data Vault

Grant the following privileges connected as SYS user in Oracle database. These privileges are set for Extract and Replicat user credentials:

EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE ('userID','*',
GRANT_OPTIONAL_PRIVILEGES=>'*');
GRANT DV_GOLDENGATE_ADMIN, DV_GOLDENGATE_REDO_ACCESS to userID;

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 scripts:


BEGIN
DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(
REALM_NAME => 'Oracle Default Component Protection Realm',GRANTEE => 'userID',AUTH_OPTIONS => 1) ;
END ;
/
EXECUTE_DBMS_MACADM.AUTHORIZE_DDL('SYS', 'SYSTEM');

For DDL replication, grant the following as the Database Vault owner:
```
EXECUTE DBMS_MACADM.AUTHORIZE_DDL
(‘userID', ‘SCHEMA FOR DDL’);
```