DEV Community

Faris Durrani
Faris Durrani

Posted on

How to read OCI bucket files in OCI Oracle Database SQL using OCI resource principals

#oracle #database #bucket #cloud

How to read Oracle Cloud object storage bucket files using SQL in an OCI Oracle database using OCI resource principals

1. Create dynamic group

While these steps can be used for autonomous and non-autonomous Oracle Databases, we require for this guide that the database be in Oracle Cloud to make use of OCI's Resource Principal permissions.

First, we create a dynamic group--e.g., named "ADB-dgrp" containing the database matching rule:

resource.id = 'ocid1.autonomousdatabase.iad..aaaxxxxxx'
Enter fullscreen mode Exit fullscreen mode

2. Grant permissions in IAM policies

In OCI, grant the appropriate policies for the dynamic group to read files in the bucket

allow dynamic-group ADB-dgrp to read object-family in compartment ABC
Enter fullscreen mode Exit fullscreen mode

3. Grant resource principal credential use

Within the admin account in the database, enable the use of OCI resource principals which makes use of said dynamic group.

EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();
Enter fullscreen mode Exit fullscreen mode

We also give access of the resource principal to the database schema where the read files SQL query will occur:

BEGIN
    DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL(
        username => 'my_schema'
    );
END;
Enter fullscreen mode Exit fullscreen mode

4. Grant execute privileges for DBMS_CLOUD

Grant execute privileges for DBMS_CLOUD for the schema. This is the package used to call the bucket. From the admin account:

GRANT EXECUTE ON DBMS_CLOUD TO my_schema;
Enter fullscreen mode Exit fullscreen mode

5. List the bucket objects

And you're done.

Now, list the objects in the bucket using regular SQL and the DBMS_CLOUD.LIST_OBJECTS package function, replacing the object storage namespace and bucket name with your own:

SELECT *
FROM DBMS_CLOUD.LIST_OBJECTS(
  credential_name => 'OCI$RESOURCE_PRINCIPAL',
  location_uri => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idszihaxdr/b/testfd-bucket-1047/o/'
);
Enter fullscreen mode Exit fullscreen mode

References

  1. docs.oracle.com: DBMS_CLOUD

Safe harbor statement

The information provided on this channel/article/story is solely intended for informational purposes and cannot be used as a part of any contractual agreement. The content does not guarantee the delivery of any material, code, or functionality, and should not be the sole basis for making purchasing decisions. The postings on this site are my own and do not necessarily reflect the views or work of Oracle or Mythics, LLC.

This work is licensed under a Creative Commons Attribution 4.0 International License (CC-BY 4.0).

Top comments (0)