Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORDS and RAS-enabled schema

Plamen PetrovOct 22 2024

I have a RAS-protected schema. Now I write ORDS REST services to retrieve the data. I would like to make these services “RAS-aware” - select only the data which the user executing them has rights to see. I searched the forums for clues, found couple of postings, but no definitive answer.

Here is my use case. Starting point is the HR RAS demo. The database is 19c or 23ai, on premises. ORDS - the latest. I RAS-enable the schema and make a service, something like this:

ords.define_handler(
   p_module_name    => 'v1',
   p_pattern        => 'emp',
   p_method         => 'GET',
   p_source_type    => 'json/collection',
   p_items_per_page => 25,
   p_mimes_allowed  => '',
   p_comments       => NULL,
   p_source         => 'select * from employees order by employee_id'
);

I also create two OAuth clients, roles and privileges and protect the v1 module. How to relate the first OAuth client id to the DAUSTIN application user and the second one to SMAVRIS? I can easily make a simple table to store this mapping, but the question remains - how to tell ORDS that it has to create and attach a XS application session before running the select statement and dispose of it afterwards?

Then we have the pre-hook… I tried to use it for that purpose and failed. Looks like I am not the only one… The pre-hook sounds like a good idea, but it is on a server-level (should be per ORDS enabled schema), there is no “post-hook” etc. It is meant for other use cases, like logging.

If I write my services in PL/SQL then I can do whatever I need to do and possibly solve my problem, but it will be a shame not take advantage of all the built-in ORDS capabilities .

Best,

Plamen

Comments
Post Details
Added on Oct 22 2024
0 comments
57 views