Issue :
Got the error : ORA-20401: Authorization failed, while listing contents of OCI Object storage using PL/SQL in ADB
Error :
ERROR at line 1:
ORA-20401: Authorization failed for URI – https://identity.us-ashburn-1.oraclecloud.com/20160918/compartments?compartmentId=ocid1.tenancy.oc1..aaaaaaaazhrdcmfrzkdn
ORA-06512: at “C##CLOUD$SERVICE.DBMS_CLOUD”, line 964
ORA-06512: at “C##CLOUD$SERVICE.DBMS_CLOUD”, line 2864
ORA-06512: at line 7
Reason :
API Keys for the OCI user are missing
Fix :
1. Generate an API Signing Key
Generate a private key and pem format public key.
Generating private key and ensuring that you can only read the private key file:
openssl genrsa -out /home/opc/TESTKEY/oci_api_key.pem 2048
chmod go-rwx /home/opc/TESTKEY/oci_api_key.pem
Generating public key:
openssl rsa -pubout -in /home/opc/TESTKEY/oci_api_key.pem -out ~/.oci/oci_api_key_public.pem
Generating the key’s fingerprint:
openssl rsa -pubout -outform DER -in /home/opc/TESTKEY/oci_api_key.pem | openssl md5 -c
2. Create an OCI user and Public key for the user.
Menu > Identity > Users > Create user.
Click on ‘API Keys’ in Resources Section and then add the Public key contents.
Allow the user to manage object-family.
3. Creating the Database credentials:
Connect to database and execute following step for creating credentials:
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL (
credential_name => ‘OCI_KEY_CRED’,
user_ocid => ‘ocid1.user.oc1..aaaaaaaa5ullx2ufyhlskklzowo2pcaw76cx3nv2kcygh’,
tenancy_ocid => ‘ocid1.tenancy.oc1..aaaaaaaazhrdcmfrzkdn’,
private_key => ‘MIIEogIBAAKCAQEA3zZ4HC9WiuYs1nDVEu1xCEM2a6Y7HX8nUfKBB0oljYffwf+L1CxyGtrcedChdhw=’,
fingerprint => ’20:9e:a1:ee:1a:22:fa:50:ea:3f:17:77:ce:9e:c0:25′);
END;
/
4. Listing the compartment
— List compartments example:
set linesize 300
set pagesize 200
set serveroutput on
DECLARE
resp DBMS_CLOUD_TYPES.resp;
root_compartment_ocid VARCHAR2(512) := ‘ocid1.tenancy.oc1..aaaaaaaazhrdcmfrzkdn’;
BEGIN
— Send request
dbms_output.put_line(‘Send Request’);
resp := DBMS_CLOUD.send_request(
credential_name => ‘OCI_KEY_CRED’,
uri => ‘https://identity.us-ashburn-1.oraclecloud.com/20160918/compartments?compartmentId=’ || root_compartment_ocid,
method => DBMS_CLOUD.METHOD_GET,
headers => JSON_OBJECT(‘opc-request-id’ value ‘list-compartments’)
);
dbms_output.put_line(‘Body: ‘ || ‘————‘ || CHR(10) || DBMS_CLOUD.get_response_text(resp) || CHR(10));
dbms_output.put_line(‘Headers: ‘ || CHR(10) || ‘————‘ || CHR(10) || DBMS_CLOUD.get_response_headers(resp).to_clob || CHR(10));
dbms_output.put_line(‘Status Code: ‘ || CHR(10) || ‘————‘ || CHR(10) || DBMS_CLOUD.get_response_status_code(resp));
dbms_output.put_line(CHR(10));
END;
/
