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. 

File Retention on ZFS now supports expired file deletion/holds and changing permissions

 The latest release of ZFS (8.8.63) contains 2 new features associated with File Retention lock.

• File retention (deletion or hold) after file expiration
• Allow permission changes on retained files.

File retention on expiry policy

This new setting for projects/shares defaults to "off" which is the normal behavior of unlocking files, but leaving them on the filesystem.  In order to delete files you need to wait until the retention period expires, and then you can delete the file.

There are 2 new settings you can use to work with locked files to change this behavior.

Delete

When set to "Delete", files will be immediately deleted when their retention lock expires. This can be very useful if you want files to be automatically cleaned up at the end of their retention without having to create a deletion process.

There are a few items to be aware of the automatic deletion process.

1. DO NOT use this with an RMAN retention window.  Customers typically use a weekly full/daily incremental backup strategy with RMAN. With this strategy, a weeks worth of backups (all dependent on the oldest full backup) are deleted together.  Deleting backups as soon as they expire would delete backups too soon.  Even with archival backups I recommend letting RMAN perform the deletions, otherwise you risk having a file deleted too early.
2. Be careful changing this setting on an existing share.  This setting takes effect immediately and will affect ALL files that have a retention lock.  Any files that were locked, but their retention lock expired will be deleted when this setting is applied.

Hold

When set to "Hold", any files that have, or have had a retention lock will be affected.  This setting  immediately prevents the deletion of all retention locked files until the hold is removed regardless of when the lock is set to expire.  Keep in mind that while a hold is in place, the files still have a retention lock with an expiration date.

Removing the hold: When you remove the hold, the normal expiration date takes effect.  If you remove the hold by changing the expiry policy to "Delete", ALL files that have an expired retention will be immediately deleted.  If you change the expire policy to "Off", the files remain, and you must manually delete them.

NOTE: Be very careful when changing the Expiry Policy.  The new setting immediately affects existing files, not just new files going forward, unlike the other file retention settings.

Allow permission changes on retained files

What happens normally : When a file has a retention lock set, you are protecting this file from both being deleting AND from being updated. Because you were not allowed to update the file permissions, you were not able to change the settings from the default of -r--r-----+ while the file was locked.

This could be an issue depending on what type of file you are protecting. There are some cases where you want to make this file either

• An executable file, not just a read only file.
• readable by any user.

When you attempted to make this change to a locked file the update would fail with a "Operation not permitted".

-r--r-----+ 1 oracle oinstall 792 Dec  4  2023 testfile
[oracle@ssh-server rmanbackups]$ ls -al testfile
-r--r-----+ 1 oracle oinstall 792 Dec  4 20:26 testfile
[oracle@ssh-server rmanbackups]$ chmod 550 testfile
chmod: changing permissions of 'testfile': Operation not permitted
[oracle@ssh-server rmanbackups]$ chmod 444 testfile
chmod: changing permissions of 'testfile': Operation not permitted
[oracle@ssh-server rmanbackups]$

What this setting does: When you check the setting for "Allow permission changes on retained files" you are IMMEDIATELY able to change the permissions on files that are locked.  The files are still protected from making them writable, but you can adjust both the "r" - read and "x" execute settings for all users. 

NOTE: this setting does take affect immediately and will affect all currently locked files regardless of when they were created.