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 Managed 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 Autonomous Databases, you can use the ADBSNMP user as the monitoring user, however, it's recommended that you set the Advanced diagnostics preferred credential to use the ADMIN user, or set the session credential to use the ADMIN user as the ADBSNMP user does not have the privileges required to perform certain advanced monitoring and management tasks.

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

Note: This is not applicable for Autonomous Databases.

SQL tuning advisor

 
SELECT_CATALOG_ROLE
SELECT ANY DICTIONARY

SQL tuning sets

 
SELECT_CATALOG_ROLE
ADMINISTER ANY SQL TUNING SET

Optimizer statistics

 
ANALYZE ANY
ANALYZE ANY DICTIONARY

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

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. This is not applicable for Autonomous Databases.

Tablespaces

 
CREATE TABLESPACE
ALTER TABLESPACE
DROP TABLESPACE
MANAGE TABLESPACE
ALTER DATABASE

Note: SYSDBA system privileges are required to create the SYSAUX tablespace. This is not applicable for Autonomous Databases.

Database parameters

ALTER SYSTEM

Note: SYSDBA system privilege is required to edit static parameters. This is not applicable for Autonomous Databases.