• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Oracle Data Guard Security Enhancement – SYSDG Administration Privilege

by admin

Starting with Oracle 12c you can grant the ‘SYSDG’ Administration Privilege to a User so they can perform Data Guard-related operations including using Data Guard Broker. It is no longer necessary to use SYS or SYSDBA for Data Guard Administration.

Just grant this Privilege to a User in SQL*PLUS:

SQL> grant sysdg to [USER];

Example:

SQL> grant sysdg to scott;

Since this is an Administration Privilege, you will have to copy the Oracle password file to all Physical Standby Databases again after the grant succeeds. This will ensure the user is able to access the Standby Databases when they are not open. You can use this query to verify and compare the current Password file Contents for SYSDG from primary and each mounted or opened standby:

SQL> select username from v$pwfile_users where sysdg='TRUE';

Once granted you can connect to the Data Guard Configuration through SQL*PLUS or DGMGRL. For example:

SQL> connect scott/[Password]@[Database_TNS] as sysdg

Or

DGMGRL> connect scott/[Password]@[Database_TNS]

The SYSDG Administration Privilege provides access to the following SQL operations along with all DGMGRL command line operations:

  • STARTUP
  • SHUTDOWN
  • ALTER DATABASE
  • ALTER SESSION
  • ALTER SYSTEM
  • CREATE RESTORE POINT (including GUARANTEED Restore Points)
  • CREATE SESSION
  • DROP RESTORE POINT (including GUARANTEED Restore Points)
  • FLASHBACK DATABASE
  • SELECT ANY DICTIONARY (DBA_ Views)
  • SELECT
    • X$ Tables
    • V$ and GV$ Views
    • APPQOSSYS.WLM_CLASSIFIER_PLAN
  • DELETE
    • APPQOSSYS.WLM_CLASSIFIER_PLAN
  • EXECUTE
    • SYS.DBMS_DRS
NOTE: If SYSDG is not setup it will be possible for ORA-1017 and ORA-1031 errors to be logged in audit trail while using DGMGRL due to it’s attempts to try to use AS SYSDG first.

Filed Under: Data Guard, oracle, oracle 12c

Some more articles you might also be interested in …

  1. Oracle Database 18c : How to Merge Partitions And Subpartitions Online
  2. How to convert NON-OMF to OMF files ( Oracle Managed File conversion – NON-ASM )
  3. Interview Questions : Oracle Flex ASM 12c
  4. ORA-16713: The Oracle Data Guard broker command timed out When Changing LogXptMode
  5. How to Enable Fast-Start Failover using Enterprise Manager
  6. CentOS / RHEL 7 : How to set udev rules for ASM on multipath disks
  7. How to Verify if Oracle Active Data Guard is Enabled
  8. WebLogic Server Domain: How To Disable the HTTP methods other than GET and POST (such as PUT, DELETE, etc.)
  9. How to split BCV and open oracle ASM database
  10. How to Change the Default Home Page of Oracle HTTP Server

You May Also Like

Primary Sidebar

Recent Posts

  • ctags: Generates an index (or tag) file of language objects found in source files for many popular programming languages
  • csvtool: Utility to filter and extract data from CSV formatted sources
  • csvstat: Print descriptive statistics for all columns in a CSV file
  • csvsql: Generate SQL statements for a CSV file or execute those statements directly on a database

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright