This content has been marked as final. Show 7 replies
There is a APEX session present. Using V('APP_USER') in a trigger on a table is working. Only logon on database isn't working the way a thought it would be.
What i want to achieve here is to use the apex_user to restrict the rows from table to be retrieved. APEX_USER_1 sees only his own rows etc.
The logon on database trigger will call a context package and in that package:
select V('APP_USER') into v_1 from dual;
DBMS_SESSION.SET_CONTEXT('VOORBEELD_CTX', 'VAR_1', NVL(v_1, 'EMPTY'));
Basically is my question:
why is V('APP_USER') working fine in:
CREATE OR REPLACE TRIGGER "SCHOOL"."TRIGGER1"
BEFORE INSERT OR UPDATE ON DOCENT
FOR EACH ROW
select V('APP_USER') into :new.apex_user from dual;
select USER into :new.database_user from dual;
and not in a after logon on database trigger?
app_user is saved in the package wwv_flow.g_user variable.
Before you can get the value there has to be a database connection, and after the connection is established Apex can get the session state.
The package variable wwv_flow.g_user variable is not initialized right after logon to the database.
That is why the USER function gives you as result apex_public_user and v('APP_USER') gives you null.
a logon event into Apex doesn't necessarily result in a database logon. The access method you use, whether it is the Apex Listener, Oralce HTTP server or the embedded PL/SQL gateway all use connection pooling and will only create a new DB session if demand requires. This means calling V('app_user') has no context when the app server connections are established and as these are used by multiple Apex connections, it probably wouldn't be correct anyway.
As chrdei pointed out, there are devices for setting fine grained security features in Apex, at the application level.