Autonomous
Database (ADB-S) users often interact with other resources in Oracle cloud
infrastructure (OCI) to perform various common operations such as accessing the
contents to and from the Object storage locations. In the past we have discussed
about the Credential objects, using this an ADB-S instance can access the other
resources (like OCI Buckets, Vault etc) , today we will discuss about OCI
Resource principal, what it is and how to configure them.
OCI
resource principal is principal type in Oracle Identity and Access Management
(IAM) that eliminates the need to create and configure OCI user credential
objects in the database. In other words, a resource principle
uses a certificate that is frequently refreshed to sign the API calls to
certain OCI services (e.g. Object Storage, Vault) and the authorization is
established through dynamic groups and IAM policies.
In
the remainder of this blogpost, I’m going to demonstrate how to create OCI
resource principal and use that to access OCI object storage contents.
Create
Dynamic Group and policy.
First,
we need to create dynamic group and policy, to be able to use Resource
principal authentication, that is we will be able to tell Identity and Access
management (IAM) that a given ADB-S should be able to access OCI buckets.
1.
In the OCI console, go to Identity &
Security >> Domains >> Dynamic Groups >> Create Dynamic
Groups
2.
Since I want my ADB-S instance to this
Dynamic Group, I need to add OCID of my database instance in the following
rule.
Now that we have created a Dynamic group that
includes our ADB-S instance, we can go ahead and create policy to allow this
resource to access other resources that resides in each compartment / tenancy.
1)
In the OCI console, go to Identity
& Security >> Policy >> create policy
2)
Add your policy statement in the plain
text or using a policy builder
The
above policy allows read and write access to OCI buckets.
Resource
principal is not enabled by default, In order to be able to use resource
principal in our ADB-S instance, we need to enable it using DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL()
method.
admin@ATP19C> EXEC
DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();
PL/SQL procedure successfully
completed.
The
above step enables resource principal for ADMIN user, if you like other
database users to call DBMS_CLOUD API using resource principal, then ADMIN user
can enable resource principal authentication for other database users as well.
admin@ATP19C> EXEC
DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL('DEMO_USER');
PL/SQL procedure successfully
completed.
admin@ATP19C> EXEC
DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL('DEMO');
PL/SQL procedure successfully
completed.
To
verify the Resource principal is enabled as follows, we can use the below query.
admin@ATP19C> select
grantee
2 from all_tab_privs
3 where table_name ='OCI$RESOURCE_PRINCIPAL'
4 and grantor ='ADMIN';
GRANTEE
---------------------
C##CLOUD$SERVICE
GRAPH$METADATA
DEMO_USER
RAJESH
DEMO
As
a final step of demonstration, we can use the resource principal to access the
object storage contents.
demo-user@ATP19C> variable
uri varchar2(100)
demo-user@ATP19C> exec
:uri := 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/MY_DEMO_BUCKET/o/EXPORT_DEMO/';
PL/SQL procedure successfully
completed.
demo-user@ATP19C> select
count(*)
2 from
dbms_cloud.list_objects(
3
credential_name =>'OCI$RESOURCE_PRINCIPAL'
4
, location_uri => :uri);
COUNT(*)
----------
1
As
you might have noticed, OCI$RESOURCE_PRINCIPLE
is the credential_name we need to specify in DBMS_CLOUD APIs whenever we
want to use resource principal authentication.
To
summarize, resource principal is a really neat Oracle IAM capability that
enables your OCI resources to access various OCI services through dynamic
groups and policies. Creating dynamic groups and policies can potentially be a
one-time operation since you can define your dynamic group such that it
includes all existing and future ADB-S instances in a given compartment.
Whenever you provision a new ADB-S instance, all you have to do would be to
enable resource principle for that instance via the DBMS_CLOUD_ADMIN API if the
instance needs to access other OCI services or resources. Much simpler and
easier than creating credential objects via auth
tokens or OCI
native authentication!
Pre-Authenticated URLs(PAR URLs) provides
secure, time limited access to specific datasets on Autonomous platform, which
can be shared with any REST clients, this feature allows the team to easily
share sensitive data without complex permission, ensuring that this access is
temporary and controlled. Also this PAR URLs can be invalidated at anytime,
adding another layer of security to data sharing, for more information of this
feature refer to the earlier blogpost on this topic.
Over the last few months, a series of new
features focusing on improving user experience and functionality was added,
these updates aimed to enhance convenience for both data providers – who create
and share the data – and data consumers – who analyse and use the data. Each of
these new features were created to provide more control, flexibility and
efficiency while maintaining the highest standard of data security.
Data consumers can now enjoy,
user-friendly web interface for viewing and interacting with shared data. This
enhanced web output UI is highly functional and designed to simplify consumer
experience.
Grouping and sorting: ability to group
and sort the data directly from the web interface.
Providers can pick columns where GROUP BY
is possible, and consumers easily can set it up, for example in the original dataset.
In addition to grouping and sorting, the
improved filtering makes navigating large datasets much simpler, consumers can
filter by various fields to narrow down exactly what they are looking for. The
goal is to make data exploration as intuitive as possible, allowing consumer to
focus on analysis rather than struggling with user interface.
The filter will be applied.
And the user will see the results like
this.
With the updated PAR capabilities, time/access
limited URL that only exposes the necessary data, keeping everything behind the
door will be like this
demo@ATP19C> declare
2
l_result long;
3
begin
4
dbms_data_access.get_preauthenticated_url(
5
schema_name => user
6
, schema_object_name => 'T'
7
, expiration_count => 100
8
, result => l_result
9
, column_lists => '{
"group_by_columns":["PRODUCT_TYPE","STATUS"]
10 ,
"order_by_columns":["PRODUCT_ID"]
11 ,
"filter_columns":["CUSTOMER"] }');
12
select json_query(l_result,'$' pretty) into l_result from dual;
13
dbms_output.put_line( l_result );
14 end;
15 /
{
"status" : "SUCCESS",
"id" : "W7CFSsa69dZFkWIB11lg1wp5iehHXjESei4DfLSXnYwihT44VBYRLt9qMA0rEE1C",
"preauth_url" :
"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/4YXCQr5oxuEOqbpq
DeSc8VdhJf8UKVfhw6Sgi1y2xYLszanujxUkcXm1rb4GgMlVEu8h2SSgoiM/data",
"expiration_ts" : "2025-04-03T01:59:11.904Z",
"expiration_count"
: 100
}
PL/SQL procedure successfully
completed.
demo@ATP19C> select jt.*
2 from
json_table( dbms_data_access.list_active_urls ,'$[*]'
3
columns (
4
schema_name varchar2(10) path '$.schema_name'
5
, object_name varchar2(10) path '$.schema_object_name'
6
, expiration_count number path '$.expiration_count'
7
, access_count number path '$.access_count'
8 , column_lists varchar2(140) format json
path '$.column_lists'
9
)
10 ) jt
;
SCHEM OBJECT_
EXPIRATION_COUNT ACCESS_COUNT COLUMN_LISTS
----- -------
---------------- ------------
--------------------------------------------------------------------------------------------------------------------------------------------
DEMO T 100 0
{"order_by_columns":["PRODUCT_ID"],"group_by_columns":["PRODUCT_TYPE","STATUS"],"filter_columns":["PRODUCT_TYPE","STATUS","CUSTOMER"]}
If the consumer needs more access, it is
not a problem – PAR URL can be easily extended. Previously providers were
limited in how long they could make the data accessible, this often resulted in
a rush to get the work done before the link get expired. Now, ADB allows us to extend
the PAR validity with just a few API calls, this means longer hassle-free data
sharing periods. This feature offers providers from regenerating URLs and offer
much more flexibility.
demo@ATP19C> declare
2
l_result long ;
3
begin
4
dbms_data_access.extend_url(
5
id =>
'W7CFSsa69dZFkWIB11lg1wp5iehHXjESei4DfLSXnYwihT44VBYRLt9qMA0rEE1C'
6
, extend_expiration_count_by => 30
7
, result => l_result );
8
select json_query(l_result,'$' pretty) into l_result from dual;
9
dbms_output.put_line( l_result );
10 end;
11 /
{
"status" : "SUCCESS"
}
PL/SQL procedure successfully
completed.
demo@ATP19C> select jt.*
2 from
json_table( dbms_data_access.list_active_urls ,'$[*]'
3
columns (
4
schema_name varchar2(10) path '$.schema_name'
5
, object_name varchar2(10) path '$.schema_object_name'
6
, expiration_count number path '$.expiration_count'
7
, access_count number path '$.access_count'
8
, column_lists varchar2(140) format json path '$.column_lists'
9
)
10 ) jt
;
SCHEM OBJECT_ EXPIRATION_COUNT ACCESS_COUNT COLUMN_LISTS
----- ------- ---------------- ------------
--------------------------------------------------------------------------------------------------------------------------------------------
DEMO T 130 0
{"order_by_columns":["PRODUCT_ID"],"group_by_columns":["PRODUCT_TYPE","STATUS"],"filter_columns":["PRODUCT_TYPE","STATUS","CUSTOMER"]}
demo@ATP19C>
However it should be noted that we cannot
reduce the access, by supplying negative value to the parameter EXTEND_EXPIRATION_COUNT_BY, doing so will end up with error like
this.
demo@ATP19C> declare
2
l_result long ;
3
begin
4
dbms_data_access.extend_url(
5
id =>
'W7CFSsa69dZFkWIB11lg1wp5iehHXjESei4DfLSXnYwihT44VBYRLt9qMA0rEE1C'
6
, extend_expiration_count_by => -70
7
, result => l_result );
8
select json_query(l_result,'$' pretty) into l_result from dual;
9
dbms_output.put_line( l_result );
10 end;
11 /
{
"status" : "FAILURE",
"return_message"
: "ORA-20001: extend_url attribute extend_expiration_count_by cannot be
negative"
}
PL/SQL procedure successfully
completed.
demo@ATP19C>
