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.

Oracle Cloud Infrastructure Documentation

Advanced Diagnostics Credential – User Privileges

Administration Credential – User Privileges