Oracle Cloud Infrastructure Documentation

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

Note

For Oracle Cloud 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:

Perform Prerequisite Tasks and Obtain Required Permissions

Here's a list of typical tasks that must be performed before setting up preferred credentials.

  1. The Database Administrator creates the database users for whom the preferred credentials will be set:
    • Monitoring user
      Note

      You can use the DBSNMP user as the monitoring user, and this is a convenient option as the DBSNMP user is built-in with the Oracle Database and has the privileges required to monitor databases in Oracle Cloud Infrastructure. In place of the DBSNMP 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.
    • 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

    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 information on how to create user accounts, see Creating User Accounts in Oracle Database Security Guide.

  2. 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 minimum DBMGMT_MANAGED_DB_UPDATE permission to a user group or grant broad level permissions using the use or manage verbs and the dbmgmt-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 Database Management.

Set Preferred Credentials in Database Management

You can set the Advanced diagnostics and Administration preferred credential in Database Management.

Note

The Basic monitoring credential is set when Database 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: Update the database credentials specified when enabling Database Management for the Oracle Cloud Database:
    1. Go to the Database Management Administration Managed databases page.
    2. On the left pane, select the compartment in which the database resides and the deployment type of the database.
    3. Click the Actions icon (Actions) for the database and click Edit Database Management.
    4. In the Edit Database Management panel, update the database credentials specified for the connection to the selected Oracle Cloud Database.

To set the Advanced diagnostics and Administration preferred credentials in Database Management:

  1. Go to the Managed database details page and on the left pane under Resources, click Credentials.
    The Preferred credentials tab is displayed and you can set the preferred credentials and view details such as the status of the preferred credentials, whether access is enabled or not, the user name and role set in the preferred credentials, and the associated named credential, if any.
  2. Click the Actions icon (Actions) for the preferred credential that you want to set, and click Edit.
  3. In the Edit preferred credential panel, select one of the following options in the Credential type drop-down list to set the preferred credential:
    • Named credentials: Select this option to associate the preferred credential with an existing named credential and click the Arrow icon to the left of Active credential:
      Note

      If a named credential is in use (active) and is set as the session credential for the Managed Database, then the name of the named credential is displayed adjacent to Active.
      1. Scope: Select the scope of the named credential:
        • Resource: A named credential with the Resource scope can be used to access, monitor and manage a single Managed Database.
        • Global: A named credential with the Global scope can be used to access, monitor and manage all the Managed Databases in Database Management.
      2. Named credential: Select the named credential. If the compartment in which the named credential resides is different from the compartment displayed, click Change compartment and select another compartment.
    • New credential: Select this option to create a new credential:
      1. User name: Specify the database user name to connect to the Managed Database.
      2. User password secret: Select the secret that contains the database user password from the drop-down list. Note that the user password cannot be directly stored in the preferred credential and it must first be stored in a Vault service secret. If the compartment in which the secret resides is different from the compartment displayed, click Change compartment and select another compartment.

        If an existing secret with the database user password is not available, then select Create new secret... in the drop-down list. For information on the permission required to create a secret and how to create a secret, see Perform Prerequisite Tasks and Obtain Required Permissions.

      3. Role: Select the role from the available options, Normal or SYSDBA.
      4. Save as new named credential: Optionally, select this check box and specify a name for the named credential and one of the following password access mode options to save the new credential as a named credential.
        • User: The permission to access the password secret is defined for a user in the policy.
        • Resource: The permission to access the password secret is defined for the type of resource (for which the named credential is created) in the policy.

    For information on named credentials, see Create and Manage Named Credentials.

  4. Optionally, click Test to check whether the connection to the Managed Database is established successfully using the credentials.
  5. Click Save to save the credentials.
On the Preferred credentials tab, you can click the name of the credential to view details such as the user name and password secret, if the credential is set. You can also click the Actions icon (Actions) for a preferred credential to edit, view, or clear the preferred credential.
Note

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

Here's a table that lists the tasks that can be performed with the Advanced diagnostics and Administration preferred credentials.

Note

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.