New roles have been created in the Database 23ai for granting appropriate capture and apply privileges to the GoldenGate and XStream administrators. These new roles replace the functionality in the procedures of the DBMS_GOLDENGATE_AUTH and DBMS_XSTREAM_AUTH packages, which are now de-supported in 23ai database.

There are 3 new roles in the Oracle Database 23ai:

• OGG_CAPTURE

This role has the privileges necessary for using and managing Extract processes. In addition, GoldenGate user also needs privileges for DDL and DML for each object.

If you would like to see what privileges are granted to OGG_CAPTURE, feel free to run the SQL below:

select privilege from role_sys_privs where role = 'OGG_CAPTURE'
union all
select privilege||' on '|| owner||'.'||table_name from role_tab_privs where role = 'OGG_CAPTURE';

• OGG_APPLY

This role has the privileges necessary for using Oracle GoldenGate Replicat. In addition, GoldenGate user also needs privileges for DDL and DML for each object.

If you would like to see what privileges are granted to OGG_APPLY, feel free to run the SQL below:

select privilege from role_sys_privs where role = 'OGG_APPLY'
union all
select privilege||' on '|| owner||'.'||table_name from role_tab_privs where role = 'OGG_APPLY';

• OGG_APPLY_PROCREP

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

If you would like to see what privileges are granted to OGG_APPLY_PROCREP, feel free to run the SQL below:

select privilege from role_sys_privs where role = 'OGG_APPLY_PROCREP'
union all
select privilege||' on '|| owner||'.'||table_name from role_tab_privs where role = 'OGG_APPLY_PROCREP';

https://docs.oracle.com/en/middleware/goldengate/core/23/coredoc/prepare-oracle.html#GUID-9253C65D-5242-42C2-9CD9-6CBCD669273F