Sharing is caring. What if you have a resource you want to share with a trusted friend, partner, or system? And what if that resource was being managed by an Oracle Database?
For example, let’s suppose you would like to share your product inventory via an array of JSON objects via a REST API?
Wouldn’t it be nice if you could just do that via a single link?
PARs allow anyone with the link to access the resource. If you have ever used our Cloud’s Object Store, you have probably seen this:
Premise: you trust the person you choose to share the resource via the PAR. If you have the PAR, you have access to the resource.
ORDS Examples
🔖: ORDS Docs Managing PARs.
I’m going to take an existing resource in my Always Free Database and share it with…I guess you, via this blog.
The Resource: my Strava (exercise diary) activity from 2013 to Spring of 2024.
It’s protected. If you try to access it directly, you’ll get a HTTP 401 Unauthorized.
Creating the PAR
I’m going to use our new ORDS_PAR package to define a pre-authenticated request (link) for the REST API, which happens to be a GET on the collection of activities.
🔖: PL/SQL Reference Docs for ORDS_PAR package.
DECLARE
l_uri clob;
BEGIN
l_uri := ORDS_PAR.DEFINE_FOR_HANDLER(
p_module_name => 'thatjeffsmith',
p_pattern => 'workouts/',
p_method => 'GET',
p_duration => 2592000
);
COMMIT;
END;
/We’re not generating a PAR for the entire resource and all of it’s handlers, but only for a very specific one, the GET. And the duration I’ve defined is, according to google, the number of seconds in 30 days.
I’m going to throw in an additional DBMS_OUTPUT line to print the contents of L_URI.
OK, now what?
We’ll take the uri attribute in the response of our function call, and use that to construct our cURL request.
Just to confirm the REST API is protected, if we try to access it, sans PAR / Token, we get the expected 401:
But, if we construct the request to include the token –
Here, you try!
Click this Link to see Jeff’s Strava Data via the PAR URL
I created this resource around 3PM Eastern Time US on the 7th of March, so it should be good until April the 7th.
What about…?
I change my mind, revoke the PAR!
No worries, simply run this –
BEGIN
ORDS_PAR.REVOKE_PAR(
p_par_token => 'Pc7nVorz...literalTokenStringHere'
)
COMMIT;
END;
/
How much time is left for my PAR before it expires?
The ORDS_PAR package include few functions, one to check if a PAR is valid, and another to return the amount of time, in seconds, a valid PAR has until it expires. So I can run a query like so to see how much time is left…
DECLARE
x integer;
time_left varchar2(256);
BEGIN
x := ORDS_PAR.par_expiration(p_par_token => 'Pc7nVorzfpf2KwounayJ5cEBlmaqWR6pCvW9NpFab9o5LyYHgj7wZt9kGhpcP9g5XGMQheyNTp6WSfDGHx9XuntgCOJc3NgFtF056BGNEeeYxeXU7ircg9r1');
SELECT FLOOR(X / (24 * 60 * 60)) || ' days, ' ||
FLOOR(MOD(X, (24 * 60 * 60)) / (60 * 60)) || ' hours, ' ||
FLOOR(MOD(x, (60 * 60)) / 60) || ' minutes, ' ||
MOD(x, 60) || ' seconds' into time_left
FROM DUAL;
dbms_output.put_line(time_left);
end;
/And running that shows me…
I want to share a PAR so people can add their own records? Can I do this with PUTs and POSTS? Yes!
The ORDS_PAR.DEFINE_FOR_HANDLER API allows me to submit any P_METHOD type that I want, not just GETs.
So I’ve created another endpoint and generated a PAR for it, as well.
I’ve added another API where you can let me know you found this post and made it this far. Here’s a cURL you can try –
curl --location --request POST \
'https://pvqhdhmzqnn1hi4-tjsatp.adb.us-ashburn-1.oraclecloudapps.com/ords/build_stuff/_/par/yp2qqQwRUoSEgUyXbm4BFUTaUToAGrVc1A6VCcBK7LZJkwwWIHFYWbXf4hgxFrVI6uRrFdhAqOdyRZkLSyrbZtFefHhJ7Ubsrw5joYZUMDShzlnjHRVWgb3viUsPVZ/thatjeffsmith/i-see-you' \
--header 'Content-Type: application/json' \
--data-binary '{
"name": "change your name",
"message": "add your message"
}'The UI for managing and working with PARs will come later this year.
So for now you can use these PL/SQL APIs (Docs).
The Code
Here’s my code for the MODULE, templates, handlers, and the privilege used to protect the endpoints. My STRAVA table data I’ve talked about before, and you can guess what the REGISTER table looks like.
-- Generated by ORDS REST Data Services 24.4.1.r0381713
-- Schema: BUILD_STUFF Date: Fri Mar 07 09:35:36 2025
--
DECLARE
l_roles OWA.VC_ARR;
l_modules OWA.VC_ARR;
l_patterns OWA.VC_ARR;
BEGIN
ORDS.DEFINE_MODULE(
p_module_name => 'thatjeffsmith',
p_base_path => '/thatjeffsmith/',
p_items_per_page => 25,
p_status => 'PUBLISHED',
p_comments => 'blog demos and stuff');
ORDS.DEFINE_TEMPLATE(
p_module_name => 'thatjeffsmith',
p_pattern => 'workouts/',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'thatjeffsmith',
p_pattern => 'workouts/',
p_method => 'GET',
p_source_type => 'json/collection',
p_mimes_allowed => NULL,
p_comments => NULL,
p_source =>
'select id "$id",
activity_date occurred_on,
activity_name name,
activity_type type,
description,
distance
from strava');
ORDS.DEFINE_TEMPLATE(
p_module_name => 'thatjeffsmith',
p_pattern => 'workouts/:id',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'thatjeffsmith',
p_pattern => 'workouts/:id',
p_method => 'GET',
p_source_type => 'json/collection',
p_items_per_page => 25,
p_mimes_allowed => NULL,
p_comments => NULL,
p_source =>
'select *
from STRAVA
where ID = :id');
ORDS.DEFINE_TEMPLATE(
p_module_name => 'thatjeffsmith',
p_pattern => 'i-see-you',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'thatjeffsmith',
p_pattern => 'i-see-you',
p_method => 'POST',
p_source_type => 'plsql/block',
p_mimes_allowed => NULL,
p_comments => NULL,
p_source =>
'BEGIN
insert into registry (name, message, submitted_on) values (:name, :message, sysdate);
commit;
:response := ''Got it, thanks '' || :name || ''!'';
:status_code := 201;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
:response := ''Sorry cannot find who you are looking for, get with the program'';
:status := 400;
WHEN VALUE_ERROR
THEN
:response := ''Your name and message were, weird? Check inputs and try again.'';
:status := 400;
WHEN OTHERS
THEN
:response := ''Something bad happened, but we are not sure what, here is the system response: '' || SQLERRM;
:status := 400;
end;');
ORDS.DEFINE_PARAMETER(
p_module_name => 'thatjeffsmith',
p_pattern => 'i-see-you',
p_method => 'POST',
p_name => 'response',
p_bind_variable_name => 'response',
p_source_type => 'RESPONSE',
p_param_type => 'STRING',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.CREATE_ROLE(p_role_name => 'oracle.dbtools.role.autorest.BUILD_STUFF');
l_roles(1) := 'oracle.dbtools.autorest.any.schema';
l_roles(2) := 'oracle.dbtools.role.autorest.BUILD_STUFF';
l_patterns(1) := '/metadata-catalog/*';
ORDS.DEFINE_PRIVILEGE(
p_privilege_name => 'oracle.dbtools.autorest.privilege.BUILD_STUFF',
p_roles => l_roles,
p_patterns => l_patterns,
p_modules => l_modules,
p_label => 'BUILD_STUFF metadata-catalog access',
p_description => 'Provides access to the metadata catalog of the objects in the BUILD_STUFF schema.',
p_comments => NULL);
l_roles.DELETE;
l_modules.DELETE;
l_patterns.DELETE;
l_roles(1) := 'SODA Developer';
l_patterns(1) := '/soda/*';
ORDS.DEFINE_PRIVILEGE(
p_privilege_name => 'oracle.soda.privilege.developer',
p_roles => l_roles,
p_patterns => l_patterns,
p_modules => l_modules,
p_label => NULL,
p_description => NULL,
p_comments => NULL);
l_roles.DELETE;
l_modules.DELETE;
l_patterns.DELETE;
l_modules(1) := 'thatjeffsmith';
ORDS.DEFINE_PRIVILEGE(
p_privilege_name => 'strava.share.par.demo',
p_roles => l_roles,
p_patterns => l_patterns,
p_modules => l_modules,
p_label => 'Strava_Share_PAR DEMO',
p_description => 'Jeff''s personal Strava data from like 2013 to early 2024.',
p_comments => NULL);
l_roles.DELETE;
l_modules.DELETE;
l_patterns.DELETE;
COMMIT;
END;
2 Comments
Greate feature.
Can it be applied to autoRest also?
For the moment it’s limited to REST Modules and a particular handler, on a given template.