DB Script management through pre-authenticated URLs

 Pre-authenticated URLs in OCI are fast becoming one of my favorite features of using object storage.  In this blog post I will explain how I am using them for both:

• Storing the current copy of my backup scripts and dynamically pulling it from my central repository
• uploading all my logs files to a central location

PROBLEM:

The problem I was trying to solve, is that I wanted to create a script to run on all my database nodes to create a weekly archival backup.

Since I have databases that are both Base DB databases, and ExaCS I found that I was continuously making changes to my backup script.  Sometimes it was checking something different in my environment, and sometimes it was improving the error checking.

Each time I made a change, I was going out to every DB host and copying the new copy of my script.

Eventually I realized that Pre-authenticated URLs could not only help me ensure all my DB hosts are running the current copy of my backup script, they could be the central log location.

Solution:

Solution #1 - Script repository

The first problem I wanted to solve, was that I wanted to configure a script repository that I could dynamically pull the most current copy of my scripts from. Since I am running in OCI, I was looking for a "Cloud Native" solution rather than using NFS mounts that are shared across all my DB hosts.

To complicate things, I have databases that are running in different tenancies.

Step #1 - Store scripts in a bucket

The first step was to create a bucket in OCI to store both the scripts and logs.  Within that bucket, under "More Actions" I chose "Create New Folder" and I created 2 new folders, "logs" and "scripts".

Then within the scripts folder I uploaded by current scripts

• rman.sh - My executable script that will set the environment and call RMAN
• backup.rman - My RMAN script that contains the RMAN command to backup my database.

Step #2 - Create a Pre-Authenticated Request

The next step was to create a Pre-Authenticated request on the "scripts" folder.  Next to the scripts folder I clicked on the  3 dots and chose "Create Pre-Authenticated Request".

On the window that came up, I changed the expiration to be 1 year in the future (the default is 7 days).  I chose the "Objects with prefix" box so that I could download any scripts that I put in this folder to the DB hosts.  I also made sure the "Access Type" is "Permit object reads on those with specified prefix".

I did not chose "Enable Object Listing".

These settings will allow me to download the scripts from this bucket using the Pre-Authenticated URL only.  From this URL you will not be able to list the objects, or upload any changes.

Step #3 - Create wrapper script to download current scripts

Then using the Pre-Authenticated URL in a wrapper script, I download the current copies of the scripts to the host and then executed my execution script (rman.sh) with a parameter.

Below you can see that I am using curl to download my script (rman.sh) and storing it my local script directory (/home/oracle/archive_backups/scripts).  I am doing the same thing for the RMAN command file.

Once I download the current scripts, I am executing the shell script (rman.sh) .

curl -X GET https://{my tenancy}.objectstorage.us-ashburn-1.oci.customer-oci.com/p/{actual URL is removed }/n/id20skavsofo/b/bgrenn/o/scripts/rman.sh --output /home/oracle/archive_backups/scripts/rman.sh
curl -X GET https://{my tenancy}.objectstorage.us-ashburn-1.oci.customer-oci.com/p/{actual URL is removed }/n/id20skavsofo/b/bgrenn/o/scripts/backup.rman --output /home/oracle/archive_backups/scripts/backup.rman


/home/oracle/archive_backups/scripts/rman.sh $1

Solution #2 - Log repository

The second problem I wanted to solve was to make it easy review the execution of my scripts.  I don't want to go to each DB host and look at the log file.  I want to have the logs stored in a central location that I can check.  Again Pre-Authenticated URLs to the rescue !

Step #1 - Create the Pre-Authenticated URL

In the previous steps I already create a "logs" folder within the bucket. In this step I want to create a Pre-Authenticated URL like I did for the scripts, but in this case I want to use it to store the logs.

Like before I chose "Create Pre-Authenticated Request" for the "logs" folder.

This time, I am choosing "Permit object writes to those with the specified prefix". This will allow me to write my log files to this folder in the bucket, but not list the logs, or download any logs.

Step #2 - Upload the log output from my script

The nice thing was once I implemented Solution #1, and had all of my DB nodes already downloading the current script.  I updated the script to add an upload to object storage of the log file and they will all use my new script.

In my script I already had 2 variables set

• NOW - The current date in "yyyymmdd" format
• LOGFILE - The name of the output log file from my RMAN backup.

Now all I had to do was to add a curl command to upload my log file to the bucket.

Note I ma using the NOW variable to create a new folder under "logs" with the data so that my script executions are organized by date.

curl --request PUT --upload-file /home/oracle/archive_backups/logs/${LOGFILE} https://{My tenancy}.objectstorage.us-ashburn-1.oci.customer-oci.com/p/{URL removed}/n/id20skavsofo/b/bgrenn/o/logs/${NOW}/${LOGFILE}

BONUS

If I wanted to get fancy I could have put my LOGS in a new bucket, and configured  a lifecycle management rule to automatically delete logs after a period of time from the bucket.

RMAN create standby database - Restore or Duplicate ?

RMAN create standby database - Are you like me and use "restore database" for large databases, or like most people (based on my Linkedin poll) and use "duplicate for standby"? 

The table below shows you the 3 main differences between the 2 methods.

This post started with a discussion within my team around which method you use. I, being of the "restore database" camp, didn't realize how commonly used "duplicate for standby" is. 

I have also dug through the documentation, and there is no common method that is mentioned. Even the 21c documentation for creating a standby database doesn't mention using the duplicate command.

I also was pointed to a MOS note that goes through creating a standby directly from the primary across multiple nodes, and with encryption.  Creating a Physical Standby database using RMAN restore database from service (Doc ID 2283978.1)

Well in this post, I will explain why "restore database" has been my preference. 

NOTE : If you are creating a standby database that is encrypted and the source database is not (creating a standby database in OCI for example) then I have instructions at the end of this post for how to use "Restore Database" to create a hybrid standby database.

Duplicate database for standby

From the poll I ran, this is the most common way to create a standby database.  It is probably the simplest way also because a lot of the configuration of the standby database is done automatically as part of the automated process.

Below is the simplified steps to perform this process.

PRE work

1. Create simple initfile on the standby host.  The real SPFILE will be brought over as part of the duplication process.  This may contain location parameters for datafiles and redo logs if different from the primary.
2. Create directories on the standby host.  This includes the audit directory, and possibly the database file directories if they are different from the host.
3. Startup nomount.

Duplicate 

The duplicate process automatically performs these major steps using the standby as an auxiliary instance.

1.  Create an SPFILE. The process creates an SPFILE for the standby and sets parameters for the standby.
2. Shutdown/Startup standby database. This will use the newly created SPFILE during the rest of the processing
3. Restore backup controlfile for standby database. The controlfile for the standby database is put in place, and the spfile is updated to it's location
4. Mount controlfile . Mount the controlfile that was restored
5. Restore database . Restore the datafiles files for the CDB and PDBs to their new location on the standby
6. Switch datafile . Uses the new location of the datafiles that were restored.
7. Create standby redo logs.
8. Set parameters for standby database. The parameters necessary to communicate with the primary database are set.
9. Put standby in recover mode . By this time, you should have set the primary database to communicate with the standby database.

NOTES

If you noticed above, I highlighted the second step which forces a shutdown/startup of the standby database. Because of this step, it is not possible to use this method and restore across nodes in a RAC database.  This can cause the duplicate operation to take much longer for larger databases.

Then in step #5 you can see that the "Restore Database" is automatic in the processing and it is not possible to perform a "restore as encrypted" if you are migrating to OCI from a non-TDE database.  The duplicate process does support "restore as encrypted", but only for creating a new Database, not a standby database.

Restore Database

This is the method that I've always used.  There is no automation, but it gives you much more control over the steps.  

PRE work

1. Restore copy of prod SPFILE to standby host.  For this process, it doesn't matter if it is an intifile or spfile.  In this file you set all the parameters that are needed for the standby database to communicate with the primary and store datafiles/logfiles in the correct location.
2. Create directories on the standby host.  This includes the audit directory, and possibly the database file directories if they are different from the host.
3. Startup nomount.
4. Create copy of primary controlfile for standby. This will be used for the standby database, and should contain the backup catalog  of the primary database, and the RMAN settings including the  channel definitions.
5. Copy standby controlfile to standby host. The controlfile is copied to the standby host, and may be put in ASM at this point. Ensure the spfile points to the controlfile (and/or srvctl).
6. Alter database mount.  Mount the controlfile. 
7. Start up ALL nodes in the RAC cluster in mount mode.  This will allow you to restore the database across ALL nodes in the RAC cluster, and include all the networking from these nodes.  For a large database hosted on multiple DB nodes this can make a HUGE difference when restoring the database.
8. Create (or copy) TDE wallet.  If the standby database is going to be TDE, then include the wallet if the primary is TDE, or create a new wallet and key if the standby database is going to be TDE.

Restore Database 

The restore process is a manual process

1.  RMAN Connect to database (and possibly RMAN catalog). Connect to the database and make sure you have access to the backups. For ZDLRA this may mean connecting to the RMAN catalog.
2. Restore Database (as encrypted). This will restore the database to the new location.  With Restore Database, the database can be encrypted during the restore operation.  With 19c it is supported to have the standby database be encrypted without the primary database being encrypted (Hybrid dataguard).
3. Switch datafile . Uses the new location of the datafiles that were restored.
4. Recover database. This will use the archive logs that are cataloged to bring the standby database forward
5. Create standby redo logs.
6. Set parameters for standby database. The parameters necessary to communicate with the primary database are set.
7. Put standby in recover mode . By this time, you should have set the primary database to communicate with the standby database.

NOTES

With the restore database, there are 2 sections I highlighted and these are the advantages that I love about using this method.

• RMAN is restoring across multiple nodes in a RAC cluster which can make the restore operation much faster.
• Restore as encrypted allows you take a database that may have TDE partially implemented, or not implemented and create a new standby database that is encrypted. With the duplicate method, TDE would have to be implemented separately.

If you are restoring a VERY large database (200 TB for example) that was not TDE from object storage to the Exadata Cloud Service, both of these advantages can make a HUGE difference when creating a standby database.

Comparison

The chart below compares the the differences between "Duplicate Database" and "Restore Database".

WARNING: When using a ZDLRA for backups, it is NOT recommended to use the "Restore Database" to clone a database as a new copy. Registering the restored copy can cause issues with the RMAN catalog because the "restore database" leaves entries in the RC_SITE table.

Data Guard Hybrid Cloud Configuration

The ability to create a hybrid cloud configuration was introduced in Version 19.16 and there is a great blog post from Glen Hawkins explaining this feature.

This feature allows you have your Primary database remain unencrypted (no ASO license), but still have the standby database in OCI be TDE encrypted.

In this section I want to talk about how you can use "Restore Database as Encrypted" to implement this configuration quickly.

If you want to implement this feature using "Duplicate for standby" you have to separately encrypt the datafiles once they are restored in OCI.  This can be done online, or offline, but it is still a time consuming task.

Prepare the primary and future standby databases

The first step is prepare the primary database and future standby database by creating a wallet file and setting encryption keys.  There is a great video put together by Peter Wahl (PM for TDE and OKV) that goes through a lot of the steps.

Below is a summary of the steps you need to perform.  You can follow along the steps in Peter's video and I will point out where in the video you will find each step.

• Create the directories on the primary (3:40) -  Directories are specified in the video and need to be created on all nodes in a RAC cluster.
• Create the directories on the standby database (4:18) -Directories are specified in the video and need to be created on all nodes in a RAC cluster.
• Set the wallet_root in the primary (4:25) - This is set in the SPFILE only
• Set tablespace_encryption to decrypt_only on primary (4:40) -  This is set in the SPFILE only
• Set the default algorithm to AES256 on primary (4:50) - This is set in the SPFILE only
• Set wallet_root on standby, tablespace_encryption to auto_enable, and default algorithm on standby --  This is set in the initfile that you create prior to performing the restore.  This step is different from the video because there is no standby at this point.
• Bounce the primary database (5:50) - This can be in a rolling manner.
• Create a password protected wallet on the primary (7:25) - This gets created in the default location specified from WALLET_ROOT
• Create an auto open wallet on the primary (7:30) - This will make it automatically open for the database.
• Set the encryption keys in the primary (7:40) - The keys will be created so that they can be used when restoring the datafiles on the standby database.
• Copy the wallets from the primary to the standby (7:55) - This provides the standby database with the keys to encrypt.

ZFSSA can be used to share data from your Oracle Database

 Data Sharing has become a big topic recently, and Oracle Cloud has added some new services to allow you to share data from an Autonomous Database.  But how do you do this with your on-premises database ? In this post I show you how to use ZFS as your data sharing platform.

Data Sharing

Being able to securely share data between applications is a critical feature in todays world.  The Oracle Database is often used to consolidate and summarize collected data, but is not always the platform for doing analysis.  The Oracle Database does have the capability to analyze data, but tools such as Jupyter Notebooks, Tableu, Power Bi, etc are typically the favorites of Data Scientists and data analysts.

The challenge is how to give access to specific pieces of data in the database without providing access to the database itself.  The most common solution is to use object storage and pre-authenticated URLs.  Data is extracted from the database based on the user and stored in object storage in a sharable format (JSON for example).  With this paradigm, and pictured above, you can create multiple datasets that contain a subset of the data specific to the users needs and authorization.  The second part is the use of a pre-authenticated URL.  This is a dynamically created URL that allows access to the object without authentication. Because it contains a long string of random characters, and is only valid for a specified amount of time, it can be securely shared with the user.

My Environment

For this post, I started with an environment I had previously configured to use DBMS_CLOUD.  My database is a 19.20 database.  In that database I used the steps specified in the MOS note and my blog (information can be found here) to use DBMS_CLOUD.

My ZFSSA environment is using 8.8.63, and I did all of my testing in OCI using compute instances.

For preparation to test I had

• Installed DBMS_CLOUD packages into my database using MOS note #2748362.1
• Downloaded the certificate for my ZFS appliance using my blog post and added them to wallet.
• Added the DNS/IP addresses to the DBMS_CLOUD_STORE table in the CDB.
• Created a user in my PDB with authority to use DBMS_CLOUD
• Created a user on ZFS to use for my object storage authentication (Oracle).
• Configured the HTTP service for OCI
• Added my public RSA key from my key par to the OCI service for authentication.
• Created a bucket

NOTE:  In order to complete the full test, there were 2 other items I needed to do.

1) Update the ACL to also access port 80.  The DBMS_CLOUD configuration sets ACLs to access websites using port 443.  During my testing I used port 80 (http vs https).

2) I granted execute on DBMS_CRYPTO to my database user for the testing.

Step #1 Create Object

The first step was to create an object from a query in the database.  This simulated pulling a subset of data (based on the user) and writing it to a object so that it could be shared.  To create the object I used the DBMS_CLOUD.EXPORT_DATA package.  Below is the statement I executed.

BEGIN
 DBMS_CLOUD.EXPORT_DATA(
       credential_name => 'ZFS_OCI2',  
    file_uri_list =>'https://zfs-s3.zfsadmin.vcn.oraclevcn.com/n/zfs_oci/b/db_back/o/shareddata/customer_sales.json',
    format => '{"type" : "JSON" }',
   query => 'SELECT OBJECT_NAME,OBJECT_TYPE,STATUS FROM user_objects');
END;
/

In this example:

• CREDENTIAL_NAME - Refers to my authentication credentials I had previously created in my database.
• FILE_URI_LIST - The name and location of the object I want to create on the ZFS object storage.
• FORMAT - The output is written in JSON format
• QUERY - This is the query you want to execute and store the results in the object storage.  

As you can see, it would be easy to create multiple objects that contain specific data by customizing the query, and naming the object appropriately.

In order to get the  proper name of the object I then selected the list of objects from object storage.

set pagesize 0
SET UNDERLINE =
col object_name format  a25
col created format  a20
select object_name,to_char(created,'MM/DD/YY hh24:mi:ss') created,bytes/1024  bytes_KB 
       from dbms_cloud.list_objects('ZFS_OCI2', 'https://zfs-s3.zfsadmin.vcn.oraclevcn.com/n/zfs_oci/b/db_back/o/shareddata/');


 customer_sales.json_1_1_1.json 12/19/23 01:19:51    3.17382813

From the output, I can see that my JSON file was named 'customer_sales.json_1_1_1.json'.

Step #2 Create Pre-authenticated URL

The Package I ran to do this is below. I am going to break down the pieces into multiple sections. Below is the full code.

DECLARE tenancy_ocid VARCHAR (100) ; user_ocid VARCHAR (100) ; fingerprint VARCHAR (100) ; rest_api VARCHAR (100) ; host VARCHAR (100) ; body VARCHAR (2000) ; URL varchar (20); Bucket varchar (20); compartment varchar (20); object_name varchar (200); content_sha256 VARCHAR (2000) ; content_sha256_header VARCHAR (2000) ; content_length VARCHAR (2000) ; content_length_header VARCHAR (200) ; headers VARCHAR (200) ; content_type_header VARCHAR (200) ; vardate VARCHAR (200) ; date_header VARCHAR (200) ; host_header VARCHAR (200) ; request_target VARCHAR (200) ; signing_string VARCHAR (2000) ; ip_str VARCHAR (2000) ; post_request VARCHAR (2000) ; pubkey VARCHAR (2000) ; prvkey VARCHAR (2000) ; sign_raw RAW (2000); signature varchar (2000); signature_final varchar (2000); authorization_header varchar (2000); position64 integer :=1; returnval BOOLEAN := false; sType PLS_INTEGER := DBMS_CRYPTO.SIGN_SHA256_RSA; kType PLS_INTEGER := DBMS_CRYPTO.KEY_TYPE_RSA; req utl_http.req; res utl_http.resp; buffer varchar2(4000); json_obj json_object_t; url_string varchar2(400); BEGIN /* ########################################################################################## # # # Set Variables # # # ########################################################################################## */ /* # The tenancy. */ tenancy_ocid := 'ocid1.tenancy.oc1..nobody'; user_ocid := 'ocid1.user.oc1..oracle'; /* # The fingerprint. */ fingerprint := '6d:f9:57:d5:ff:b1:c0:98:81:90:1e:6e:08:0f:d0:69'; /* # The URL */ URL := '/oci'; /* # The bucket */ bucket := 'db_back'; /* # The compartment/share on ZFS */ compartment := 'zfs_oci'; /* # The REST api you want to call, with any required paramters. */ rest_api := URL || '/n/' || compartment || '/b/' || bucket || '/p?compartmentId=' || compartment ; /* # The host you want to make the call against */ host := 'zfs-s3.zfsadmin.vcn.oraclevcn.com'; /* # Get the private key to sign the request */ prvkey := 'MIIEowIBAAKCAQEAzdfuSSwga9ziJTQK6RKB86VkBRnsIUrNGKQVKAkGaG5V+NjjJXvZt3a7p70NykHD4ZKO5ShbTBxYMCHc6uIjAJe/5moGog267K4q1Wa1WJIcL0/H5t/MWKMjToQ+qIz2K21zsOCAgf9YBY3XoQsK3RvPcCO2zbp0g0PZ5cfOUxNGQP7o2aPltymx6OrzMMXWfr7vgnMWeif86AAgdh4yfyQmNzjZFjwz1RDwYQYA50iLGSEVf0UIxDqxe8qCIMzPe5J8Drhw10NKB95ZSj++GKZ6wpv1ScClzuxS2kZYHqWfIYpAZal/Xmpr5o6GVyMyeAYxZ3TnCsXH7PhrnIbb9QIDAQABAoIBADKIlTYfgCIayuXpPdh0M7T14HUkJQUTUQCAn7UBajJPDAoYbMMyAYP5wR3v2qfRt130VeJKIwevWbMPTSXYuFEX8AOH+fLkvgQGyfszyMdir0qqImA6hvcnra5D5wIIARMuPp2or8O6RQiwBsTBajxX7HgSPHmLEbl7+c1gXPWlr4f1Qu7Fzas3plNjdkTRb+Dewr6bE1k/EVhEGm/LIHZn6bObx2pUZ2RHSVWuyZjqgLN7mcmC0z9tMumvdAVbi6SDNsp6wfk21kkywo5eKe2nYjqm4ezWCYX7ZLsjJg0jIzjO0Y7u1x/8lyvsgVuwU74SY84hOgJYEQy7MG9HZ+ECgYEA+1Qm6XT1H/YX9O+bZ3eEZH6AgTAq9PIgqWV8+1InvH4u6HT5OMccxc2RsxxCUXarv3jGJAme43jLaCeP/CkjBhcVpRQT1PLv4k8Sf4ABO3GLEP2qdM7sk/dJbFbDplO1sS0ZZh8AKcntLzN99trvZ+E9BvJXmBroyjstoJSUg20CgYEA0ata94Q9SOFleMK82Oflm+gs7zbuyebSmh6lBn8gal0szW5nOsKce48ht71nRFQU4rLCkglcjxH8s8+BSVDaoo05LeR8SDKpPV1qdd0itVFe+agHyOGuTlZnjh3u+B3xyRKn9tdSiUNopSZ1wMqy3p1/k8BgFjAAUvu6o+nD3akCgYATZaFDL1Wjy9h5fOyfhafgIatFTTRjhzRiHoUQ2Hxcndh89FovuZ5M3dq62hl8NA55TpbsPRnYMIByX2tLaNldJPb0sKH/M2i0LKoe0QBHWDIFc65uKXI00UcHZ4LeGWPScsqdCRN4Ji96Dx1PqgIEIGwXVyjPgnQo93xEAnOb2QKBgDjSaJ/FzdoPg7F7jTR3fEmJXYwi5gn/rTmpbm3ZTA6nW7n83qsVDNWLj8a85NymsNZTPdnYAaagaEzOwqn86JO/FCz0zB3A5Q7FX1G78WkUOVoNiLaPOu7uCbiBMj851kn47V4/MpYSl6ySRL1P8bPVGTsQmrnInn3ezNEZTESpAoGBAPRTl9d3l8LtlVD10qDyunMyvfeLXvBMSq4Nje7EfTT5wGwRUIFabvM/KSM0NMW1eaEKO2PxeTpcvVarJykbLh3caSra/ptJ0/Qm2F595iIuwNeAt3pMkpr+gVtp21re+vFw6JUw/3SUirXvPWPUogN+6h0/wlIcWQr7CCerY452'; /* # Set the current date/time to the correct format */ select to_char (systimestamp at time zone 'GMT','Dy, dd Mon yyyy hh24:mi:ss') || ' GMT' into vardate from dual; /* ########################################################################################## # # # Set rest API URL call # # # ########################################################################################## */ /* # The REST api you want to call, with any required paramters. */ rest_api := URL || '/n/' || compartment || '/b/' || bucket || '/p?compartmentId=' || compartment ; /* ########################################################################################## # # # Set The Post Request # # # ########################################################################################## */ post_request := 'POST http://' || host || rest_api || ' Date: ' || vardate; /* ########################################################################################## # # # Set The body as JSON # # # ########################################################################################## */ /* # the json file containing the data you want to POST to the rest endpoint */ object_name := 'shareddata/customer_sales.json_1_1_1.json'; body := '{' || chr(10) || ' "accessType": "ObjectRead",' || chr(10) || ' "bucketListingAction": "Deny",' || chr(10) || ' "bucketName": "' || bucket || '",' || chr(10) || ' "name": "mytestforjsonx1",' || chr(10) || ' "namespace": "' || compartment || '",' || chr(10) || ' "namespaceName": "' || compartment || '",' || chr(10) || ' "objectName": "' || object_name || '",' || chr(10) || ' "timeExpires": "2024-11-01T00:00:00+00:00"' || chr(10) || '}'; select utl_raw.cast_to_varchar2(utl_encode.base64_encode(dbms_crypto.hash (utl_i18n.string_to_raw (body || chr(10), 'AL32UTF8'),dbms_crypto.hash_sh256))) into content_sha256 from dual; /* ########################################################################################## # # # Set The signing string # # # ########################################################################################## */ content_sha256_header := 'x-content-sha256: ' || content_sha256 ; content_type_header := 'content-type: application/json'; content_length := length(body); content_length_header := 'content-length: ' || content_length ; date_header := 'date: ' || vardate; host_header := 'host: ' || host; headers := '(request-target) date host x-content-sha256 content-type content-length'; request_target := '(request-target): post ' || rest_api ; signing_string := request_target || chr(10) || date_header || chr(10) || host_header; signing_string := signing_string || chr(10) || content_sha256_header || chr(10) || content_type_header || chr(10) || content_length_header; sign_raw := DBMS_CRYPTO.SIGN ( src => UTL_I18N.STRING_TO_RAW(signing_string,'AL32UTF8'), prv_key => UTL_I18N.STRING_TO_RAW( prvkey, 'AL32UTF8'), pubkey_alg => kType, sign_alg => sType ); /* ########################################################################################## # # # encode the signing string in Base64 and build the final signature # # # ########################################################################################## */ select utl_raw.cast_to_varchar2(utl_encode.base64_encode(sign_raw)) into signature from dual; signature_final := ''; while position64 < length(signature) loop if position64 + 63 > length(signature) THEN signature_final := signature_final || substr(signature,position64,length(signature) - (position64-1)); else signature_final := signature_final || substr(signature,position64,64); end if; position64 :=position64 + 66; end loop; /* ########################################################################################## # # # Create the authorization header string including the signature string # # # ########################################################################################## */ authorization_header := 'Signature version="1",keyId="' || tenancy_ocid || '/' || user_ocid || '/' || fingerprint || '",algorithm="rsa-sha256",headers="(request-target) date host x-content-sha256 content-type content-length",signature="' || signature_final || '"'; /* ########################################################################################## # # # Send the post request to create the pre-authenticated URL # # # ########################################################################################## */ req := utl_http.begin_request('http://' || host || rest_api, 'POST',' HTTP/1.1'); utl_http.set_header(req, 'date', vardate); utl_http.set_header(req, 'x-content-sha256', content_sha256); utl_http.set_header(req, 'Content-Type', 'application/json'); utl_http.set_header(req, 'Content-Length', content_length); utl_http.set_header(req, 'Authorization', authorization_header); utl_http.write_text(req, body); /* ########################################################################################## # # # Read through the response and display the resulting URL # # # ########################################################################################## */ res := utl_http.get_response(req); begin loop utl_http.read_line(res, buffer); dbms_output.put_line(' '); json_obj := json_object_t.parse (buffer); url_string := json_obj.get('accessUri').to_string; dbms_output.put_line('URI to use : http://' || host || substr(url_string,2,length(url_string)-2)); dbms_output.put_line(' '); end loop; utl_http.end_response(res); exception when utl_http.end_of_body then utl_http.end_response(res); end; END; /

Step #2a Declare variables

The first part of the pl/sql package declares the variables that will be used in the package. Most of the variables are normal VARCHAR variables, but there a re a few other variable types that are specific to the packages used to encrypt and send the URL request.

• sType,kType - These are constants used to sign the URL request with  RSA 256 encryption
• utl_http.req,utl_http.resp - These are request and response types used when accessing the object storage
• json_obj - This type is used to extract the url from the resulting JSON code returned from the object storage call. 

Step #2b Set variables

In this section of code I set the authentication information along with the host, and the private key part of my RSA public/private key pair. 

I also set a variable with the current date time, in the correct GMT format.

NOTE: This date time stamp is compared with the date time on the ZFSSA. It must be within 5 minutes of the ZFSSA date/time or the request will be rejected.

Step #2c Set JSON body

In this section of code, I build the actual request for the pre-authenticated URL.  The parameters for this are...

• accessType - I set this to "ObjectRead" which allows me to create a URL that points to a specific object.  Other options are Write, and ReadWrite.
• bucketListingAction - I set this to "Deny",  This disallows the listing of objects.
• bucketName - Name of the bucket
• name - A name you give the request so that it can be identified later
• namespace/namepaceName - This is the ZFS share
• objectName - This is the object name on the share that I want the request to refer to. 
• timeExpires - This is when the request expires.

NOTE: I did not spend the time to do a lot of customization to this section.  You could easily make the object name a parameter that is passed to the package along with the bucketname etc. You could also dynamically set the expiration time based on sysdate.  For example you could have the request only be valid for 24 hours by dynamically setting the timeExpires.

The last step in this section of code is to create a sha256 digest of the JSON "body" that I am sending with the request.  I created it using the dbms_crypto.hash.

Step #2d Create the signing string

This section builds the signing string that is encrypted.  This string is set in a very specific format.  The string that is build contains.

(request-target): post /oci/n/{share name}/b/{bucket name}/p?compartment={compartment} 

date: {date in GMT}

host: {ZFS host}

x-content-sha256: {sha256 digest of the JSON body parameters}

content-type: application/json

content-length: {length of the JSON body parameters}

NOTE: This signing string has to be created with the line feeds.

The final step in this section is sign the signing string with the private key.

In order to sign the string the DBMS_CRYPTO.SIGN package is used.

Step #2e Build the signature from the signing string

This section takes the signed string that was built in the prior step and encodes the string in Base 64.  This section uses the utl_encode.base64_encode package to sign the raw string and it is then converted to a varchar.

Note: The resulting base64 encoded string is broken into 64 character sections.  After creating the encoded string, I loop through the string, and combine the 64 character sections into a single string.

This took the most time to figure out.

Step #2f Create the authorization header

This section dynamically builds the authorization header that is sent with the call.  This section includes the authentication parameters (tenancy OCID, User OCID, fingerprint), the headers (these must be in the order they are sent), and the signature that was created in the last 2 steps.

Step #2g Send a post request and header information

The next section sends the post call to the ZFS object storage followed by each piece of header information.  After header parameters are sent, then the JSON body is sent using the utl_http.write_text call.  

Step #2h Loop through the response

This section gets the response from the POST call, and loops through the response.  I am using the json_object_t.parse call to create a JSON type, and then use the json_obj.get to retrieve the unique request URI that is created.

Finally I display the resulting URI that can be used to retrieve the object itself.

Documentation

There were a few documents that I found very useful to give me the correct calls in order to build this package.

Signing request documentation : This document gave detail on the parameters needed to send get or post requests to object storage.  This document was very helpful to ensure that I had created the signature properly.

Http message signature format : This document gives detail on the signature itself and the format.

OCI rest call walk through : This post was the most helpful as it gave an example of a GET call and a PUT call. I was first able to create a GET call using this post, and then I built on it to create a GET call.