Skip to Main Content
  • Questions
  • Restrict access to monitor standby database in 11.2.0.4

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Marcelo.

Asked: January 20, 2016 - 7:07 pm UTC

Last updated: January 21, 2016 - 2:19 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Dear Tom.
So we are having to review here our strategies to monitor our remote physical standby database due audit requirements.

The first strategy that I think, is to create a simple user "dgmon", allowing access only to V_$MANAGED_STANDBY, V_$ARCHIVE_GAP, V_$INSTANCE, V_$DATABASE and V_$ARCHIVED_LOG. The access attempts made on our primary database has worked properly, however, we have faced the ORA-01033 error on standby database (Oracle Initialization startup or shutdown in progress).

Reading some forums here, I have found that access in a MOUNT database is only possible in SYSDBA or SYSOPER mode. We cannot use SYSDBA to run our automated monitoring scripts (as told by our auditors), but SYSOPER, according to them, can be used. However, I cannot access the V$ tables using SYSOPER, even granting access for them explicitly. I used the following queries:

GRANT SELECT ON V_$ARCHIVED_LOG TO DGMON;
GRANT SELECT ON V_$DATABASE TO DGMON;
GRANT SELECT ON V_$INSTANCE TO DGMON;
GRANT SELECT ON V_$ARCHIVE_GAP TO DGMON;
GRANT SELECT ON V_$MANAGED_STANDBY TO DGMON;

I wonder that is a known issue, because I also have read in other forums that Oracle has delivered in version 12c, a new role called SYSDG. I took a look into this role and seems that it is exactly what we need, but since Oracle has extended the free extended support until 2017, my organization has decided to postpone the upgrade project to 12c for next year.

Can you kindly advise how we solve our audit problem using 11g resources?
Thanks a lot.

and Connor said...

I dont know of anything you can do in 11g about that. Yes, you are right, the 12c changes address that need.

But I'd be going back to the auditors and challenging their requirement. DataGuard already means you've got sysdba connections going on behind the scenes (simply because log shipping requires it, as does the broker).

So its not as if your monitoring scripts are the only thing that is using sysdba here. And ultimately, a monitoring script may one day want to take remedial action, so a sysdba connection may be needed anyway. Perhaps use that as a justification.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Marcelo, January 21, 2016 - 9:49 pm UTC

Hey Connor.... thanks for your FUP. :-)
Well, actually, the main concern of the auditors here is, since a automated user can log on into standby database as SYSDBA, it will be so easy to logon also in PRIMARY DB with same privilege by an malicious / unauthorized user (even using external authentication by OPS$ to run the monitoring scripts as sysdba).

But I think that it will not have so much alternatives based on current scenario. After the upgrade to 12c this audit requirement may be reachable. So I will suggest here internally the Oracle Database Upgrade to 12c as as action plan to solve this "non-compliance", so our script can log on to database "as SYSDG".

Good to know that I am so wrong based on a expert analysis :-)


Best regards,