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.
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;
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.