This discussion is archived
5 Replies Latest reply: Sep 28, 2011 5:02 AM by Patrick Wolf RSS

prevent spoofing of v('app_user') via apex_custom_auth.set_user?

256719 Newbie
Currently Being Moderated
I’ve been using the expression nvl(v(‘APP_USER’),user) in triggers and procedures until I realized that the value for APP_USER can be set without authentication via the APEX_CUSTOM_AUTH.SET_USER or APEX_CUSTOM_AUTH.DEFINE_USER_SESSION procedure… which would allow my audit and authentication code to be circumvented when DML is issued outside of the Apex application.

It looks like my code could check APEX_CUSTOM_AUTH.IS_SESSION_VALID before using v(‘APP_USER’) and that should prevent spoofing outside of the Apex environment – however, it in SQL Workshop, a user could still call APEX_CUSTOM_AUTH.SET_USER and spoof my code.

So, my questions are:

1) Outside of Apex, is checking APEX_CUSTOM_AUTH.IS_SESSION_VALID before using v(‘APP_USER’) sufficient or could a valid session be set up via other APEX_CUSTOM_AUTH calls outside of the Apex environment?

2) Is there any way to prevent an unauthenticated call to APEX_CUSTOM_AUTH.SET_USER or APEX_CUSTOM_AUTH.DEFINE_USER_SESSION or otherwise prevent the value of APP_USER from being changed from within an Apex SQL Workshop session?

Please note that the solution is not to disallow access outside of the Apex interface -- my trigger and procedure code needs to coexist within Apex and non-Apex applications.
  • 1. Re: prevent spoofing of v('app_user') via apex_custom_auth.set_user?
    256719 Newbie
    Currently Being Moderated
    It looks like apex_custom_auth.get_username() will do the trick. I can’t see where that can be spoofed by either calls to apex_custom_auth or directly setting globals in the apex_application package, using any global that looks like it might hold a user name.

    This code shows that get_username remains null outside of apex, and is apparently reliable inside of apex:

    declare
    procedure pr
    is
    begin
    dbms_output.put_line(rpad('-',80,'-'));
    dbms_output.put_line('g_user : [' || apex_application.g_user || ']');
    dbms_output.put_line('g_current_user : [' || apex_application.g_current_user || ']');
    dbms_output.put_line('g_user_known_as: [' || apex_application.g_user_known_as || ']');
    dbms_output.put_line('get_user : [' || apex_custom_auth.get_user || ']');
    dbms_output.put_line('get_username : [' || apex_custom_auth.get_username || ']');
    end pr;
    begin
    pr;
    apex_custom_auth.set_user('spoofer');
    pr;
    apex_application.g_user := 'spoofed g_user';
    pr;
    apex_application.g_current_user := 'spoofed g_current_user';
    pr;
    end;

    However, for g_current_user the docs say “This user name is usually* the same as the authenticated user running the current page” (my emphasis). So I’m wondering if this might actually be unreliable at some point during page processing. I’ll probably log g_current_user vs g_user during testing and see if we get any anomalies.
  • 2. Re: prevent spoofing of v('app_user') via apex_custom_auth.set_user?
    Christian Neumueller Expert
    Currently Being Moderated
    Hi!

    That apex_custom_auth.get_username() will cause a query to the session table. That might be inefficient if your dml code affects lots of records. You could also check if apex_custom_auth.get_security_group_id() is not null to determine if v('APP_USER') can be trusted. It returns the session's workspace id, which can not be set outside of APEX, by a simpler global variable lookup.

    Regards,
    Christian
  • 3. Re: prevent spoofing of v('app_user') via apex_custom_auth.set_user?
    256719 Newbie
    Currently Being Moderated
    Thanks for the post, Christian.

    Checking apex_custom_auth.get_security_group_id() should work outside of apex, where it appears to return 0, but it won't help within SQL Worksheet.

    Thanks for the heads-up on performance, though -- I'll have to give that some thought.
  • 4. Re: prevent spoofing of v('app_user') via apex_custom_auth.set_user?
    682558 Journeyer
    Currently Being Moderated
    Hi All,

    You can set apex_custom_auth.get_security_group_id from outside ApEx:
    begin
    dbms_output.put_line('apex_custom_auth.get_security_group_id: '||apex_custom_auth.get_security_group_id);
    dbms_output.put_line('wwv_flow_api.get_security_group_id: '||wwv_flow_api.get_security_group_id);
                   for i in (
                        select workspace, schemas, workspace_id 
                        from apex_workspaces
                   ) loop
                        wwv_flow_api.set_security_group_id(i.workspace_id);
                        exit;
                   end loop;
    dbms_output.put_line('apex_custom_auth.get_security_group_id: '||apex_custom_auth.get_security_group_id);
    dbms_output.put_line('wwv_flow_api.get_security_group_id: '||wwv_flow_api.get_security_group_id);
    end;
    Regards

    Michael
  • 5. Re: prevent spoofing of v('app_user') via apex_custom_auth.set_user?
    Patrick Wolf Employee ACE
    Currently Being Moderated
    But only if the current user is one of the schemas assigned to the workspace to want to set.

    Regards
    Patrick
    -----------
    My Blog: http://www.inside-oracle-apex.com
    APEX Plug-Ins: http://apex.oracle.com/plugins
    Twitter: http://www.twitter.com/patrickwolf

Legend

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