One of the biggest issues with Oracle GoldenGate over the years has been the database permissions needed for the GoldenGate Admin to use the product. Over the years, many users, admins, and consultants have tried to write scripts that would alleviate this concern. Ultimately, some manager or DBA would say “screw it” and grant the SYSDBA role to the GoldenGate user just to ensure that everything worked as expected.

Outside of assigning SYSDBA to the GoldenGate admin, many others and I have scripts that look like this:

For Oracle Database 12c through 21c (CDB/PDB configuration):

--Run from the CDB layer —

create user C##GGATE
identified by ***************
default tablespace USERS
temporary tablespace TEMP
quota unlimited on USERS
account unlock;

grant connect to c##ggate;

grant dba to c##ggate;

grant resource to c##ggate;

grant alter any table to c##ggate;

grant alter session to c##ggate;

grant alter system to c##ggate;

grant create any edition to c##ggate;

grant create evaluation context to c##ggate;

grant create job to c##ggate;

grant create rule to c##ggate;

grant create rule set to c##ggate;

grant create session to c##ggate;

grant dequeue any queue to c##ggate;

grant drop any edition to c##ggate;

grant execute any rule set to c##ggate;

grant flashback any table to c##ggate;

grant insert any table to c##ggate;

grant logmining to c##ggate;

grant select any dictionary to c##ggate;

grant select any table to c##ggate;

grant select any transaction to c##ggate;

grant unlimited tablespace to c##ggate;

--Modify this line for the correct PDB—

begin
  SYS.DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('C##GGATE', container=>'ALL’);
end;
/

As you can tell, although the roles of CONNECT, DBA, and RESOURCE were granted; there were still another 19 grants that needed to be provided to the GoldenGate user at the CDB level.

At the PDB level, the following needs to be granted to the GoldenGate User:

--Modify this line for the correct PDB—

alter session set container = OGGTST;

grant connect to c##ggate;

grant dba to c##ggate;

create user GGATE
identified by ***************
default tablespace USERS
temporary tablespace TEMP
quota unlimited on USERS
account unlock;

grant connect to ggate;

grant dba to ggate;

This meant that the Common GoldenGate (C##GGATE) user needed access to the underlying PDB plus a separate GoldenGate user (GGATE) was needed for the Replicat to apply to the PDB. Plus, the local GoldenGate user was granted the CONNECT and DBA roles. In both cases, granting the DBA role to the GoldenGate common user and local user was a potential security issue.

Starting on Oracle GoldenGate 23ai, the product team has finally done something about this! I, for one, am happy that they made some changes to help mitigate this problem. Sadly, these changes only work with Oracle Database 23ai. Meaning that if you use Oracle GoldenGate 23ai against an earlier version of the Oracle Database (12c – 21c), you will still use a script similar to what I showed previously.

Moving towards 23ai

Starting in Oracle Database 23ai, the database now provides Oracle GoldenGate roles that you can grant to a GoldenGate user. Additionally, in 23ai you can now do per-PDB capture, which makes simplifies the configuration back to just a single GoldenGate user.

The roles that Oracle is now providing for Oracle GoldenGate 23ai are as follows:

OGG_CAPTURE – privileges necessary for using and managing Extract processes.
OGG_APPLY – privileges necessary for using and managing Replicat processes.
OGG_APPLY_PROCREP – privileges necessary to execute packages supported for procedural replication.

OGG_CAPTURE

For the GoldenGate user to capture from the PDB, the following example shows how to configure the user:

GRANT CONNECT TO GGATE;

GRANT RESOURCE TO GGATE;

GRANT OGG_CAPTURE TO GGATE;

If the GoldenGate user is configured against a CDB, then an ALTER USER command must be executed as well:

ALTER USER C##GGATE SET CONTAINER_DATA=ALL CONTAINER=CURRENT;

Note: I tried this, and it works only from the AdminClient. The HTML5 pages will not work for setting this permission.

OGG_APPLY

For the GoldenGate user to apply transactions to the database, the following example shows how to configure the user:

GRANT CONNECT TO GGATE;

GRANT RESOURCE TO GGATE;

GRANT OGG_APPLY TO GGATE;

GRANT SELECT, INSERT, UPDATE, DELETE ON {schema}.{table} to GGATE;
…
…
GRANT SELECT, INSERT, UPDATE, DELETE ON {schema}.{table} to GGATE;

If the GoldenGate user needs to do DDL operations, then the appropriate permissions must be set as well. Here is an example:

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

OGG_APPLY_PROCREP

The last permission that can be assigned is related to a feature that came out in Oracle GoldenGate 12.3.0.1, called Procedural Replication. Procedural Replication is cool and may be another blog post later. In the meantime, this role should be assigned in conjunction with OGG_APPLY if there is a need for the user that runs the Replicat to execute procedures on the target platform.

An example of granting this role is as follows:

GRANT CONNECT, RESOURCE TO GGATE;

GRANT OGG_APPLY, OGG_APPLY_PROCREP TO GGATE;

Summary

With Oracle GoldenGate 23ai there are a few good things that come with the release. One of which is the database roles that can be assigned to make the configuration simpler, although it is only in Oracle Database 23ai. With the attempt to make it simpler, I feel that Oracle made it both complex and simpler. What I mean is that assigning the OGG_APPLY role only gets you so far. If you do not assign the DML permissions on a per table basis, you will run into “ORA-41900: missing privilege”.

Possible Workaround

With the new way of assigning permissions with Oracle GoldenGate 23ai, there may be a workaround to solving the DML permissions problem. By lumping all the required permission into a separate role, you may be able to simplify the assignment process. Just my opinion and haven’t tried it yet.