Manage Credentials

You can create credentials, list credentials, or delete credentials in your Autonomous Database.

Create Credentials to Access Cloud Services

To access services in the Cloud, such as Cloud Object Store, you first need to create credentials in your Autonomous Database.

  1. Create and store credentials using the procedure DBMS_CLOUD.CREATE_CREDENTIAL. For example:
    SET DEFINE OFF
    BEGIN
      DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'DEF_CRED_NAME',
        username => 'adb_user@example.com',
        password => 'password'
      );
    END;
    /

    This operation stores the credentials in the database in an encrypted format. You can use any name for the credential name. Note that this step is required only once unless your object store credentials change. Once you store the credentials you can then use the same credential name for all data loads.

    For detailed information about the parameters, see CREATE_CREDENTIAL Procedure.

    Creating a credential to access Oracle Cloud Infrastructure Object Store is not required if you enable resource principal credentials. See Use Resource Principal to Access Oracle Cloud Infrastructure Resources for more information.

    Note

    Some tools like SQL*Plus and SQL Developer use the ampersand character (&) as a special character. If you have the ampersand character in your password use the SET DEFINE OFF command in those tools as shown in the example to disable the special character and get the credential created properly.
  2. With the credential you created in Step 1, you can access Object Store or other cloud resources from Autonomous Database using a procedure such as DBMS_CLOUD.COPY_DATA, DBMS_CLOUD.EXPORT_DATA, DBMS_CLOUD_PIPELINE if you are using a Data Pipeline, or other procedures that require DBMS_CLOUD credentials.

Create Credentials to Access Cloud Services in Cloud Shell

Use Cloud Shell to run a script that creates OCI Native Credentials and Auth Token in your Autonomous Database.

To create credentials using the DBMS_CLOUD package, see Create Credentials to Access Cloud Services and CREATE_CREDENTIAL Procedure.

About the Create Credential Cloud Shell Script

Run the Create Credential Cloud Shell script in the Cloud Shell developer tool to generate credential scripts to run in your Autonomous Database.

Use the Create Credential script, adb-create-cred.sh, to create new or to reuse existing OCI Native Credentials, including an RSA key pair with a fingerprint. The credentials are provided to the user in the form of scripts, oci_native_credential.sql and oci_native_credential.json, that can be run in your Autonomous Database. Existing credentials are backed up if new credentials are created.

Use the adb-create-cred.sh script to run the generated credential scripts in your Autonomous Database or exit the program and run the scripts in your database with a SQL or JSON compatible tool or utility of your choice. See Example: Create OCI Native Credentials and Example: Create OCI Native Credentials and Run in Autonomous Database for more information and examples.

Optionally, adb-create-cred.sh is used to create Auth Token/Swift credentials. Oracle recommends the use of OCI Native Credentials. However, if you want to create an Auth Token/Swift credential, it is supported by this shell script. See Create Auth Token Used for Swift Credential for details.

Note

If for you do not have access to the Cloud Shell developer tool, create OCI Native Credentials without using the adb-create-cred.sh script. See Create Oracle Cloud Infrastructure Native Credentials for details.

Example: Create OCI Native Credentials

This example uses Cloud Shell to run the Create Credential script to create OCI Native Credential scripts.

Run adb-create-cred.sh to generate credential scripts, oci_native_cred.sql and oci_native_cred.json. The adb-create-cred.sh script searches for existing credentials, if found you are asked if you want to reuse them or if you want new credentials created. Depending on your decision, the generated credential scripts include new or existing credentials. Download the OCI Native Credential script or copy it to run it directly in your database using any SQL or JSON tool or utility.

Note

For a list of arguments supported by the Create Credential script, enter adb-create-cred.sh --help.

In this example, OCI Native Credential scripts are generated for your tenancy without running them in your database. See Example: Create OCI Native Credentials and Run in Autonomous Database, for an example of running the OCI Native Credential script in your database.

  1. Sign into your Tenancy, select the Developer Tools icon and click Cloud Shell from the drop down list.
    Description of cs_cloud_shell.png follows
  2. Run the adb-create-cred.sh script.


    Description of cs_script_no_args.png follows

  3. Enter y to reuse existing credentials in the generated OCI Native Credential script.


    Description of cs_existing.png follows

    If you choose to create new credentials, and decide after overwriting the credentials that you want to use the overwritten credentials, go back to the directory where the backup is and reuse them. The backup file name or folder has a suffix with this syntax: _bkp_YYYYMMDD_abc. For example, this backup file was created on June 06, 2024: _bkp_20240603_woT.

  4. Enter n to exit the script without running the OCI Native Credential script in your database.


    Description of cs_do_not_run.png follows

  5. The program exits and displays the name of the OCI Native Credential and the command to view it.


    Description of cs_exit_script.png follows

    Download the OCI Native Credential script or copy it and run it directly in your database using any SQL tool.

Example: Create OCI Native Credentials and Run in Autonomous Database

This example uses the Create Credential script to create an OCI Native Credential script that is run in your Autonomous Database.

Before running the script, consider the following:
  • (Optional) Provide the database region, compartment and database name, to avoid having the script search for the database. Although these options are not required, they can save processing time, especially on tenancies spanning a multitude of compartments and Autonomous Databases.
  • The script assumes the database is in your home region. If it is in a different region, you must pass in the region name argument, --region, when running the script.
  • If you don't provide the compartment (--compartment), or database name (--database), the script searches for possible candidates and prompts you to make a selection from lists of possible compartments and databases.
  • To list available options, enter -h or --help.

In the following example, the Create OCI Native Credential script, adb-create-cred.sh, is used to generate a credential script with existing credentials and run the script in a specified database.

The database region, compartment, and name are passed as arguments to the script. The --database option requires both --region and --compartment. If you specify only --database without these, then the option is ignored.

The username used to connect to the database is also provided along with a name for the created credential.
Note

For a list of arguments supported by the Create Credential script, enter adb-create-cred.sh --help.
  1. Sign into your Tenancy, select the Developer Tools icon and click Cloud Shell from the drop down list.
    Description of cs_cloud_shell.png follows
  2. Run the adb-create-cred.sh script, including arguments used to locate the database, connect to the database and a user specified credential name.


    Description of cs_run_all_options.png follows

  3. Existing API keys and fingerprint were found. Enter y to reuse them in the generated credential scripts.


    Description of cs_existing_keys.png follows

    If you choose to create new credentials, and decide after overwriting the credentials that you want to use the overwritten credentials, you can go back to the directory where the backup is and reuse them. The backup file name or folder has a suffix with this syntax: _bkp_YYYYMMDD_abc. For example, this backup file was created on June 06, 2024: _bkp_20240603_woT.

  4. Enter y to run the created credential script, oci_native_credential.sql, in the database you specified with the region, compartment and database arguments.


    Description of cs_run_script.png follows

  5. A Wallet File was not found, so it was set up. If a Wallet File was found, you are asked if you want to reuse it or set up a new one. Enter the password for the ADMIN username you provided. The username and password are used to connect to the database.


    Description of cs_pass.png follows

    Note

    If you have an Autonomous Database private endpoint in a Virtual Cloud Network, the adb-create-cred.sh script generates the SQL and JSON scripts. However, it does not perform the steps required to access the Autonomous Database private end point. Instead, it prompts you to execute cat ~/oci_native_credential.sql to copy the SQL into whatever SQL tool you have access to via a Bastion or Jump Host.

  6. The login was successful and the MYOCICRED credential is created in the specified database. The existing credentials are dropped and the new credentials are created. Enter n to not run credentials in another database. The script exists and provides the path for the created oci_native_credential.sql script.

    Description of cs_done.png follows

    Note

    If the connection to the Autonomous Database is unsuccessful, you can run cat ~/oci_native_credential.sql, and copy the SQL to run it directly in your database using any SQL tool.

Create Auth Token Used for Swift Credential

If you want to create an Auth Token/Swift credential, though Oracle recommends the use of OCI Native Credentials, include the --all argument when running the script to generate OCI Native Credentials and the Auth Token/Swift credential.

For example:
adb-create-cred.sh --all

When this flag is used, the script asks if you want to include an Auth Token. By answering y, your Auth Token key is generated and uploaded to your OCI profile, and the oci_auth_token_credential.sql and auth_token.tok scripts are created.

There are 2 options you can use to view the Auth Token key:
  • Run the oci_auth_token_credential.sql script from the Cloud Shell, to create the Auth Token key in your database. The Auth Token key is the value of the password parameter for DBMS_CLOUD_CREATE_CREDENTIAL.
  • View auth_token.tok from the Cloud Shell. Your Auth Token is the value of token.

List Credentials

DBMS_CLOUD provides the ability to store credentials using the procedure DBMS_CLOUD.CREATE_CREDENTIAL. You can list credentials from the view ALL_CREDENTIALS.

For example, to list credentials, run the following command:

SELECT credential_name, username, comments FROM all_credentials;

CREDENTIAL_NAME                                            USERNAME    
---------------------------–-----------------------------  --------------------
COMMENTS
---------------------------–-----------------------------  --------------------
ADB_TOKEN                                                  user_name@example.com
{"comments":"Created via DBMS_CLOUD.create_credential"}
DEF_CRED_NAME                                              user_name@example.com
{"comments":"Created via DBMS_CLOUD.create_credential"}
 

See ALL_CREDENTIALS for more information.

Delete Credentials

DBMS_CLOUD provides the ability to store credentials using the procedure DBMS_CLOUD.CREATE_CREDENTIAL. You can remove credentials with DBMS_CLOUD.DROP_CREDENTIAL.

For example, to remove the credential named DEF_CRED_NAME, run the following command:

BEGIN
   DBMS_CLOUD.DROP_CREDENTIAL('DEF_CRED_NAME');
END;

For more information about the DBMS_CLOUD procedures and parameters, see DBMS_CLOUD Subprograms and REST APIs.