5 Replies Latest reply on Jan 24, 2020 10:55 AM by Paavo

    procedure.rest.preHook, create/attach ras session for sso user ?

    Paavo

      APEX, ORDS 19.2+ in 12.2+ rdbms.

       

      Apache-Tomcat(ords)-12.2+

      sso -- prehook

       

      Prerequisities working - based on this: https://www.doag.org/formes/pubfiles/11293573/2019-APEX-Dietmar_Aust-Oracle_ORDS_-_New_Features_You_Need_to_Know_About_-… 

      - ras configured + ras enabled apex application works with dynamic roles for SSO_USER via header variable from the Apache

      - ords procedure.rest.preHook function works and I can log the environments the prehook function can see, especially interesting is the SSO_USER "leaks through" simply with OWA_UTIL.get_cgi_env('SSO_USER')

       

      Now before starting furious rtfm-iterations with the RAS stuff, need to ask how to set the RAS properly and is there need to have some-sort-of posthook to clean the table?

       

      And yes, I am looking sort of "ras enabled rest services on ords"-setup which I can easily toggle on by cloning ords setup for rest services from the ords serving apex and then prehooking it with the strict-ras.

      Especially scheme with dynamic role style, where users are not managed inside the database but outside. In-case rbac would be needed then the prehook or the ras code could check the role if needed from external dir.serv..

      nb. I sense that then RAS is trusting perhaps literally too much on Apache-sso and there will be need to add extra security on top-of the rest, but this sounds more like rtfm.

       

      rgrds Paavo

        • 1. Re: procedure.rest.preHook, create/attach ras session for sso user ?
          Paavo

          FYI:

           

          Placed ords prehook function and its xlog-table to "ORDSHOOK" schema, so that it can be used when accessing any other parsing schema's rest api's.

          Granted the function and tapi for xlog for the schemas.

          Changed to ords conf default.xml to use ordshook.prehookfunc

           

          Now I am able to convey the SSO_USER to ords prehooked rest get which is fetching environment variables from view like this:

          CREATE OR REPLACE FORCE EDITIONABLE VIEW "V_USERENVS" ("LBL", "CONT") AS 
            select lbl,cont from (
          select 'APP_ID' lbl, v('APP_ID') cont from dual
          union all
          select 'APP_SESSION' lbl, v('APP_SESSION') cont from dual
          union all
          select 'APP_USER' lbl, v('APP_USER') cont from dual
          union all
          select 'XS_SYS_CONTEXT(''XS$SESSION'',''CREATED_BY'')' lbl, XS_SYS_CONTEXT('XS$SESSION','CREATED_BY') cont from dual
          union all
          select 'XS_SYS_CONTEXT(''XS$SESSION'',''USERNAME'')' lbl, XS_SYS_CONTEXT('XS$SESSION','USERNAME') cont from dual
          union all
          select 'RAS_HANDLER_PKG.F_GET_APP_USER()' lbl, RAS_HANDLER_PKG.F_GET_APP_USER() cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''CURRENT_USER'')' lbl,sys_context('userenv','current_user') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''CURRENT_SCHEMA'')' lbl,sys_context('userenv','current_schema') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''AUTHENTICATED_IDENTITY'')' lbl,sys_context('userenv','authenticated_identity') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''SESSION_USER'')' lbl, SYS_CONTEXT('USERENV','SESSION_USER') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''ACTION'')' lbl, SYS_CONTEXT('USERENV','ACTION') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''AUTHENTICATED_IDENTITY'')' lbl, SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''AUTHENTICATION_DATA'')' lbl, SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''AUTHENTICATION_METHOD'')' lbl, SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''CLIENT_IDENTIFIER'')' lbl, SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''CLIENT_INFO'')' lbl, SYS_CONTEXT('USERENV','CLIENT_INFO') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''DB_DOMAIN'')' lbl, SYS_CONTEXT('USERENV','DB_DOMAIN') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''DB_NAME'')' lbl, SYS_CONTEXT('USERENV','DB_NAME') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''DB_UNIQUE_NAME'')' lbl, SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''ENTERPRISE_IDENTITY'')' lbl, SYS_CONTEXT('USERENV','ENTERPRISE_IDENTITY') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''HOST'')' lbl, SYS_CONTEXT('USERENV','HOST') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''IDENTIFICATION_TYPE'')' lbl, SYS_CONTEXT('USERENV','IDENTIFICATION_TYPE') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''INSTANCE_NAME'')' lbl, SYS_CONTEXT('USERENV','INSTANCE_NAME') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''IP_ADDRESS'')' lbl, SYS_CONTEXT('USERENV','IP_ADDRESS') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''ISDBA'')' lbl, SYS_CONTEXT('USERENV','ISDBA') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''MODULE'')' lbl, SYS_CONTEXT('USERENV','MODULE') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''NETWORK_PROTOCOL'')' lbl, SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''PROXY_ENTERPRISE_IDENTITY'')' lbl, SYS_CONTEXT('USERENV','PROXY_ENTERPRISE_IDENTITY') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''PROXY_USER'')' lbl, SYS_CONTEXT('USERENV','PROXY_USER') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''SESSION_USER'')' lbl, SYS_CONTEXT('USERENV','SESSION_USER') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''TERMINAL'')' lbl, SYS_CONTEXT('USERENV','TERMINAL') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''OS_USER'')' lbl, SYS_CONTEXT('USERENV','OS_USER') cont from dual
          union all
          select 'SYS_CONTEXT(''USERENV'',''POLICY_INVOKER'')' lbl, SYS_CONTEXT('USERENV','POLICY_INVOKER') cont from dual
          union all
          select 'SUBSTR(SYS_CONTEXT(''USERENV'',''CLIENT_IDENTIFIER''), 1 ,INSTR(SYS_CONTEXT(''USERENV'',''CLIENT_IDENTIFIER''), '':'', 1, 1)-1)' lbl
          ,SUBSTR(SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER'), 1 ,INSTR(SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER'), ':', 1, 1)-1) FROM dual
          ) order by lbl;
          

           

          And in the prehook function set the sso user like this:

          dbms_session.set_identifier(sso_user);
          

           

          so the rest get returns the sso_user "usernameviasso" like this:

          {"lbl":"SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')","cont":"usernameviasso"}
          
          

           

          But if I try to add XS session management to prehook function e.g. create, attach, assign. like e.g. this

              dbms_xs_sessions.create_session('DAUSTIN', sessID);
              dbms_output.put_line(sessID);
              dbms_xs_sessions.attach_session(sessID);
              dbms_xs_sessions.detach_session(TRUE);
              dbms_xs_sessions.destroy_session(sessID);
          

           

          The prehook fails to create_session

          SYS.DBMS_XS_SESSIONS.CREATE_SESSION(username  => application_user   --sso_user
                                             ,is_external  => TRUE
                                             ,sessionid => sessionid);
          

           

          ORA-46070: insufficient privileges
          

           

          So the need is to enable XS dynamic role for the GET and convey the SSO_USER for it.

          E.g. for the RAS enabled header sso authenticated apex application the same env view has the row for:

           

           

          XS_SYS_CONTEXT('XS$SESSION','USERNAME')

           

           

          where I can see the SSO_USER's value.

           

          My question is now how to create the XS session and have the dynamic role defined? What kind of user and privileges are needed for the ordshook.prehookfunction ?

          Or is there need to make xs proxy user or something much more easier? Now there are  :

          • 2 schemas :
            • ordshook - where the prehookfunc is
            • parsing_schema_x - where the data is
          • 1 sso_user : usernameviasso

          E.g. how it was done for the APEX ras enabled applicaton with the dynamic role selected?

           

          rgrds Paavo

          • 2. Re: procedure.rest.preHook, create/attach ras session for sso user ?
            Paavo

            Cont.. tried to give pretty excessive privileges, but can't figure out how and to which user to give those. Below futile attempt..
            Is this approach doomed somehow, and does the prehook work only for vpd setups which might be happy with the envs they get?

            Rather would like to see this done with RAS

            DECLARE
            ace_list  XS$ACE_LIST;
            BEGIN
                ace_list := XS$ACE_LIST(
                    XS$ACE_TYPE(privilege_list=>XS$NAME_LIST('"ADMINISTER_SESSION"','"CREATE_SESSION"','"MODIFY_SESSION"','"ATTACH_SESSION"'),       
                                granted=>true,
                                principal_name=>'ORDSHOOK')
                                );
                    sys.xs_acl.create_acl(name=>'MASTER_OF_SESSIONS_ACL',
                                    ace_list=>ace_list,
                                    sec_class=>'SESSIONPRIVS',
                                    description=>'Session management');
            END;
            /
            BEGIN
             SYS.XS_PRINCIPAL.CREATE_USER(name=>'ORDSHOOK',
                                          schema=>'ORDSHOOK',
                                          acl=>'MASTER_OF_SESSIONS_ACL');
            END;
            /
            --ORA-46222: Real Application principal name ORDSHOOK conflicts with another user or role name.  <-- this is of course expected
            -- but is there need to create yeat another user, so there would be there even more users : ordshook, parsingschemax, ssouser ...
            
            -- to be able to manage the XS ras session?
            EXEC  SYS.XS_PRINCIPAL.SET_PASSWORD('ORDSHOOK', 'somepass');
            

             

            rgrds Paavo

            • 3. Re: procedure.rest.preHook, create/attach ras session for sso user ?
              Paavo

              FYI: if I have understood correctly the XS session for the rest get should go through steps:

              • create session
              • attach session
              • assign user

               

              But can't get it right - some errors thrown from the prehook function to xlog-table:

              1. ORA-46063: not attached to XS Security session -- if try to assign user w/o create+attach
              2. ORA-46070: insufficient privileges - if try to create session for sso_user etc.
              3. ORA-46060: user name not specified - if try leave username out from create session - (how anon ras session is made, or is it relevant here?)
              4. ORA-46079: invalid external principal specified - if created XS principal and tried to use it as username for the create session
              5. ORA-01031: insufficient privileges - if try SYS.XS_PRINCIPAL.ADD_PROXY_USER(target_user => 'ORDSHOOKXS',proxy_user=¨TEMPEXTXSPRINCIPAL1)
              6.      required grant alter user to ordshook (owner of prehook function)
              7. ORA-46215: XS entity by the name TEMPEXTXSPRINCIPAL1 did not exist. -- after grant

               

              So it would be good to know the correct way to drum the RAS XS session for the dynamic role, like for the APEX ras enabled application with dynamic roles defined.

              Some of these ORA-errors are perhaps a bit fuzzy what should be tried next, just changing e.g. principal name wont help.

               

              Any ideas how to proceed?

               

              rgrds Paavo

              • 4. Re: procedure.rest.preHook, create/attach ras session for sso user ?
                Paavo

                FYI:

                 

                Now after several ORA-errors, managed to fiddle the prehook so that it allows to do XS: create,attach,assign steps.

                But now the rest get doesn't return data

                desc

                DBMS_XS_SESSIONS.ASSIGN_USER enable_dynamic_roles: MY_DYN_APP_ROLE2
                DBMS_XS_SESSIONS.ASSIGN_USER is_external: TRUE
                DBMS_XS_SESSIONS.ASSIGN_USER username: myssouser
                DBMS_XS_SESSIONS.ATTACH_SESSION enable_dynamic_roles: MY_DYN_APP_ROLE2
                DBMS_XS_SESSIONS.ATTACH_SESSION sessionid: 9CCF0806A03A0C05E053830EB183D470
                DBMS_XS_SESSIONS.CREATE_SESSION is_external: TRUE
                DBMS_XS_SESSIONS.CREATE_SESSION sessionid: 9CCF0806A03A0C05E053830EB183D470
                DBMS_XS_SESSIONS.CREATE_SESSION username : XSGUEST
                dbms_session.set_identifier(sso_user); : myssouser
                

                 

                So pretty close.. but.

                 

                rgrds Paavo

                • 5. Re: procedure.rest.preHook, create/attach ras session for sso user ?
                  Paavo

                  FYI: the create, attach, assign steps work if they are executed in sqldeveloper in the parsing schema.

                  But I think the step 4.) in the picture is already starting in XS session and this doesn't allow the call?

                   

                  So basically prehook is not returning sessionid to be consumed in the 4.) but just yes/no + some header vars for internal processing.

                  Now the question is - where the XS session setup "prehook" could be made to allow RAS ?

                  It should be made for the step 4.)

                   

                  rgrds Paavo