Set Preferred Credentials
You can set preferred credentials to connect to the Managed Database and perform specific tasks.
Preferred credentials simplify access to the Managed Database by using the database credentials stored in an Oracle Cloud Infrastructure Vault service secret. Preferred credentials can be used to provide default connectivity to the database based on user roles and the tasks to be performed, thereby allowing the separation of duties for different user groups and providing another layer of security. In Database Management, you can set the following preferred credentials for users so they can connect to the Managed Database and perform the associated set of tasks:
-
Basic monitoring: Minimum privileges to collect metrics and view the database fleet summary and Managed Database details. The Basic monitoring credential is set automatically for the monitoring user when Database Management Diagnostics & Management is enabled.
-
Advanced diagnostics: Advanced privileges to use diagnostic tools such as Performance Hub and AWR Explorer. If the Advanced diagnostics credential is set for a Managed Database, then it can be used to automatically use diagnostic features and for the read operations in the Managed Database.
-
Administration: Management privileges to perform administrative tasks such as creating tablespaces and editing database parameters. 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 Oracle Cloud Databases and Autonomous Databases, the preferred credentials in Database Management are only available to users who have the permission to read the secret that stores the database user password. For more information on permissions, see Perform Prerequisite Tasks and Obtain Required Permissions.
For information on:
- Tasks you can perform with the Advanced diagnostics and Administration preferred credentials, see Additional Information on Preferred Credentials.
- Required database user privileges available with the Advanced diagnostics and Administration preferred credentials, see Database User Privileges Required for Diagnostics & Management.
Perform Prerequisite Tasks and Obtain Required Permissions
Here's a list of typical tasks that must be performed before setting up preferred credentials.
- The Database Administrator creates the database users for whom the
preferred credentials will be set:
- Monitoring user
Note
- For External Databases and Oracle Cloud Databases,
you can use the
DBSNMP
user as the monitoring user, and this is a convenient option as theDBSNMP
user is built-in with the Oracle Database and has the privileges required to monitor databases in Oracle Cloud Infrastructure. In place of theDBSNMP
user, you also have the option of using a SQL script to create a new database user with the minimum set of privileges required to monitor Managed Databases. For information on the SQL script, see Creating the Oracle Database Monitoring Credentials for Database Management (Doc ID 2857604.1) in My Oracle Support. - For Autonomous Databases, you can use the
ADBSNMP
user as the monitoring user, however, note that theADBSNMP
user does not have the privileges required to perform certain advanced monitoring and management tasks.
- For External Databases and Oracle Cloud Databases,
you can use the
- Advanced diagnostics user
Note
On creating the Advanced diagnostics user to perform Performance Hub tasks, you must ensure that the user is granted the privileges required to use Performance Hub. For information on the required privileges, see OCI: Prerequisite Conditions for Performance Hub (Doc ID 2760305.1) in My Oracle Support. - Administrator user
For External Databases and Oracle Cloud Databases, you have the option of using a SQL script to create a new database user with the set of privileges required to perform advanced diagnostics and administrative tasks. 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 Autonomous Databases, it's recommended that you use the
ADMIN
user to perform advanced diagnostics and administrative tasks.For information on how to create user accounts, see Creating User Accounts in Oracle Database Security Guide.
- Monitoring user
- An Oracle Cloud Infrastructure user with the
required permissions creates the following Vault service secrets for database user
passwords:
- Secret to store the monitoring user password
- Secret to store the Advanced diagnostics user password
- Secret to store the Administrator user password
These secrets can be created in different compartments or in the same compartment with a different or the same vault key.
Here's an example of the policy that grants a user group the permission to create secrets:
Allow group DB-MGMT-USER to manage secret-family in compartment ABC
For information on how to create a secret, see Creating a Secret in a Vault.
On performing the prerequisite tasks, a user with the following permissions can set the preferred credential in Database Management:
DBMGMT_MANAGED_DB_UPDATE
permission to set preferred credentials. You can grant the minimumDBMGMT_MANAGED_DB_UPDATE
permission to a user group or grant broad level permissions using theuse
ormanage
verbs and thedbmgmt-managed-databases
resource-type.Here's an example of a policy with the minimum permission to set preferred credentials:
Allow group DB-MGMT-USER to {DBMGMT_MANAGED_DB_UPDATE} in compartment ABC
Here's an example of a broad policy that grants a user group the permission to set preferred credentials:
Allow group DB-MGMT-USER to use dbmgmt-managed-databases in compartment ABC
For more information on Database Management resource-types and permissions, see Policy Details for Database Management.
-
Permission to read the secret that stores the database user password. Here's an example of the policy that grants a user group the permission to create secrets:
Allow group DB-MGMT-USER to read secret-family in compartment ABC
If you want to grant the permission to read secrets only from a specific vault, then update the policy to:
Allow group DB-MGMT-USER to read secret-family in compartment ABC where target.vault.id = 'Vault OCID'
For more information on the Vault service permissions required to access and use the secrets, see Additional Permissions Required to Use Diagnostics & Management.
Set Preferred Credentials in Database Management
You can set the Advanced diagnostics and Administration preferred credential in Database Management.
The Basic monitoring credential is set when Database Management Diagnostics & Management is enabled, however, you can update the Basic monitoring credential. To update the Basic monitoring credential for:
- External Databases: Update the database credentials specified when creating the connection to the External Database. For information, see Update the Connection Credentials of an External Database Connection.
- Oracle Cloud Databases and Autonomous Databases: Update the
database credentials specified when enabling Database Management for the Oracle Cloud Database:
- Go to the Database Management Administration Managed databases page.
- On the left pane, select the compartment in which the database resides and the deployment type of the database.
- Click the Actions icon (
) for the database and click Edit Database Management.
- In the Edit Database Management panel, update the database credentials specified for the connection to the selected Oracle Cloud Database.
If you enabled Diagnostics & Management for Autonomous Databases in Database Management prior to the availability of the advanced monitoring support or on the Autonomous Database details page, then too you must set the Basic monitoring credential following the procedure given above.
To set the Advanced diagnostics and Administration preferred credentials in Database Management:
![Actions Actions](img/actions.png)
- If the Administration preferred credential is set, then it's autofilled when you perform tasks such as creating a job or tablespace and you have the option of either using the Administration preferred credential or providing new credentials. However, if the Administration preferred credential is set for the run on-demand ADDM task in Performance Hub or the kill sessions task in Performance Hub, then the Administration preferred credential is selected automatically.
- If a session credential is set and a preferred credential is also set using different user credentials, then the session credential takes precedence over preferred credentials and is automatically selected when you perform tasks and you have the option of using the session credential, selecting the preferred credential, or providing new credentials. If a session credential is set, then it's used to run the on-demand ADDM task or to kill sessions in Performance Hub. For information on session credentials, see Set Session Credentials.
- If preferred credentials are not set, then:
- Monitoring user is used for basic monitoring.
- Session credential can be set to be used in a particular session.
- Database credentials must be specified when performing write operations.
Permissions Required to Use Preferred Credentials to Perform Tasks
Here are a couple of sample scenarios that list the IAM policies, which grant the permissions required to use preferred credentials. In the scenarios, it's assumed that:
- The preferred credentials were set by an Oracle Cloud Infrastructure user with the required permissions.
- The preferred credentials were set for the following database users and
these users have to be granted the Oracle Cloud Infrastructure
permissions to view the preferred credential and perform the associated set of
Diagnostics & Management tasks in Database Management.
- Advanced diagnostics user
- Administrative user
For information on the prerequisite tasks and permissions required to set preferred credentials, see Perform Prerequisite Tasks and Obtain Required Permissions.
Scenario 1: If the Advanced diagnostics preferred credential is set for the Advanced diagnostics user, then this user requires the following permissions to view the preferred credential and perform the associated set of tasks:
- Database Management permission to perform the
Diagnostics & Management task. For example, to view tablespaces, the Advanced
diagnostics user will require the
DBMGMT_MANAGED_DB_READ
permission.Here's an example of the policy that grants a user group the permission to view tablespaces:
Allow group DB-MGMT-USER to read dbmgmt-managed-databases in compartment ABC
- Vault service permission to read the secret that contains the Advanced
diagnostics user password.
Here's an example of the policy that grants a user group the permission to read secrets:
Allow group DB-MGMT-USER to read secrets in compartment ABC
Scenario 2: If the Administration preferred credential is set for the Administrator user, then this user requires the following permissions to view the preferred credential and perform the associated set of tasks:
- Database Management permission to perform the
Diagnostics & Management task. For example, to create tablespaces, the
Administrator user will require the
DBMGMT_MANAGED_DB_CONTENT_WRITE
permission.Here's an example of the policy that grants a user group the permission to create tablespaces:
Allow group DB-MGMT-USER to use dbmgmt-managed-databases in compartment ABC
- Vault service permission to read the secret that contains the
Administrator user password.
Here's an example of the policy that grants a user group the permission to read secrets:
Allow group DB-MGMT-USER to read secrets in compartment ABC
For information on the Database Management permissions required to perform each task, see Policy Details for Database Management.
Additional Information on Preferred Credentials
Here's a table that lists the Diagnostics & Management tasks that can be performed with the Advanced diagnostics and Administration preferred credentials.
If a session credential is set and a preferred credential is also set using different user credentials, then the session credential takes precedence over preferred credentials and is automatically selected when you perform tasks and you have the option of using the session credential, selecting the preferred credential, or providing new credentials. If a session credential is set, then it's used to run the on-demand ADDM task or to kill sessions in Performance Hub. For information on session credentials, see Set Session Credentials.
Preferred Credential | Tasks |
---|---|
Advanced diagnostics | Perform all Performance Hub tasks except run on-demand
ADDM and kill session, which require the Administration preferred
credential.
For information, see Performance Hub Features. |
Perform all AWR Explorer-related tasks.
For information, see Use AWR Explorer to Analyze Database Performance. |
|
View alert logs and attention logs.
For information, see View Alert Logs. |
|
Perform all read-only SQL tuning tasks such as viewing
SQL tuning tasks, SQL tuning sets, and SQL Tuning Advisor findings and
recommendations.
For information, see Analyze SQL with SQL Tuning Advisor. |
|
List SQL tuning sets and view SQL tuning set details.
For information, see Manage SQL Tuning Sets. |
|
View optimizer statistics summary and tasks and
Optimizer Statistics Advisor summary and tasks.
For information, see Monitor and Analyze Optimizer Statistics. |
|
Perform all read-only SPM tasks such as viewing SQL plan
baselines, SQL plan baseline configuration details, and the jobs
submitted to load SQL plan baselines.
For information, see Use SPM to Manage SQL Execution Plans. |
|
View tablespaces.
For information, see Monitor and Manage Tablespaces and Datafiles. |
|
View users and user details.
For information, see View Users. |
|
View database parameters.
For information, see View and Edit Database Parameters. |
|
Administration | Kill sessions in Performance Hub.
For information, see Terminate a Session. |
Run on-demand ADDM tasks in Performance Hub.
For information, see Run an ADDM Task. |
|
Implement SQL Tuning Advisor recommendations.
For information, see Analyze SQL with SQL Tuning Advisor. |
|
Create, load, and delete SQL tuning sets.
For information, see Manage SQL Tuning Sets. |
|
Implement Optimizer Statistics Advisor
recommendations.
For information, see Monitor and Analyze Optimizer Statistics. |
|
Perform SPM configuration tasks and other tasks such as
loading SQL plan baselines.
For information, see Use SPM to Manage SQL Execution Plans. |
|
Create, edit, and delete tablespaces and datafiles.
For information, see Monitor and Manage Tablespaces and Datafiles. |
|
Edit database parameters.
For information, see View and Edit Database Parameters. |
|
Create jobs.
For information, see Create a Job. |