Load data from files in cloud storage using API Keys

 
In this post we will see about how to use API keys for OCI user to authenticate the cloud resources.
 
Step#1 – generate an API signing key
 
Generate a private key and pem format public key.
 
In this example the public and private key are stored in %HOME%\TESTKEY2
 
Rajeshwaran@rajeyaba-3WH3DK3 MINGW64 /c/users/Rajeshwaran
$ cd /c/users/rajeshwaran/
 
Rajeshwaran@rajeyaba-3WH3DK3 MINGW64 /c/users/rajeshwaran
$ mkdir TESTKEY2
 
Rajeshwaran@rajeyaba-3WH3DK3 MINGW64 /c/users/rajeshwaran
$ cd TESTKEY2
 
Generating the private key and ensuring that we can only read the private key file.
 
Rajeshwaran@rajeyaba-3WH3DK3 MINGW64 /c/users/rajeshwaran/TESTKEY2
$ openssl genrsa -out oci_api_key.pem 2048
Generating RSA private key, 2048 bit long modulus (2 primes)
..............................................+++++
.....................................+++++
e is 65537 (0x010001)
 
Rajeshwaran@rajeyaba-3WH3DK3 MINGW64 /c/users/rajeshwaran/TESTKEY2
$ ls -ltr
total 4
-rw-r--r-- 1 Rajeshwaran 197121 1702 Oct 21 19:05 oci_api_key.pem
 
Rajeshwaran@rajeyaba-3WH3DK3 MINGW64 /c/users/rajeshwaran/TESTKEY2
$ chmod go-rwx oci_api_key.pem
 
Rajeshwaran@rajeyaba-3WH3DK3 MINGW64 /c/users/rajeshwaran/TESTKEY2
$ ls -ltr
total 4
-rw-r--r-- 1 Rajeshwaran 197121 1702 Oct 21 19:05 oci_api_key.pem
 
Generating the public key
 
Rajeshwaran@rajeyaba-3WH3DK3 MINGW64 /c/users/rajeshwaran/TESTKEY2
$ openssl rsa -pubout -in oci_api_key.pem -out oci_api_key_public.pem
writing RSA key
 
Rajeshwaran@rajeyaba-3WH3DK3 MINGW64 /c/users/rajeshwaran/TESTKEY2
$ ls -ltr
total 5
-rw-r--r-- 1 Rajeshwaran 197121 1702 Oct 21 19:05 oci_api_key.pem
-rw-r--r-- 1 Rajeshwaran 197121  460 Oct 21 19:06 oci_api_key_public.pem
 
Generating the key’s fingerprint
 
Rajeshwaran@rajeyaba-3WH3DK3 MINGW64 /c/users/rajeshwaran/TESTKEY2
$ openssl rsa -pubout -outform DER -in oci_api_key.pem | openssl md5 -c
writing RSA key
(stdin)= d8:b3:74:c1:5c:76:51:5a:0b:5d:89:ce:25:46:ad:35
 
Step#2 – creating an OCI user and public key for the user.
 
Click on the OCI Hamburger menu > Identity > users > create user.
Then click on the ‘API keys’ in the resource section to add the public key contents ( oci_api_key_public.pem )
 
 

 
Once the public key got dragged and dropped there, the API Key fingerprint will be generated like this.
 

 
 
Step#3 – connect to the database and create credentials using the private key like this
 
demo-user@ATP19C> begin
  2     dbms_cloud.create_credential(
  3             credential_name =>'api_key_cred',
  4             user_ocid => 'ocid1.user.oc1..aaaaaaaambmzgftlbzv2krf2cm7dismh35jmdzhrdh3rx4z4434atun7dlsa',
  5             tenancy_ocid => 'ocid1.tenancy.oc1..aaaaaaaacogbjgqcpcgk4vtnwhi2binpz5buav3sidham4evvfu2fjixucaa',
  6             private_key =>'MIIEowIBAAKCAQEA0zUiWFszop1WIjWiKS3PzstuNbeSKffB3oyWuBID/xpQREKS
  7  0it1+YoXo8jFAee9Ep6idM97AMkOp1L1/FoSyHBzs4qHqE4srRAnSwNUzaox/ATZ
       ........
       ........
       ........
 30  khS6loArMh6vQOrpjmDjm3Mbu7kt4ROJHa2dwpSVKUiDmKXjMtb6',
 31             fingerprint =>'d8:b3:74:c1:5c:76:51:5a:0b:5d:89:ce:25:46:ad:35' );
 32  end;
 33  /
 
PL/SQL procedure successfully completed.
 
Step#4 – accessing the object storage file contents
 
demo-user@ATP19C> exec :filename := 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/MY_DEMO_BUCKET/o/mycsvdemo.txt';
 
PL/SQL procedure successfully completed.
 
demo-user@ATP19C> exec :uri := 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/';
 
PL/SQL procedure successfully completed.
 
demo-user@ATP19C> exec :namespace := '********';
 
PL/SQL procedure successfully completed.
 
demo-user@ATP19C> select object_name, bytes
  2  from dbms_cloud.list_objects(
  3     'api_key_cred',
  4     :uri||:namespace||'/b/MY_DEMO_BUCKET/o/');
 
OBJECT_NAME                         BYTES
------------------------------ ----------
E1.DMP                              12288
E2.DMP                              12288
E3.DMP                              12288
File1.json                          55878
File2.json                          45992
File3.json                         484455
File4.json                         113873
File5.json                           2757
File6.json                          10612
mycsvdemo.txt                         933
 
10 rows selected.
 
Above all the steps are required to access the object storage contents using API Keys, but if the step (2) was missing then it will return an error like this
 
ORA-20401: Authorization failed for URI - https://objectstorage.us-ashburn-1.oraclecloud.com/n/********/b/MY_DEMO_BUCKET/o/
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1679
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 9318
ORA-06512: at line 1