1 person found this helpful
If you are using SSO, you can use the pass the username HTTP header to the service as a parameter. This works quite well, RESTful services have great support for working with HTTP headers.
Otherwise, you might be out of luck since the the query runs with no knowledge of any APEX session information. Perhaps you could code a loopback request in an APEX "On Demand" process that passes APP_USER as a header. You could also access the REMOTE_ADDR header in the service and put a while condition in your query :REMOTE_ADDR = '127.0.0.1'.
Thanks Capt. Egg I'm currently in development trying to figure out which way to go. SSO setting the HTTP header sounds like a workable option. I was hoping ords.war might set something for me but apparently not.
So if I do set the header then I need to process it in every endpoint to figure out who the user is? I was trying some things out and have this defined as a parameter for an ORDS endpoint:
ords.define_parameter( p_module_name => l_module_name, p_pattern => l_pattern, p_method => l_method, p_name => 'REMOTE_USER', p_bind_variable_name => 'remote_user' p_source_type => 'HEADER', p_param_type => 'STRING', p_access_method => 'IN', p_comments => '');
If I want to validate all my endpoints do I need to add this parameter to all of them and add appropriate handling? I was hoping for a design that allowed something like this:
SELECT * FROM some_view;
where the view was auto-magically restricted to the correct user through client_identifier/context for a poor man's VPD/RLS. Similar to what Jeff Kemp did here in APEX: https://jeffkemponoracle.com/2013/02/28/apex-and-application-contexts/ (but he has real RLS).
Is ords.war using utl_http, can I directly access the response through that package as a session variable?
1 person found this helpful
I've very new to ORDS, I've read no documentation just used the APEX wizard for creating RESTful services, so I'm certainly not the best person to help out here. I did a quick test, created a view with OWA_UTIL.GET_CGI_ENV('REMOTE_USER') in the where clause. It threw an error as it seems OWA isn't initialised.
I find it hard to believe that a web service platform such as this wouldn't have easy access to some kind of browsing session information usable to filter down the results in the SQL query. Maybe they blocked it by design though to avoid security implications that don't apply to my particular ORDS use case.
Thanks again Capt. Egg. I have little to no experience with APEX and have spent many hours reviewing everything I can on ORDS, all official documentation, blogs, forums and YouTube videos. Creating an account and posting here was my last ditch effort.
Based on your suggestion I also tried just querying OWA_UTIL.GET_CGI_ENV('REMOTE_USER') from ORDS but got "ORA-06502: PL/SQL: numeric or value error" which is the same as what you get when run from inside SQL Developer, presumably isn't initialised, as you said.
I can get the browsing session information, the approach I am using is to set up parameters as in my previous post and then wrap all my queries in:
CREATE OR REPLACE FUNCTION query_name (header_args..) RETURN SYS_REFCURSOR AS
Then the endpoints are defined as "SELECT query_name(...) FROM dual", so it all works - just a horrible kludge that makes me feel queasy each time I look at it.
ORDS has a bit of a split personality. The "Query" GETs with all their seemingly useful added value seem to have been coded in the middle layer by java people who don't really worry too much about databases. Everything else (POST,PUT,DELETE and Query PL/SQL) goes straight back to the database and thus things like OWA_UTIL work because its also needed by APEX.
If you aren't that into HATEOAS and don't need Rels etc (most of us don't I suspect) then I would suggest you just bin the idea of using the Query GETs and write PL/SQL ones which call apex_json.write (p_name,p_cursor) ("Signature 13" in the reference and you'll need Apex 5 installed even if you don't want to use Apex) - which you could wrap in your own procedure/function so that you pass in a literal "Select" statement -however, once you've made the step of actually writing code, you might quite like the idea of the source table/view showing up as a dependency so you probably shouldn't. This approach also makes it easier to have a POST/PUT/DELETE generate a response without having to set x-apex-forward to make a separate database call which doesn't remember anything about what you've just done (the joys of statelessness).
Which leads on to the actual usefulness of the "graphical" interface in Apex/SQLDeveloper. Very nice to play about with, but in a world where we need robust production code where changes can be easily tracked and promoted, I suspect the safe answer is to do real development by creating packaged procedures on the database which call the ORDS package either directly or through suitable wrappers. Just need to remember to run the code once in order to "install" your services.
It would be nice to get the procedure.preprocess handler working for ORDS as this would enable you to do a lot of standard stuff without having to include it in every service. In order to be useful, it needs to be in the same database call as the service and I suspect the way the Query GETs have been done is a major obstacle to getting it working.
You can do such a thing with a PL/SQL block service.
ORDS knows the following default bind-variables:
GET module/pattern ords.define_handler( p_module_name => 'module', p_pattern => 'pattern', p_method => 'GET', p_source_type => 'plsql/block', p_source => 'DECLARE BEGIN shema.package.procedure_get(poauthclient => :current_user); END;', p_items_per_page => NULL ); POST module/pattern ords.define_handler( p_module_name => 'module', p_pattern => 'pattern', p_method => 'POST', p_source_type => 'plsql/block', p_source => 'DECLARE BEGIN shema.package.procedure_post(poauthclient => :current_user, pcontenttype => :content_type, prequest => :body); END;', p_items_per_page => NULL );
Client data is accessible from the view: user_ords_clients
select name INTO lname from user_ords_clients where client_id = poauthclient;
For more about bind variables and http headers check: https://www.smartdogservices.com/ords-use-case-integrating-salesforce-com-customers-to-ebs/
In which case the answer to the original question is simply:
select 'Hello '||:current_user as greeting from dual
and he doesn't need to use a pl/sql get - which if you do use them means you're on your own as far as relational -> json mapping goes unless you've also installed Apex 5 - and even then you'll need to code your own paging, RELs etc.
Thanks for the link. I knew about :body and :content_type but hadn't noticed :current_user (for reasons far too long and complicated to explain we are authenticating elsewhere anyway so it doesn' ) and it doesn't seem to get a mention anywhere in the Oracle ORDS "documentation" - but then that isn't exactly surprising...
In fact the only mention of "current_user" is an example which demonstrates passing in a user name from the URL and using sys_context('USERENV', 'CURRENT_USER') if the URL value is missing - which of course would return the parsing schema name rather than anything to do with the client !