My environment：OGG Version 12.3.0.1.4 for linux, Bi-direction extract/replicat application of two 12CR1 ORACLE DBs. According to installation instrument, we should run this firstly:
exec dbms_goldengate_auth.grant_admin_privilege('goldengate');
https://docs.oracle.com/goldengate/1212/gg-winux/GIORA/user_assignment.htm#GIORA552
Based on above doc.,I granted relative privileges to goldengate user. After re-running OGG, it showed some errors indicating no privileg to access v$ views, so I added corresponding privileges as well. However, it still showed some errors on replicats. So I run below code, granting dbms_ package privileges:
grant execute on DBMS_XSTREAM_GG_ADM to goldengate;
grant execute on DBMS_LOGREP_UTIL to goldengate;
Unfortunately, the new errors like below can't be resolved:
2021-05-19 19:33:15 ERROR OGG-00665 OCI Error executing single row select (status = 26944-ORA-26944: 用户 "GOLDENGATE" 尝试调用过程, 但没有正确的权限。
ORA-06512: 在 "SYS.DBMS_LOGREP_UTIL", line 569
ORA-06512: 在 "SYS.DBMS_LOGREP_UTIL", line 602
ORA-06512: 在 "SYS.DBMS_XSTREAM_GG_ADM", line 2401
ORA-06512: 在 line 1), SQL<BEGIN dbms_xstream_gg_adm.set_tag(:1); END;>.
2021-05-19 19:33:15 ERROR OGG-01668 PROCESS ABENDING.
No other way, I granted DBA role to the user, and everything restored to normal.
Below is the privileges list of the user:
select * from dba_role_privs where grantee='GOLDENGATE';
GRANTEE GRANTED_ROLE ADMIN_OPT DELEGATE_ DEFAULT_R COMMON
---------- -------------------- --------- --------- --------- ---------
GOLDENGATE RESOURCE NO NO YES NO
GOLDENGATE CONNECT NO NO YES NO
GOLDENGATE DBA NO NO YES NO
GOLDENGATE SELECT_CATALOG_ROLE NO NO YES NO
select * from dba_sys_privs where grantee='GOLDENGATE';
GRANTEE PRIVILEGE ADMIN_OPT COMMON
---------- ------------------------- --------- ---------
GOLDENGATE SELECT ANY TRANSACTION NO NO
GOLDENGATE CREATE JOB NO NO
GOLDENGATE ALTER ANY TABLE NO NO
GOLDENGATE CREATE TABLE NO NO
GOLDENGATE UNLIMITED TABLESPACE NO NO
GOLDENGATE CREATE CREDENTIAL NO NO
GOLDENGATE LOGMINING NO NO
GOLDENGATE DELETE ANY TABLE NO NO
GOLDENGATE UPDATE ANY TABLE NO NO
GOLDENGATE INSERT ANY TABLE NO NO
GOLDENGATE LOCK ANY TABLE NO NO
GOLDENGATE ALTER SYSTEM NO NO
GOLDENGATE SELECT ANY TABLE NO NO
GOLDENGATE CREATE SESSION NO NO
GOLDENGATE SELECT ANY DICTIONARY NO NO
Seemingly, we can't get rid of DBA role privilege from the user.
Question: Can we totally revoke DBA ROLE FROM GOLDENGATE user ?