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
DBMS_CLOUD
package was introduced in Oracle Autonomous database to provide a simple way to
interact with an object store from the database. It can be used to support data
loading from various cloud service provides like OCI, Azure blob storage,
Amazon S3, Git hub repository and Google cloud storage.
In the following
example I will be using my Oracle Free tier account for demonstration to show
how to do step by step configuration for copying data from a delimited file
located in object storage to an existing table in the database.
Log into the
Oracle cloud and from the “Top-left” hamburger menu select the “Storage >
Object storage & Archive storage > Buckets “option, finally click on the
“Create Bucket” button on the “Object storage” dashboard and enter the required
details like ( name of the bucket, storage tier and key management, then click
the “create” button )
Once the bucket
has been provisioned, drill into it by clicking the bucket name.
Click the “upload”
button, to upload the required file into the object storage bucket, once the
file got uploaded click on the “kebab” menu on the right hand side of the
uploaded item and select the “view object details” option, the resulting screen
will display details about the uploaded object, including the URI path, which
will be needed to access the object.
To grant the
access to the bucket and its contents, we must create an access token. using
the “Top-left” hamburger menu select the “ Identity & Security >
Identity > users “ option, and click on the user that needs access to the
bucket, then click on the “Auth Tokens” link in the “Resources” menu on the bottom left of the screen, and click on
the “Generate Token” button.
Enter the
description of the token and click the “Generate Token” button. Click the
“copy” link to record the token in clipboard, which will be utilized shortly to
create the credentials in the database.
Now we will see
how to use this generated Token to access the details in object storage from
SQL Statements.
First we will
create a schema user account with required set of privileges to it.
admin@ATP19C> create user
demo_user identified by "********";
User created.
admin@ATP19C> grant
connect,resource,unlimited tablespace to demo_user;
Grant succeeded.
admin@ATP19C> grant
execute on dbms_cloud to demo_user;
Grant succeeded.
Then connect
back to demo_user account and create a credential for object storage using
create_credential procedure.
admin@ATP19C> conn
demo_user/"********"@atp19c
Connected.
demo_user@ATP19C> begin
2
dbms_cloud.create_credential(
3 credential_name =>'my_demo_cred',
4
username => 'your_oci_login_email_account',
5
password => 'the_above_copied_userAuth_Token_Here');
6 end;
7 /
PL/SQL procedure successfully
completed.
To access the
objects in the object storage all we need its URL location.
demo_user@ATP19C> variable
filename varchar2(800)
demo_user@ATP19C> variable
namespace varchar2(80)
demo_user@ATP19C> variable
uri varchar2(80)
demo_user@ATP19C>
demo_user@ATP19C> exec
:filename := 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/*******/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
'my_demo_cred',
4
:uri||:namespace||'/b/MY_DEMO_BUCKET/o/');
OBJECT_NAME BYTES
------------------------------
----------
File1.json 55878
File2.json 45992
File3.json 484455
File4.json 113873
File5.json 2757
File6.json 10612
mycsvdemo.txt 933
7 rows selected.
The dbms_cloud
package got several methods related to file handling in the object storage,
‘list_object” method is there to list all the files in the specific location. Likewise,
to read the contents of the specific file we can use the “get_object” method –
that returns the binary contents of the data.
demo_user@ATP19C> select
dbms_cloud.get_object('my_demo_cred',:filename) from dual;
DBMS_CLOUD.GET_OBJECT('MY_DEMO_CRED',:FILENAME)
----------------------------------------------------------------------------------------------------------------------------------------------------------------
22454D504E4F222C22454E414D45222C224A4F42222C224D4752222C224849524544415445222C2253414C222C22434F4D4D222C22444550544E4F220D0A373336392C22534D495448222C22434C4552
4B222C373930322C31372D4445432D313938302031323A30303A303020414D2C3830302C2C32300D0A373439392C22414C4C454E222C2253414C45534D414E222C373639382C32302D4645422D313938
312031323A30303A303020414D2C313630302C3330302C33300D0A373532312C2257415244222C2253414C45534D414E222C373639382C32322D4645422D313938312031323A30303A303020414D2C31
3235302C3530302C33300D0A373536362C224A4F4E4553222C224D414E41474552222C373833392C30322D4150522D313938312031323A30303A303020414D2C323937352C2C32300D0A373635342C22
4D415254494E222C2253414C45534D414E222C373639382C32382D5345502D313938312031323A30303A303020414D2C313235302C313430302C33300D0A373639382C22424C414B45222C224D414E41
474552222C373833392C30312D4D41592D313938312031323A30303A303020414D2C323835302C2C33300D0A373738322C22434C41524B222C224D414E41474552222C373833392C30392D4A554E2D31
3938312031323A30303A303020414D2C323435302C2C31300D0A373738382C2253434F5454222C22414E414C595354222C373536362C31392D4150522D303038372031323A30303A303020414D2C3330
30302C2C32300D0A373833392C224B494E47222C22505245534944454E54222C2C31372D4E4F562D313938312031323A30303A303020414D2C353030302C2C31300D0A373834342C225455524E455222
2C2253414C45534D414E222C373639382C30382D5345502D313938312031323A30303A303020414D2C313530302C302C33300D0A373837362C224144414D53222C22434C45524B222C373738382C3233
2D4D41592D303038372031323A30303A303020414D2C313130302C2C32300D0A373930302C224A414D4553222C22434C45524B222C373639382C30332D4445432D313938312031323A30303A30302041
4D2C3935302C2C33300D0A373930322C22464F5244222C22414E414C595354222C373536362C30332D4445432D313938312031323A30303A303020414D2C333030302C2C32300D0A373933342C224D49
4C4C4552222C22434C45524B222C373738322C32332D4A414E2D313938322031323A30303A303020414D2C313330302C2C31300D0A
To return the
data in the text format, we need to cast this raw data to text format using
UTL_RAW API.
demo_user@ATP19C> select
utl_raw.cast_to_varchar2(dbms_cloud.get_object('my_demo_cred',:filename)) from
dual;
UTL_RAW.CAST_TO_VARCHAR2(DBMS_CLOUD.GET_OBJECT('MY_DEMO_CRED',:FILENAME))
-------------------------------------------------------------------------
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17-DEC-1980
12:00:00 AM,800,,20
7499,"ALLEN","SALESMAN",7698,20-FEB-1981
12:00:00 AM,1600,300,30
7521,"WARD","SALESMAN",7698,22-FEB-1981
12:00:00 AM,1250,500,30
7566,"JONES","MANAGER",7839,02-APR-1981
12:00:00 AM,2975,,20
7654,"MARTIN","SALESMAN",7698,28-SEP-1981
12:00:00 AM,1250,1400,30
7698,"BLAKE","MANAGER",7839,01-MAY-1981
12:00:00 AM,2850,,30
7782,"CLARK","MANAGER",7839,09-JUN-1981
12:00:00 AM,2450,,10
7788,"SCOTT","ANALYST",7566,19-APR-0087
12:00:00 AM,3000,,20
7839,"KING","PRESIDENT",,17-NOV-1981
12:00:00 AM,5000,,10
7844,"TURNER","SALESMAN",7698,08-SEP-1981
12:00:00 AM,1500,0,30
7876,"ADAMS","CLERK",7788,23-MAY-0087
12:00:00 AM,1100,,20
7900,"JAMES","CLERK",7698,03-DEC-1981
12:00:00 AM,950,,30
7902,"FORD","ANALYST",7566,03-DEC-1981
12:00:00 AM,3000,,20
7934,"MILLER","CLERK",7782,23-JAN-1982
12:00:00 AM,1300,,10
Likewise to
copy the data into another table, we got a method called “copy_data” to copy
its content.
demo_user@ATP19C> create
table emp_demo as
2
select *
3 from
demo.emp
4
where 1 = 0 ;
Table created.
demo_user@ATP19C> begin
2
dbms_cloud.copy_data(
3
table_name =>'emp_demo',
4
credential_name =>'my_demo_cred',
5
file_uri_list => :filename,
6
format => json_object('delimiter' value ',',
7 'dateformat' value 'dd-mon-yyyy hh12:mi:ss
am',
8 'quote' value
'"','enablelogs' value false,
9 'skipheaders'
value '1') );
10 end;
11 /
begin
*
ERROR at line 1:
ORA-20000: ORA-06564: object
DATA_PUMP_DIR does not exist
ORA-06512: at
"C##CLOUD$SERVICE.DBMS_CLOUD", line 1693
ORA-06512: at
"C##CLOUD$SERVICE.DBMS_CLOUD", line 8286
ORA-06512: at
"C##CLOUD$SERVICE.DBMS_CLOUD", line 8309
ORA-06512: at line 2
The above error
is due to writing logs to default directory object “data_pump_dir” & since
the schema owner don’t have any privilege to it, It got errored.
Once we grant
the appropriate privileges
admin@ATP19C> grant write
on directory data_pump_dir to demo_user;
Grant succeeded.
admin@ATP19C> grant read
on directory data_pump_dir to demo_user ;
Grant succeeded.
The load got
succeed
demo_user@ATP19C> begin
2
dbms_cloud.copy_data(
3
table_name =>'emp_demo',
4
credential_name =>'my_demo_cred',
5
file_uri_list => :filename,
6
format => json_object('delimiter' value ',',
7 'dateformat' value
'dd-mon-yyyy hh12:mi:ss am',
8 'quote' value
'"','enablelogs' value false,
9 'skipheaders'
value '1') );
10 end;
11 /
PL/SQL procedure successfully
completed.
demo_user@ATP19C> select *
from emp_demo;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ----------
--------- ---------- ----------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-1980 800 20
7499 ALLEN SALESMAN 7698 20-FEB-1981 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-1981 1250 500 30
7566 JONES MANAGER 7839 02-APR-1981 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-1981 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-1981 2850 30
7782 CLARK MANAGER 7839 09-JUN-1981 2450 10
7788 SCOTT ANALYST 7566 19-APR-0087 3000 20
7839 KING PRESIDENT 17-NOV-1981 5000 10
7844 TURNER SALESMAN 7698 08-SEP-1981 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-0087 1100 20
7900 JAMES CLERK 7698 03-DEC-1981 950 30
7902 FORD ANALYST 7566 03-DEC-1981 3000 20
7934 MILLER CLERK 7782 23-JAN-1982 1300 10
14 rows selected.
demo_user@ATP19C>
