X

Information, tips, tricks and sample code for Big Data Warehousing in an autonomous, cloud-driven world

  • August 8, 2019

Autonomous Database Now Supports Accessing the Object Storage with OCI Native Authentication

Can Tuzla
Senior Product Manager

Loading and querying external data from Oracle Cloud Object Storage are amongst some of the common operations performed in Autonomous Database. Accessing Object Storage requires users to have credentials that they can create via the CREATE_CREDENTIAL procedure of DBMS_CLOUD package. Object Storage credentials are based on the user's OCI username and an Oracle-generated token string (also known as 'auth token'). While auth token based credentials are still supported, DBMS_CLOUD now supports creating OCI native credentials as well! In this blog post, we are going to cover how to create a native credential and use it in an operation that requires Object Storage authentication. Let’s start…

As you may already be familiar, the syntax to create a credential with a username and an auth token in ADB is as follows:

DBMS_CLOUD.CREATE_CREDENTIAL (
  credential_name   IN VARCHAR2,
  username          IN VARCHAR2,
  password                      IN VARCHAR2 DEFAULT NULL);

CREATE_CREDENTIAL procedure is now overloaded to provide native authentication with the following syntax:

DBMS_CLOUD.CREATE_CREDENTIAL (
	credential_name IN VARCHAR2,
	user_ocid       IN VARCHAR2,
	tenancy_ocid    IN VARCHAR2,
	private_key     IN VARCHAR2,
	fingerprint     IN VARCHAR2);

In native authentication, the username and password parameters are replaced with the user_ocid, tenancy_ocid, private_key, and fingerprint parameters. 

user_ocid and tenancy_ocid are pretty self-explanatory and they correspond to user’s and tenancy’s OCIDs respectively (Check out “Where to Get the Tenancy's OCID and User's OCID” for more details).

The private_key parameter specifies the generated private key in PEM format. When it comes to the private_key parameter, there are couple important details worth mentioning. Currently, a private key that is created with a passphrase is not supported. Therefore, you need to make sure you generate a key with no passphrase (Check out “How to Generate an API Signing Key” for more details on how to create a private key with no passphrase). Additionally, the private key that you provide for this parameter should only contain the key itself without any header or footer (e.g. ‘-----BEGIN RSA PRIVATE KEY-----', ‘-----END RSA PRIVATE KEY-----’).

Lastly, the fingerprint parameter specifies the fingerprint that can be obtained either after uploading the public key to the console (See “How to Upload the Public Key”) or via the OpenSSL commands (See “How to Get the Key's Fingerprint”). Once you gather all the necessary info and generate your private key, your CREATE_CREDENTIAL procedure should look similar to this:

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL (
    credential_name => 'OCI_NATIVE_CRED',
    user_ocid              => 'ocid1.user.oc1..aaaaaaaatfn77fe3fxux3o5lego7glqjejrzjsqsrs64f4jsjrhbsk5qzndq',
    tenancy_ocid           => 'ocid1.tenancy.oc1..aaaaaaaapwkfqz3upqklvmelbm3j77nn3y7uqmlsod75rea5zmtmbl574ve6a',
    private_key            => 'MIIEogIBAAKCAQEAsbNPOYEkxM5h0DF+qXmie6ddo95BhlSMSIxRRSO1JEMPeSta0C7WEg7g8SOSzhIroCkgOqDzkcyXnk4BlOdn5Wm/BYpdAtTXk0sln2DH/GCH7l9P8xC9cvFtacXkQPMAXIBDv/zwG1kZQ7Hvl7Vet2UwwuhCsesFgZZrAHkv4cqqE3uF5p/qHfzZHoevdq4EAV6dZK4Iv9upACgQH5zf9IvGt2PgQnuEFrOm0ctzW0v9JVRjKnaAYgAbqa23j8tKapgPuREkfSZv2UMgF7Z7ojYMJEuzGseNULsXn6N8qcvr4fhuKtOD4t6vbIonMPIm7Z/a6tPaISUFv5ASYzYEUwIDAQABAoIBACaHnIv5ZoGNxkOgF7ijeQmatoELdeWse2ZXll+JaINeTwKU1fIB1cTAmSFv9yrbYb4ubKCJuYZJeC6I92rT6gEiNpr670Pn5n43cwblszcTryWOYQVxAcLkejbPA7jZd6CW5xm/vEgRv5qgADVCzDCzrij0t1Fghicc+EJ4BFvOetnzEuSidnFoO7K3tHGbPgA+DPN5qrO/8NmrBebqezGkOuOVkOA64mp467DQUhpAvsy23RjBQ9iTuRktDB4g9cOdOVFouTZTnevN6JmDxufu9Lov2yvVMkUC2YKd+RrTAE8cvRrn1A7XKkH+323hNC59726jT57JvZ+ricRixSECgYEA508e/alxHUIAU9J/uq98nJY/6+GpI9OCZDkEdBexNpKeDq2dfAo9pEjFKYjH8ERj9quA7vhHEwFL33wk2D24XdZl6vq0tZADNSzOtTrtSqHykvzcnc7nXv2fBWAPIN59s9/oEKIOdkMis9fps1mFPFiN8ro4ydUWuR7B2nM2FWkCgYEAxKs/zOIbrzVLhEVgSH2NJVjQs24S8W+99uLQK2Y06R59L0Sa90QHNCDjB1MaKLanAahP30l0am0SB450kEiUD6BtuNHH8EIxGL4vX/SYeE/AF6tw3DqcOYbLPpN4CxIITF0PLCRoHKxARMZLCJBTMGpxdmTNGyQAPWXNSrYEKFsCgYBp0sHr7TxJ1WtO7gvvvd91yCugYBJAyMBr18YY0soJnJRhRL67A/hlk8FYGjLW0oMlVBtduQrTQBGVQjedEsepbrAcC+zm7+b3yfMb6MStE2BmLPdF32XtCH1bOTJSqFe8FmEWUv3ozxguTUam/fq9vAndFaNre2i08sRfi7wfmQKBgBrzcNHN5odTIV8l9rTYZ8BHdIoyOmxVqM2tdWONJREROYyBtU7PRsFxBEubqskLhsVmYFO0CD0RZ1gbwIOJPqkJjh+2t9SH7Zx7a5iV7QZJS5WeFLMUEv+YbYAjnXK+dOnPQtkhOblQwCEY3Hsblj7Xz7o=',
    fingerprint            => '4f:0c:d6:b7:f2:43:3c:08:df:62:e3:b2:27:2e:3c:7a');
END;
/

PL/SQL procedure successfully completed.

We should now be able to see our new credential in the dba_credentials table:

SELECT owner, credential_name
FROM dba_credentials 
WHERE credential_name LIKE '%NATIVE%';

OWNER CREDENTIAL_NAME
----- ---------------
ADMIN OCI_NATIVE_CRED

Let’s go ahead and create an external table using our new credential:

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name      =>'CHANNELS_EXT',
    credential_name =>'OCI_NATIVE_CRED',
    file_uri_list   =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/adb/b/bucket_testpdb/o/channels.txt',
    format          => json_object('delimiter' value ','),
    column_list     => 'CHANNEL_ID NUMBER, 
	                    CHANNEL_DESC VARCHAR2(20), 
	                    CHANNEL_CLASS VARCHAR2(20), 
	                    CHANNEL_CLASS_ID NUMBER, 
	                    CHANNEL_TOTAL VARCHAR2(13), 
	                    CHANNEL_TOTAL_ID NUMBER');
END;
/

PL/SQL procedure successfully completed.

SELECT count(*) FROM channels_ext;

COUNT(*)
--------
5

To summarize, in addition to the auth token based authentication, you can now also have OCI native authentication and CREATE_CREDENTIAL procedure is overloaded to accommodate both options as we demonstrated above.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.