5 Replies Latest reply on Sep 28, 2011 12:02 PM by Patrick Wolf-Oracle

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

      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?
          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:

          procedure pr
          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;
          apex_application.g_user := 'spoofed g_user';
          apex_application.g_current_user := 'spoofed g_current_user';

          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-Oracle

            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.

            • 3. Re: prevent spoofing of v('app_user') via apex_custom_auth.set_user?
              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?
                Hi All,

                You can set apex_custom_auth.get_security_group_id from outside ApEx:
                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
                               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);

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

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