• 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. How To Change Timezone for Oracle Grid Infrastructure
  2. Oracle Database 19c: RMAN-06012: channel: d1 not allocated
  3. Oracle RAC: How to modify private hostname, Private network IP & MTU
  4. Oracle Database 12.2 RMAN Cross Platform Tablespace Transport Over Network
  5. Simple Steps to use LogMiner for finding high redo log generation
  6. How to limit access to oracle database so that only 1 user per schema is connected ( 1 Concurrent user per schema)
  7. How to Disable os-prober in CentOS/RHEL 7
  8. How to create password file for Database on 12c ASM diskgroup
  9. CRSCTL Command Examples in ASM Standalone Configurations
  10. RMAN backup Concepts for Oracle RAC

You May Also Like

Primary Sidebar

Recent Posts

  • nixos-rebuild Command Examples in Linux
  • nixos-option: Command Examples in Linux
  • nixos-container : Command Examples in Linux
  • nitrogen Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright