(Main page Image courtesy of Alexandra Bakhareva via www.pexels.com)

There is an interesting post on our community forum asking for help troubleshooting access to object storage using DBMS_CLOUD package, see here: https://community.oracle.com/customerconnect/discussion/748174/troubleshooting-dbms-cloud-access-to-object-storage#latest.

The person who created the question appeared to have created a valid credential and appeared to be using a valid URL but was getting an ora-error “ORA-20401: Authorization failed for URI“. So how do you investigate this type of problem? There are two possible areas where we might get an error- user authorization is invalid, bucket doesn’t exist, and/or user authorization is valid but can’t access the bucket,

1. Make the bucket public

Edit the visibility of the bucket and set it to public.

Try querying the bucket via SQL Worksheet: 

SELECT * FROM DBMS_CLOUD.LIST_OBJECTS(NULL, 'https://path-to-your_bucket/');

this will either work or fail with an  “ORA-20404: Object not found – xxxxxx” error. If it fails with this error then move to step 2. If it works then jump to step 3.

2. Get the URL for the bucket

This can be more complicated than it first appears because if you search the internet you will find all sorts of different types of strings for accessing buckets on OCI. To get a working URL find a file in the bucket you want to query. Click on the three vertical dots on the right side of the page for that file and select “View Object Details”:

Alternate text

 

alternate text

 

the URL you want is the first URL shown – URL Path (URI:):

at

So my query to view the contents of my object storage bucket should look like this:

SELECT * FROM DBMS_CLOUD.LIST_OBJECTS(NULL, 'https://objectstorage.uk-london-1.oraclecloud.com/n/adwc4pm/b/Testing/o/');

This query should now work with the bucket marked as “public” and return 1 row showing my file called channels.csv.

Once this query is working then we can set the visibility for the bucket back to “private” and start work on testing the credential.

3. Get your username.

This is where you need to collect information from a couple of different places. Firstly, lets get the username. This information can be viewed by clicking on the person icon in the top right corner of your cloud console page:

at

 

the first line underneath the word “Profle” is your username and that is what needed when you create a credential to access your bucket.

Then you need an “Auth Token”. Get the “Auth token” for your user via the “Domain->Users” page under Identity -> Domains -> OracleIdentityCloudService domain ->Users screen. There is an option on the bottom left for accessing the table of existing “Auth Tokens” with a button to create a new one. Once you have your auth token then you can create your credential.

4. Create a new credential

The command to create your new credential will now look like this:

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'MY_CRED_NAME',
    username => 'my_username_from_my_profile_details',
    password => 'my_new_auth_token'
  );
END;
/

 

5. Test your credential against your private bucket

So my query to view the contents of my object storage bucket should now look like this:

SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('MY_CRED_NAME', 'https://objectstorage.uk-london-1.oraclecloud.com/n/adwc4pm/b/Testing/o/');

This query should now work with the bucket marked as “private” and it should return 1 row showing my file called channels.csv.

Conclusion

The above steps will help you troubleshoot access to object storage bucket via DBMS_CLOUD package when you get  “ORA-20401: Authorization failed for URI” or “ORA-20404: Object not found – xxxxxx”. Usually, either the username is not in the correct format or the URL is not in the correct format. The above steps show you how to make sure you have the correct information for creating a new credential that allows you to access your Object Storage buckets.

There are lots of other ways to “authorize” access to your buckets and files, such as using Resource Principal – see here: https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/resource-principal.html#GUID-E283804C-F266-4DFB-A9CF-B098A21E496A. 

There are quite a few blog posts on securely accessing cloud resources on OCI, AWS and GCP, see here: https://blogs.oracle.com/datawarehousing/search.html?contentType=Blog-Post&default=resource%20principal*