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

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 dualunion allselect 'APP_SESSION' lbl, v('APP_SESSION') cont from dualunion allselect 'APP_USER' lbl, v('APP_USER') cont from dualunion allselect 'XS_SYS_CONTEXT(''XS$SESSION'',''CREATED_BY'')' lbl, XS_SYS_CONTEXT('XS$SESSION','CREATED_BY') cont from dualunion allselect 'XS_SYS_CONTEXT(''XS$SESSION'',''USERNAME'')' lbl, XS_SYS_CONTEXT('XS$SESSION','USERNAME') cont from dualunion allselect 'RAS_HANDLER_PKG.F_GET_APP_USER()' lbl, RAS_HANDLER_PKG.F_GET_APP_USER() cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''CURRENT_USER'')' lbl,sys_context('userenv','current_user') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''CURRENT_SCHEMA'')' lbl,sys_context('userenv','current_schema') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''AUTHENTICATED_IDENTITY'')' lbl,sys_context('userenv','authenticated_identity') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''SESSION_USER'')' lbl, SYS_CONTEXT('USERENV','SESSION_USER') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''ACTION'')' lbl, SYS_CONTEXT('USERENV','ACTION') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''AUTHENTICATED_IDENTITY'')' lbl, SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''AUTHENTICATION_DATA'')' lbl, SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''AUTHENTICATION_METHOD'')' lbl, SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''CLIENT_IDENTIFIER'')' lbl, SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''CLIENT_INFO'')' lbl, SYS_CONTEXT('USERENV','CLIENT_INFO') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''DB_DOMAIN'')' lbl, SYS_CONTEXT('USERENV','DB_DOMAIN') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''DB_NAME'')' lbl, SYS_CONTEXT('USERENV','DB_NAME') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''DB_UNIQUE_NAME'')' lbl, SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''ENTERPRISE_IDENTITY'')' lbl, SYS_CONTEXT('USERENV','ENTERPRISE_IDENTITY') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''HOST'')' lbl, SYS_CONTEXT('USERENV','HOST') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''IDENTIFICATION_TYPE'')' lbl, SYS_CONTEXT('USERENV','IDENTIFICATION_TYPE') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''INSTANCE_NAME'')' lbl, SYS_CONTEXT('USERENV','INSTANCE_NAME') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''IP_ADDRESS'')' lbl, SYS_CONTEXT('USERENV','IP_ADDRESS') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''ISDBA'')' lbl, SYS_CONTEXT('USERENV','ISDBA') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''MODULE'')' lbl, SYS_CONTEXT('USERENV','MODULE') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''NETWORK_PROTOCOL'')' lbl, SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''PROXY_ENTERPRISE_IDENTITY'')' lbl, SYS_CONTEXT('USERENV','PROXY_ENTERPRISE_IDENTITY') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''PROXY_USER'')' lbl, SYS_CONTEXT('USERENV','PROXY_USER') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''SESSION_USER'')' lbl, SYS_CONTEXT('USERENV','SESSION_USER') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''TERMINAL'')' lbl, SYS_CONTEXT('USERENV','TERMINAL') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''OS_USER'')' lbl, SYS_CONTEXT('USERENV','OS_USER') cont from dualunion allselect 'SYS_CONTEXT(''USERENV'',''POLICY_INVOKER'')' lbl, SYS_CONTEXT('USERENV','POLICY_INVOKER') cont from dualunion allselect '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

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

DECLAREace_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

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

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_ROLE2DBMS_XS_SESSIONS.ASSIGN_USER is_external: TRUEDBMS_XS_SESSIONS.ASSIGN_USER username: myssouserDBMS_XS_SESSIONS.ATTACH_SESSION enable_dynamic_roles: MY_DYN_APP_ROLE2DBMS_XS_SESSIONS.ATTACH_SESSION sessionid: 9CCF0806A03A0C05E053830EB183D470DBMS_XS_SESSIONS.CREATE_SESSION is_external: TRUEDBMS_XS_SESSIONS.CREATE_SESSION sessionid: 9CCF0806A03A0C05E053830EB183D470DBMS_XS_SESSIONS.CREATE_SESSION username : XSGUESTdbms_session.set_identifier(sso_user); : myssouser

So pretty close.. but.

rgrds Paavo

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