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 theEMPLOYEES
table from theHR
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 likeCREATE TABLE
,DROP TABLE
andALTER 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.
Privilege | Extract | Replicat All Modes | Purpose |
---|---|---|---|
|
Yes |
Yes |
Required to create objects In Oracle Database 12cR1 and later, instead of
|
|
Yes |
Yes |
Common user See an example of Permissions granted to an Oracle mutitenant database common user. |
|
Yes |
Yes |
Required to connect to the database. |
|
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. |
|
Yes |
Yes |
Perform administrative changes, such as enabling logging. |
|
Yes |
Yes |
Required for multitenant architecture and
|
|
Yes | Yes |
|
Grant |
Yes |
Yes |
Capture from Data Vault. See Privileges for Capturing from Oracle Data Vault. |
Grant Replicat privileges in
|
NA | Yes |
Capture from Data Vault. See Privileges for Capturing from Oracle Data Vault. |
|
NA |
Yes |
Apply replicated DML to target objects. See Details of Support for Objects and Operations in Oracle DML |
|
NA |
Yes |
Grant these privileges to the Replicat user, instead of granting
|
If DDL replication is performed, grant the following as Database Vault owner:
|
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. |
|
Yes | Yes | Grants privileges for DDL Replication for tables. |
|
Yes |
Yes |
Grants privileges for creating table in any schema. To allow creating tables
only in a specific schema, use the |
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.
|
|
Yes |
Yes |
Allow all privileges to work properly on dictionary tables. |
Example: Permissions granted for the Oracle database common user
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 theGRANT 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’);