Forum Stats

  • 3,751,799 Users
  • 2,250,415 Discussions
  • 7,867,596 Comments

Discussions

Can we totally revoke DBA ROLE FROM GOLDENGATE user ?

User_F8W33
User_F8W33 Member Posts: 18 Blue Ribbon

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 ?

Comments

  • User51642 Yong Huang
    User51642 Yong Huang Member Posts: 145 Blue Ribbon

    I'm curious about this too but I feel comfortable with granting DBA to the user.

    If you really want to find out, maybe you can do a binary elimination: revoke half of the granted privileges and see if it works, and either grant back and revoke the half of the other half, or revoke the half of this half, depending on whether it works, and repeat.