Forum Stats

  • 3,852,479 Users
  • 2,264,108 Discussions
  • 7,905,079 Comments

Discussions

Goldengate user privileges problem, too much privileges.

User_HM44O
User_HM44O Member Posts: 12 Green Ribbon

Hi,

im using goldengate 19.1 and oracle 19c it works great, but i have a problem that the goldengate user have so many privileges which the source database admin refuse to accept, only the tables that i have access to i can extract from.

what are the minimum privilges that goldengate user on the source(extract) user can have and still function great?

if not what are ways or alternatives that i can do ?

ps: im using goldengate as hub on separate server.

Tagged:

Best Answer

  • User_HM44O
    User_HM44O Member Posts: 12 Green Ribbon

    For anyone faces this problem found a way around (kinda), You can execute the "dbms_goldengate_auth.grant_admin_privilege" and then register and start the extract process wait a minuet after it finish initialization then you can revoke the "grant select any table to ggadmin" every thing keep working, I don't know what happens when initialization that requires "grant select any table to ggadmin" then not anymore.

    I can work with that for now.

    thanks

Answers

  • K.Gan
    K.Gan Member Posts: 2,815 Bronze Crown

    There is no general minimum as such, go to the Using Goldengate for Oracle database reference for your version and see Assigning Crtedentials to GoldenGate. I am not sure why DBAs think that OGG is an application, it is not, it is a system and DB utility. Anyway go through the list and see what you need. In any case all you need is add the credentials to the credential store, let the DBA enter the password and you never ever need to access the DB directly using the gg user.

    Whether you run OGG in a hub or otherwise is inmaterial.

  • User_HM44O
    User_HM44O Member Posts: 12 Green Ribbon
    edited Jul 30, 2022 1:46PM

    thank you very much for your quick response, I already went through the  Oracle database reference and Assigning Crtedentials to GoldenGate and I noticed that I need to execute this procedure :

    " dbms_goldengate_auth.grant_admin_privilege"

    which gives the goldengate user full permission on the database specifically :

    "grant select any table to ggadmin"

    which the source DBA refuse to grant and if revoked the goldengate extract process report shows an error "insufficient privileges".

    And for letting the DBA enter the password that might be the last option, I'll wait a few days, try somethings or someone come up with a walk around this problem.

    thank you.

  • User_HM44O
    User_HM44O Member Posts: 12 Green Ribbon

    For anyone faces this problem found a way around (kinda), You can execute the "dbms_goldengate_auth.grant_admin_privilege" and then register and start the extract process wait a minuet after it finish initialization then you can revoke the "grant select any table to ggadmin" every thing keep working, I don't know what happens when initialization that requires "grant select any table to ggadmin" then not anymore.

    I can work with that for now.

    thanks

  • K.Gan
    K.Gan Member Posts: 2,815 Bronze Crown

    I would use the revoke any table with caution and certainly not do this in production. Go to ggsci dblogin to the database, then select sid, serial#, program from v$session. You will see ggsci in there. Set trace on this session and in ggsci do register ext any database and unregister it again. Exit ggsci. I did a trace myself and you will see stacks of inserts into sys tables for the register and corresponding deletes for the unregister. If you revoke the select, extract might return no rows or tables not found on data it needs. This can lead to incorrect checkpointing or even logminer reads.