Data share - Part II

In this blogpost we will see about how to setup the share provider along with the necessary roles and privileges.
 
As an ADMIN user create a user called SHARE_PROVIDER, along with necessary privileges.
 
admin@ATP19C> create user share_provider identified by "Good2go1234!";
 
User created.
 
admin@ATP19C> grant connect, resource, unlimited tablespace, dwrole to share_provider ;
 
Grant succeeded.
 
admin@ATP19C>
 
then REST enable that schema using ORDS_ADMIN API
 
admin@ATP19C> begin
  2     ords_admin.enable_schema(
  3             p_schema =>'SHARE_PROVIDER',
  4             p_enabled => true,
  5             p_url_mapping_type => 'BASE_PATH',
  6             p_url_mapping_pattern => 'share_provider',
  7             p_auto_rest_auth => true);
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
Then enable the data sharing access for the schema.
 
admin@ATP19C> begin
  2     dbms_share.enable_schema(
  3             schema_name =>'SHARE_PROVIDER',
  4             enabled=>true );
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
Now login to the newly created user and run the below query to verify the required privileges to share objects
 
share-provider@ATP19C> select dbms_share.can_create_share(user) from dual;
 
DBMS_SHARE.CAN_CREATE_SHARE(USER)
---------------------------------
                                1
 
The value of 0 returned by the above query, indicates that the user doesn’t have privileges to share objects, and a value of 1 returned indicates the user have the required privileges to share objects.
 
Create an object storage bucket named DATA_SHARE_BUCKET to store the data share data in object storage and create a link to the object storage bucket and associate the access credentials with that bucket. Follow the steps listed here to access data in object store, we need to enable database user to authenticate itself with the object store using your OCI object store account and credentials.
 
share-provider@ATP19C> variable uri varchar2(95)
share-provider@ATP19C> exec :uri := 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/DATA_SHARE_BUCKET/o/';
 
PL/SQL procedure successfully completed.
 
share-provider@ATP19C>
share-provider@ATP19C> select object_name, bytes
  2  from dbms_cloud.list_objects(
  3             credential_name => 'SHARE_BUCKET_CREDENTIAL',
  4             location_uri => :uri );
 
no rows selected
 
create a named storage link that points to the object storage buckets URI.
 
share-provider@ATP19C> print uri
 
URI
------------------------------------------------------------------------------------------
https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/DATA_SHARE_BUCKET/o/
 
share-provider@ATP19C> begin
  2     dbms_share.create_cloud_storage_link(
  3             storage_link_name => 'data_share_storage_link',
  4             uri => :uri );
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
share-provider@ATP19C>
share-provider@ATP19C> @printtbl ' select * from user_lineage_cloud_storage_links '
STORAGE_LINK_NAME             : "DATA_SHARE_STORAGE_LINK"
STORAGE_LINK_ID               : "173"
URI                           : "https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/DATA_SHARE_BUCKET/o/"
METADATA_PATH                 : ""STORAGE_LINK"."DATA_SHARE_STORAGE_LINK""
CREDENTIAL_NAME               : ""
METADATA                      : ""
APPLICATION_ID                : "6"
CREATED                       : "07-APR-24 05.20.25.028435 PM +05:30"
UPDATED                       : "07-APR-24 05.20.25.028435 PM +05:30"
-----------------
 
PL/SQL procedure successfully completed.
 
Associate the storage link – created above with the OCI native credentials.
 
share-provider@ATP19C> begin
  2     dbms_share.set_storage_credential(
  3             storage_link_name => 'data_share_storage_link',
  4             credential_name => 'SHARE_BUCKET_CREDENTIAL' );
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
share-provider@ATP19C> @printtbl ' select * from user_lineage_cloud_storage_links '
STORAGE_LINK_NAME             : "DATA_SHARE_STORAGE_LINK"
STORAGE_LINK_ID               : "173"
URI                           : "https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/DATA_SHARE_BUCKET/o/"
METADATA_PATH                 : ""STORAGE_LINK"."DATA_SHARE_STORAGE_LINK""
CREDENTIAL_NAME               : "SHARE_BUCKET_CREDENTIAL"
METADATA                      : ""
APPLICATION_ID                : "6"
CREATED                       : "07-APR-24 05.20.25.028435 PM +05:30"
UPDATED                       : "07-APR-24 05.20.25.028435 PM +05:30"
-----------------
 
PL/SQL procedure successfully completed.