Oracle Cloud Infrastructure Documentation

Database User Privileges Required for Diagnostics & Management

This section provides the list of database user privileges required to perform advanced diagnostics and administrative tasks for External Databases and Oracle Cloud Databases.

In Diagnostics & Management, you can set the following preferred credentials to provide default connectivity to the database based on user roles and the tasks to be performed:

  • Basic monitoring
  • Advanced diagnostics
  • Administration

The Basic monitoring credential is set automatically for a monitoring user such as DBSNMP, when Diagnostics & Management is enabled. It includes the minimum privileges required to collect metrics and view the database fleet summary and Managed Database details. The Advanced diagnostics and Administration preferred credentials include advanced and management privileges to use diagnostic tools and perform administrative tasks. The topics in this section list the user privileges required to perform these tasks.

For External Databases and Oracle Cloud Databases, you have the option of using SQL scripts to create a user with the set of privileges required to perform monitoring, advanced diagnostics and administrative tasks:

  • For information on the SQL script to create a monitoring user with the minimum set of privileges, see Creating the Oracle Database Monitoring Credentials for Database Management (Doc ID 2857604.1) in My Oracle Support.
  • For information on the SQL script to create a user with the set of privileges required to perform advanced diagnostics and administrative tasks, see Creating the Oracle Database Management Advanced Diagnostics User and Administration User (Doc ID 2978493.1) in My Oracle Support.

For information on preferred credentials, see Set Preferred Credentials.

Advanced Diagnostics Credential – User Privileges

The following table lists the database user privileges available as part of the Advanced diagnostics credential. If the Advanced diagnostics credential is set for a Managed Database, then it can be used to automatically use diagnostic tools such as Performance Hub and AWR Explorer and for the read operations in the Managed Database. For a list of the tasks that can be performed with the Advanced diagnostics preferred credential, see Additional Information on Preferred Credentials.

Diagnostics & Management Feature Required Privileges

Performance Hub

 
CREATE PROCEDURE
SELECT ANY DICTIONARY
SELECT_CATALOG_ROLE
EXECUTE ON DBMS_WORKLOAD_REPOSITORY
CREATE SESSION

AWR Explorer

Alert log

 
SELECT ON VW_X$DIAG_ALERT_EXT

SQL tuning advisor

 
SELECT_CATALOG_ROLE
SELECT ANY DICTIONARY

SQL tuning sets

 
SELECT_CATALOG_ROLE
ADMINISTER ANY SQL TUNING SET

SQL plan management

 
READ ON
  • SYS.DBA_SQL_MANAGEMENT_CONFIG
  • SYS.V_$SYSAUX_OCCUPANTS
  • SYS.V_$SYSTEM_PARAMETER2
  • SYS.DBA_ADVISOR_PARAMETERS
  • SYS.DBA_AUTOTASK_CLIENT
  • SYS.DBA_SQL_PLAN_BASELINES
  • SYS.DBA_SCHEDULER_JOBS
  • SYS.V_$SQL

Search SQLs
  • To list SQL statements in cursor cache:
    • For CDBs and PDBs
      READ ON V_$SQL
    • For non-CDBs
      READ ON GV_$SQL
  • To list SQL statements in AWR snapshots:
    • For CDBs and PDBs
      READ ON CDB_HIST_SQLSTAT
      READ ON CDB_HIST_SQLTEXT
      READ ON CDB_HIST_SNAPSHOT
    • For non-CDBs 
      READ ON DBA_HIST_SQLSTAT
      READ ON DBA_HIST_SQLTEXT
      READ ON DBA_HIST_SNAPSHOT
  • To list SQL statements in AWR baseline:
    • For CDBs and PDBs 
      READ ON CDB_HIST_SQLSTAT
      READ ON CDB_HIST_BASELINE
      READ ON DBA_HIST_SQLTEXT
    • For non-CDBs 
      READ ON DBA_HIST_SQLSTAT
      READ ON DBA_HIST_BASELINE
      READ ON DBA_HIST_SQLTEXT
  • To list SQL statements in a SQL tuning set:
    • For CDBs and PDBs
      READ ON CDB_SQLSET_STATEMENTS
    • For non-CDBs
      READ ON DBA_SQLSET_STATEMENTS

In addition, the READ privilege is required for the following view irrespective of the data source: 

SYS.AUDIT_ACTIONS

Search sessions

To list sessions:

  • READ ON V_$SESSION
  • READ ON GV_$SESSION

To enable or disable SQL trace:

EXECUTE ON DBMS_MONITOR

Optimizer statistics

 
ANALYZE ANY
ANALYZE ANY DICTIONARY

Backup

 
SELECT ON
  • V_$FLASHBACK_DATABASE_LOG
  • V_$RMAN_BACKUP_JOB_DETAILS

Tablespaces

 
SELECT ANY DICTIONARY

Users

 
READ ON
  • DBA_USERS
  • DBA_ROLE_PRIVS
  • DBA_TAB_PRIVS
  • DBA_SYS_PRIVS
  • DBA_RSRC_CONSUMER_GROUP_PRIVS
  • PROXY_USERS
  • DBA_CONTAINER_DATA

Database parameters

 
SELECT ON
  • V_$PARAMETER
  • V_$SPPARAMETER
  • V_$SYSTEM_PARAMETER
  • V_$SYSTEM_PARAMETER2
  • V_$PARAMETER_VALID_VALUES
  • GV_$INSTANCE
  • GV_$SPPARAMETER
  • GV_$SYSTEM_PARAMETER
  • GV_$SYSTEM_PARAMETER2

Administration Credential – User Privileges

The following table lists the database user privileges available as part of the Administration credential (in addition to the privileges available for the Advanced diagnostics credential). If the Administration credential is set for a Managed Database, then it can be used to autofill database credentials to perform the write operations in the Managed Database. For a list of the tasks that can be performed with the Administration preferred credential, see Additional Information on Preferred Credentials.

Diagnostics & Management Feature Required Privileges

Performance Hub

 
ALTER SYSTEM
ADVISOR

SQL tuning advisor

 
ADVISOR
CREATE JOB
CREATE SESSION
INHERIT ANY PRIVILEGES
ADMINISTER SQL TUNING SET

SQL tuning sets

 
ADMINISTER ANY SQL TUNING SET

SQL plan management

 
ALTER SYSTEM
ADMINISTER SQL MANAGEMENT OBJECT
EXECUTE ON
  • SYS.DBMS_SPM
  • SYS.DBMS_AUTO_TASK_ADMIN
  • SYS.DBMS_SCHEDULER

Note: SYSDBA user privileges are required to edit the Automatic SPM Evolve Advisor task parameters.

Search sessions

To terminate a session:

ALTER SYSTEM

Tablespaces

 
CREATE TABLESPACE
ALTER TABLESPACE
DROP TABLESPACE
MANAGE TABLESPACE
ALTER DATABASE

Note: SYSDBA system privileges are required to create the SYSAUX tablespace.

Database parameters

ALTER SYSTEM

Note: SYSDBA system privilege is required to edit static parameters.