Oracle Cloud Infrastructure Documentation

Database User Privileges Required for Database 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 Database Management.

Note

This section and its topics detail the privileges required to use the Database Management features available for External Databases and Oracle Cloud Databases. This information is not applicable if you're using Database Management for Autonomous Databases.

In Database 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 Database Management is enabled. It includes the minimum privileges required to collect metrics and view the database fleet summary and Managed Database details. You also have the option of using a SQL script to create a monitoring user with the minimum set of privileges required to monitor Managed Databases in Database Management. For information on the SQL script, see Creating the Oracle Database Monitoring Credentials for Database Management (Doc ID 2857604.1) in My Oracle Support.

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. You also have the option of using a SQL script to create a user with the set of privileges required to perform advanced diagnostics and administrative tasks for Managed Databases in Database Management. For information on the SQL script, 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 in Database Management.

Database 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

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 in Database Management.

Database 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.

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.