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