As Oracle continues its journey toward being the most inter-operable cloud platform out there, we recently released support for accessing your Amazon Web Services (AWS) resources (such as S3 object storage) from Autonomous Database on Shared Infrastructure (ADB-S) using AWS IAM policies and roles, instead of dealing with security credentials containing secret access keys. This makes security simpler and better, allowing you to limit access to AWS resources by roles granted to a database, rather than individual user credentials, as well as taking away any risk of exposing secret access keys in a script.
Below I walk through a start-to-finish example and explanation of how you may set up such a role Amazon Resource Name (ARN) based credential for your ADB instance. Contrast this with my previous post showing how you may access the AWS S3 object storage from ADB-S using a secret access key; This method has a few additional steps, however, these are initial, one-time steps that can then used across several different databases and database users. Follow the linked URLs, in this post, to the relevant documentation if you would like to learn more about each topic.
Note: If you already have an AWS role containing a policy to grant access to your AWS resources, you may skip Steps 1 and 2 and jump directly to Step 3.
I assume below that you have an Autonomous Database instance provisioned. If not, and you are starting from scratch, follow this ADB Quickstart lab to help you get provisioned and familiar with the database console.
In your AWS console, your account administrator must define a policy that allows access to AWS resources (such as an S3 bucket). Navigate to Identity and Access Management (IAM) and hit Create Policy.
Select the AWS resources to be granted access to as well as the access level. In this example, I am granting All Access to the S3 object storage in the policy.
Follow the wizard and finish up with a name and description of your policy. In my example, I've named the policy ARNDemoPolicy. Note that this policy can be granted to several different roles, if required.
Next, we will create an AWS Role, which is a secure grouping containing the policy(s) that will grant the necessary access to your AWS resources. Once again under IAM, select Create Role.
Since we will be using our new role to trust our Oracle Cloud ADB instances (which is third-party to the AWS Cloud) select Another AWS Account. We will be updating the User Account ID and External IDs with Oracle Cloud Infrastructure (OCI) values in Step 3; for now, simply use your existing AWS Account ID as a placeholder (which you can find as shown in the screen below)
As well as any placeholder value for an External ID. Here, I've used 0000.
Follow along the wizard. Select the policy(s) you would like to include in your role (I include our created ARNDemoPolicy from Step 1) and then click Create Role.
Under your newly created role on the AWS Console, as in the screen below, you will now see a Role ARN; In AWS, the role ARN is the role's identifier to be used for access to AWS resources. Note your Role ARN, we will use it in a later step.
Click on Edit Trust Relationship. The trust relationship is a configuration in your role that informs your AWS Resources about which external AWS account and external ID it can trust. You may read more about trust relationships here.
Next, let us switch over to query our Autonomous Database for the necessary information for our AWS role's trust relationship. From your Autonomous Database console, go to Tools -> Database Actions -> SQL
For the trust relationship, we will need
Run the following query in your database and copy down the Oracle user ARN
SELECT param_value FROM CLOUD_INTEGRATIONS WHERE param_name = 'aws_user_arn';
Also run the following query over the v$pdbs view in your ADB, and note down your database, compartment or tenancy OCIDs to use as your role's External ID.
SELECT cloud_identity FROM v$pdbs;
Now, head back over to your AWS role's trust relationship.
This completes the setup required on AWS.
Now that we are done with our AWS setup, let us jump back into our ADB instance's SQL worksheet and run the following script to enable the use of ARN credentials for the database user that you want to give access to your AWS resource. Replace "adb_user" with your database user's username; In my example, I am providing access to the Admin user.
Note: Only an Admin user with privileges to access the DBMS_CLOUD_ADMIN package may run this procedure
BEGIN
DBMS_CLOUD_ADMIN.ENABLE_AWS_ARN (username => 'adb_user');
END;
/
After ARN usage is enabled for the Autonomous Database instance and the necessary role is configured in AWS, we may now create a credential object with ARN parameters by running the following script, which calls the DBMS_CLOUD package. This credential will provide the database user access to Amazon resources.
You may repeat Step 5 and 6 for any database user that you wish to grant access to the same AWS resources.
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'DEF_CRED_ARN',
params =>
JSON_OBJECT(
'aws_role_arn' value
'arn:aws:iam::123456:role/AWS_ROLE_ARN',
'external_id_type' value 'database_ocid'
)
);
END;
/
Now, with the ARN credential setup, our ADB user has access to the AWS S3 object store. We can verify this by running the following example script, passing in the "credential_name" of the credential created above and the S3 object store bucket URI, to list all the objects present in the S3 bucket!
SELECT object_name FROM DBMS_CLOUD.LIST_OBJECTS(
credential_name => 'DEF_CRED_ARN',
location_uri => 'https://my-bucket.s3.us-west-2.amazonaws.com/')
Refer to the official documentation for more information about the use of ARN credentials in Autonomous Database. We are continuing to work on releasing such one-time, repeatable and simplified methods for ADB authorization both for Oracle Cloud (see Can's post on authorization using Resource Principles) as well as for other cloud platforms.
Nilay is a principal product manager at Oracle, responsible for adoption and feature development of Oracle's flagship converged cloud database - Autonomous Database. He was previously a developer and data scientist, and has a decade worth of experience in data warehousing, dimensional modeling, search engines and machine learning. A global Carnegie Mellon graduate, he has had the opportunity to work, travel and study in several different countries in various fields. His avocation is music; in his downtime he enjoys playing guitar or piano with a strong cup of chai nearby.
Nilay blogs regularly, and often speaks at cloud and database events. Follow his work on the Twitter @theproductlad