This discussion is archived
7 Replies Latest reply: Dec 9, 2012 10:13 PM by 817136 RSS

Usage of  V('APP_USER')

817136 Newbie
Currently Being Moderated
Hi All,

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.

Help please.

Regards,

Joshua
  • 1. Re: Usage of  V('APP_USER')
    jariola Guru
    Currently Being Moderated
    Hi,

    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.



    Regards,
    Jari
    -----
    My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
    Twitter: http://www.twitter.com/jariolai
  • 2. Re: Usage of  V('APP_USER')
    817136 Newbie
    Currently Being Moderated
    Hi Jari,

    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'));





    Regards,
    Joshua
  • 3. Re: Usage of  V('APP_USER')
    817136 Newbie
    Currently Being Moderated
    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
    BEGIN
    select V('APP_USER') into :new.apex_user from dual;
    select USER into :new.database_user from dual;
    END;
    /

    and not in a after logon on database trigger?

    Regards,

    Joshua
  • 4. Re: Usage of  V('APP_USER')
    evrm Pro
    Currently Being Moderated
    Hi Joshua,

    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.

    regards,
    Erik-jan
  • 5. Re: Usage of  V('APP_USER')
    chrdei Newbie
    Currently Being Moderated
    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.
  • 6. Re: Usage of  V('APP_USER')
    Vite DBA Pro
    Currently Being Moderated
    Hi Joshua,

    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.

    Regards
    Andre
  • 7. Re: Usage of  V('APP_USER')
    817136 Newbie
    Currently Being Moderated
    Thanx all,

    I didn't know that the APP_USER wasn't available just after LOGON.
    I will use "Initialization PL/SQL Code" under Shared Components -> Definition -> Security in APEX.

    Thanx.

    Regards,
    Joshua

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points