Store Your Credentials in a Secret When Making External Calls from Your Autonomous Database

July 11, 2023 | 10 minute read
Can Tuzla
Principal Product Manager
Text Size 100%:
Update 10/10/2023: You can store your credentials not only in OCI Vault but also in Azure Key Vault, AWS Secrets Manager, or GCP Secret Manager now! This blog has been updated to include an example for Azure Key Vault in addition to OCI Vault. See our documentation for more details.


Oracle Autonomous Database Serverless (ADB-S) offers a seamless user experience when interacting with external data sources, calling web services, or sending emails as we have seen in my previous blog posts.  Creating database links to other Oracle or non-Oracle databases, loading data or querying external tables from various cloud object stores, making UTL_HTTP or UTL_SMTP calls are some examples for such use cases. Whether you want to create a database link or make a web service call, there is one thing in common, the authentication. This authentication could mean authenticating as a database user that exists in the target database for a database link or HTTP/SMTP authentication depending on your use case. However, you need a username/password pair for all these cases (unless you use resource principal or native authentication for object store access).  Creating and storing these credentials in Autonomous Database is handled by the DBMS_CLOUD.CREATE_CREDENTIAL procedure. In this blog post, we are going to explore how we are taking the capabilities of this procedure to the next level that lets us store our passwords for such use cases in OCI Vault Secret (or Azure Key Vault, AWS Secrets Manager, GCP Secret Manager) as opposed to storing them in the database.

Before we get going with our detailed walk through of this new feature, I’d like to emphasize the advantages it provides for ADB-S users:

  • No need to replicate your passwords in your ADB-S instances! You can store these passwords in a secret on OCI, Azure, AWS or GCP that can be pointed by all your ADB-S instances.
  • Easy and efficient password rotation! You can simply rotate your passwords directly in the vault or the secret manager, which will then be automatically reflected in your ADB-S instances (note that ADB refreshes the credential object every 12 hours so it can take up to 12 hours for the rotation to be reflected in the database).
  • Security! Last but not least, you get to store your passwords in your own secrets that are stored on the cloud provider of your choice, and encrypt them with your own encryption keys.

In the remaining part of this blog post, we are going to see how we can use this new feature when creating a database link between two ADB-S instances. Here’re the steps that we’ll follow:

  1. Storing the Database Link Credential in OCI Vault Secret
    • Create a secret in OCI Vault to store the password of the target database user
    • Enable the resource principal in the source ADB-S instance
    • Create a database link
       
  2. Storing the Database Link Credential in Azure Key Vault
    • Create a secret in Azure Key Vault to store the password of the target database user
    • Enable Azure service principal in the source ADB-S instance
    • Create a database link

1. Storing the Database Link Credential in OCI Vault Secret

1.1 Create a secret in OCI Vault to store the password of the target database user

To create a database link to a target database, we need the username and password for the target database (i.e. schema name and password). We’ll be storing our target database password as a secret in OCI Vault. Once we create our vault, we will create a secret that holds our target database password as follows:

Create OCI Vault secret

1.2 Enable the resource principal in the source ADB-S instance

The next step is to enable the resource principal in our source database so that it can reach our secret in OCI Vault to fetch the password of the target database user. As we already covered in an earlier blog post, we need to first create a dynamic group and a policy before enabling resource principal.

The scope of our dynamic group is limited to our source database for this example:

resource.id = 'ocid1.autonomousdatabase.oc1.us-sanjose-1.anzw*************'

We’ll also be adding the following policy that will allow our source database to access the secrets in our tenancy:

Allow dynamic-group ctuzlaDGforVault to read secret-bundles in tenancy

Finally, we will enable the resource principal in our source database:

EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();

At this point, it's worth mentioning that everyhting we have done so far is one-time only. In other words, when need to create another database link using the same credential we don't need to create a secret and enable the resource principal again.

1.3 Create a database link

Before we create our database link, we will first create our credential object for the target database:

BEGIN                                                                         
  DBMS_CLOUD.CREATE_CREDENTIAL(                                                
      credential_name => 'OCI_SECRET_CRED',
      params          => JSON_OBJECT('username'    value 'ADMIN',
                                     'region'      value 'us-sanjose-1',
                                     'secret_id'   value 'ocid1.vaultsecret.oc1.us-sanjose-1.amaa************')
  );                                                                          
END;                                                                          
/

Note that we did not provide any password when creating our credential. Instead, we specified the region and the secret OCID of the secret that stores our password.

Now we are ready to create our database link:

BEGIN
  DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
    db_link_name => 'financelink', 
    hostname => 'adb.us-sanjose-1.oraclecloud.com', 
    port => '1522',
    service_name => 'qtr*****_finance_low.adb.oraclecloud.com',
    credential_name => 'OCI_SECRET_CRED',
    directory_name => 'DBLINK_WALLET_DIR');
END;
/ 
select * from dual@financelink;

D
-
X

2. Storing the Database Link Credential in Azure Key Vault

2.1 Create a secret in Azure Key Vault to store the password of the target database user

To create a database link to a target database, we need the username and password for the target database (i.e. schema name and password). We’ll be storing our target database password as a secret in Azure Key Vault. Once we create our vault, we will create a secret that holds our target database password as follows:

Create a secret

2.2 Enable Azure service principal in the source ADB-S instance

The next step is to enable the service principal in our source database so that it can reach our secret in Azure Key Vault to fetch the password of the target database user. We are not going to cover those steps in detail here but you can follow my other blog post to perform those steps.

At this point, I’m almost ready to use Azure service principal in my ADB-S instance. As the final step of my service principal configuration, I’ll add the necessary members and access control (IAM) role(s) to my Azure key vault. In other words, I need to add my ADB-S instance (which is an application from Azure's point of view) as an authorized member and grant it the needed role(s) to access the secrets in Azure key vault:

Add ADB-S instance as an authorized member to access the secret

To add a role, I’ll navigate to Azure console home -> Security -> Key vaults -> <myKeyVaultName> -> Access Control (IAM) -> Add -> Add Role Assignment -> Role and search for “secret”. Since I only need read access, I will choose the "Key Vault Secrets User" role.

Before we move on the next step, it's worth mentioning that everyhting we have done so far is one-time only. In other words, when need to create another database link using the same credential we don't need to create a secret and enable the service principal again.

2.3 Create a database link

Before we create our database link, we will first create our credential object for the target database:

BEGIN                                                                         
  DBMS_CLOUD.CREATE_CREDENTIAL(                                                
      credential_name => 'AZURE_SECRET_CRED',
      params          => JSON_OBJECT('username'    value 'ADMIN',
                                     'secret_id'   value 'tuzlasecret',
                                     'azure_vault_name' value 'tuzlavault')
  );                                                                          
END;                                                                          
/

azure_vault_name is the name of our vault in Azure Key Vault and secret_id is the name of the secret located in the same vault. Note that we did not provide any password when creating our credential. 

Now we are ready to create our database link:

BEGIN
  DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
    db_link_name => 'financelink', 
    hostname => 'adb.us-sanjose-1.oraclecloud.com', 
    port => '1522',
    service_name => 'qtr*****_finance_low.adb.oraclecloud.com',
    credential_name => 'AZURE_SECRET_CRED',
    directory_name => 'DBLINK_WALLET_DIR');
END;
/ 
select * from dual@financelink;

D
-
X

To summarize, DBMS_CLOUD now supports integration with secret management services of OCI, Azure, AWS, and GCP which allows ADB-S users to store their passwords as secrets. This applies to any use case that requires you to provide a username and passwords pair including but limited to creating database links, making web service calls via UTL_HTTP, sending email via UTL_SMTP,  and accessing cloud object stores. Storing your passwords as a secret allows you to not only encrypt them with your own encryption keys but also make password management (e.g. creation, rotation) much easier by avoiding the password replication in each database. Check out our documentation to learn more about this feature.

Can Tuzla

Principal Product Manager

Can is a Principal Product Manager for Oracle Autonomous Database (ADB-S) and has been with the company since 2014. Prior to joining the ADB-S team, he worked on the Oracle Multitenant and Oracle Query Optimizer teams. Can holds a MS (Computer Science) from Case Western Reserve University and a BS (Computer Engineering) from Bilkent University.


Previous Post

July edition of newsletter for Autonomous Database Serverless

Keith Laker | 7 min read

Next Post


Manage Your Oracle Cloud Infrastructure Cost and Usage!

John McHugh | 2 min read
Everything you need to know about data warehousing with the world's leading cloud solution provider