APEX

We are trying to find a technique to authenticate a user against an existing oracle user account AND establish the session as that user id.

The preconfigured authentication schema Database Account Authentication allows an application to authenticate a user against an existing Oracle username and password but it is does not establish a session to the database as that user, nor is it running any code as that particular user. The actual user session is established by dbms_sys_sql.parse_as_user(), which will typically be the Apex anonymous account.

The requirement arises in a conversion project of an existing forms/reports application to Apex. Unfortunately there are 10,000’s of packages/procedures/functions that utilise “user” in sql or PL/SQL e.g. insert into table (user_id, insert_date, column1, ..) values (user, sysdate, value1 ..). In addition, database level security is used based on the Oracle user. A rewrite of all the procedures to use v(app_user) rather than “user” is no small task.

Would anybody have any ideas on how an authentication schema could be created in Apex 4.0 that enables the user session to be set as the oracle login id rather than the scheme attached to the workspace? The Oracle documentation has been scoured but nothing obvious found.

Thanks

Occam