7 Replies Latest reply on Jul 4, 2016 10:49 AM by mark123

    ORDS v('APP_USER') equivalent

    Andrew IRL

      I have a OAuth first party cookie-based authentication setup as described here in the first 4 steps of section


      which works exactly as described in the example.


      But I'd like to create a service which returns "Hello hr_admin"


      So replace this:

           p_source => 'select * from emp order by empno desc'


           p_source => 'select <something> msg from dual'

      It seems v('APP_USER') is specific to APEX and doesn't work in ORDS, so what is the ORDS equivalent?


      Having the untrusted client pass their own username isn't an option, I need it to be validated.

        • 1. Re: ORDS v('APP_USER') equivalent
          Capt. Egg

          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 = ''.

          1 person found this helpful
          • 2. Re: ORDS v('APP_USER') equivalent
            Andrew IRL

            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:


            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?

            • 3. Re: ORDS v('APP_USER') equivalent
              Capt. Egg

              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.

              1 person found this helpful
              • 4. Re: ORDS v('APP_USER') equivalent
                Andrew IRL

                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.

                • 5. Re: ORDS v('APP_USER') equivalent



                  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.

                  • 6. Re: ORDS v('APP_USER') equivalent
                    Erik Raetz

                    You can do such a thing with a PL/SQL block service.


                    ORDS knows the following default bind-variables:

                    • :current_user
                    • :content_type
                    • :body




                    GET module/pattern


                    p_module_name => 'module', p_pattern => 'pattern', p_method => 'GET',
                    p_source_type => 'plsql/block',
                    p_source => 'DECLARE
                    shema.package.procedure_get(poauthclient => :current_user);
                    p_items_per_page => NULL


                    POST module/pattern


                    p_module_name => 'module', p_pattern => 'pattern', p_method => 'POST',
                    p_source_type => 'plsql/block',
                    p_source => 'DECLARE
                    shema.package.procedure_post(poauthclient => :current_user, pcontenttype => :content_type, prequest => :body);
                    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/

                    • 7. Re: ORDS v('APP_USER') equivalent

                      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 !