I have made a simple APEX application. Now i want to know which apex user is connect to the database.
When i use V('APP_USER') in a BEFORE INSERT trigger on a table V('APP_USER') returns 'APEX_USER_1' while USER returns 'APEX_PUBLIC_USER'. Both correct.
If i do the same in a AFTER LOGON ON DATABASE trigger V('APP_USER') returns null while USER returns sometimes ruturns 'SYS' or 'DBSNMP' or 'APEX_PUBLIC_USER'
I have tried various schemas with the LOGON trigger like SYS, System, VPDADMIN etc.
You can get value from v('APP_USER) when there is APEX session.
It is not clear what you try achieve with AFTER LOGON ON DATABASE trigger.
My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
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.
Since the variable for APP_USER is not set at this moment you can not retrieve a value.
If you want to copy the value of the APP_USER var to your own e.g. package variable or session context, use "Initialization PL/SQL Code" under Shared Components -> Definition -> Security in APEX.
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.