1 Reply Latest reply on Jul 4, 2016 2:54 PM by mark123

    ORDS: Using Client Identifiers to Identify Application Users Not Known to the Database

    Andrew IRL

      This documentation:

      http://docs.oracle.com/database/121/DBSEG/authentication.htm#DBSEG99862

      describes the problem I have:

      "These application users are known to an application but unknown to the database. The CLIENT_IDENTIFIER attribute can capture any value that the application uses for identification or access control, and passes it to the database. The CLIENT_IDENTIFIER attribute is supported in OCI, JDBC/OCI, or Thin driver."

       

      Tomcat validates the user and passed the request on to ORDS.war, but how can I set the client_identifier so it (and global application context) can be used by RLS?  I want to define a service "SELECT * FROM table" without a predicate and have RLS be able to return the pertinent rows.

       

      Here's how to do it with APEX:

      https://jeffkemponoracle.com/2013/02/28/apex-and-application-contexts/

       

      How do I do the same in ORDS?

        • 1. Re: ORDS: Using Client Identifiers to Identify Application Users Not Known to the Database
          mark123

          A possible solution:

           

          CREATE OR REPLACE function f_set_client_id(i_data in varchar2) return number
          deterministic  -- this is important as you only ever want function to execute once per query
          as
          begin
          DBMS_SESSION.SET_IDENTIFIER(i_data);
          return 1;
          end;

          --------------------------------

          create table t_clients as

          --- some sample data
          select '001' as id, 'Amanda Somebody'  as  name from dual
          union
          select '002' as id, 'Bill Nobody'  as  name from dual
          union
          select '003' as id, 'Celia Who'  as  name from dual;

          select f_set_client_id('001') from dual;

          ------------------------------

          create view v_this_client

          --- use a view with a where clause to simulate RLS
          as
          select * from t_clients
          where sys_context('USERENV','CLIENT_IDENTIFIER') = id;

          -----------------------------

           

          --- test  that a select works

          select * from v_this_client
          where f_set_client_id('002') = 1;

           

          Assuming you want to use  :CURRENT_USER  in the service, your service would be

           

          select * from v_this_client

          where f_set_client_id(:CURRENT_USER) = 1;

           

          If you want your client_identifier ( = session identifer)   to be dependent on more than just the user, then you'd need to explicitly get extra info from the http headers and pass it into the "query function".