Zetetic's customized version of Oracle's page sentry to account for session ID discrepancy in Apex 4.0

oamPageSentry-apex4.sql

CREATE OR REPLACE function PASSPORT.oamPageSentry ( p_apex_user in varchar2 default 'APEX_PUBLIC_USER' )   
    return boolean   
as  
    l_cgi_var_name              varchar2(100) := 'REMOTE_USER';  
    l_authenticated_username    varchar2(256) := upper(owa_util.get_cgi_env(l_cgi_var_name));  
    --  
    l_current_sid               number;
    l_url_sid                   varchar2(4000);
    l_url                       varchar2(4000);
    l_app_page                  varchar2(4000);
    
begin  
    -- check to ensure that we are running as the correct database user  
    if user != upper(p_apex_user) then   
        return false;   
    end if;  
  
    if l_authenticated_username is null then  
        return false;   
    end if;       
    
    
    l_current_sid := apex_custom_auth.get_session_id_from_cookie;
    
    l_url := wwv_flow_utilities.url_decode2(owa_util.get_cgi_env('QUERY_STRING')); 
    
    wwv_flow.debug('oamPageSentry: request from ' || l_authenticated_username || ' (sid=' || l_current_sid || ') for ' || l_url);
    
    -- split on zero or more non-colon characters, and extract the URL session ID if it is present
    l_url_sid := REGEXP_SUBSTR(l_url, '[^:]*', 1, 5); 
    
    wwv_flow.debug('oamPageSentry: extracted current sid from url as ' || l_url_sid);
    
    -- the post_login call at the end of this function will blindly append the session ID to the URL, even if it is
    -- a deep link. Detect this condition, strip the duplicate session identifier, and redirect.
    if REGEXP_SUBSTR(l_url, '^.*:' || l_current_sid || ':.+:' || l_current_sid || '$') IS NOT NULL then
        l_url := REGEXP_REPLACE(l_url, ':' || l_current_sid || '$', '');
        wwv_flow.debug('oamPageSentry: identified duplicate session id on URL, stripping and redirecting to ' || l_url);
        owa_util.redirect_url('f?'|| l_url);
        return false;
    end if;
    
    -- apex 4.0 appears to have problems setting session variables (possibly due to new session validation)
    -- if the Session identifier present in the URL does not agree with the session identifier in the cookie
    -- detect this condition, and replace the invalid URL session identifier in the URL with the valid
    -- ID in from the cookie and redirect to the fixed URL
    if owa_util.get_cgi_env('REQUEST_METHOD') = 'GET' AND l_current_sid <> TO_NUMBER(l_url_sid) then
        l_url := REGEXP_REPLACE(l_url, '^(p=.+?:.+?):\d*(.*)$', '\1:' || l_current_sid || '\2');
        wwv_flow.debug('oamPageSentry: current sid ' ||l_current_sid || ' is diferent from url sid ' || l_url_sid || ', redirecting to url' || l_url);
        owa_util.redirect_url('f?'|| l_url);
        return false;
    end if;
    
    -- 1.  If the session is valid and the usernames match then allow the request
    -- 2.  If the session is valide but the usernames do not match, there may be session tampering going on. log the session out
    -- 3.  If the session id is not valid, generate a new session, and register it with apex
    if apex_custom_auth.is_session_valid then  
        apex_application.g_instance := l_current_sid;  
        wwv_flow.debug('oamPageSentry: current sid ' || l_current_sid || ' with username ' || apex_custom_auth.get_username || ' is valid');
        
        if l_authenticated_username = apex_custom_auth.get_username then  
            
            wwv_flow.debug('oamPageSentry: current session username ' || apex_custom_auth.get_username || ' equal to header username ' || l_authenticated_username);
            
            apex_custom_auth.define_user_session(  
                p_user=>l_authenticated_username,  
                p_session_id=>l_current_sid);        
            return true;  
        else 
            wwv_flow.debug('oamPageSentry: username ' || apex_custom_auth.get_username || ' mismatch with ' || l_authenticated_username || ' loggout');
            apex_custom_auth.logout(  
                p_this_app=>v('APP_ID'),  
                p_next_app_page_sess=>v('APP_ID')||':'||nvl(v('APP_PAGE_ID'),0)||':'||l_current_sid);  
            apex_application.g_unrecoverable_error := true; -- tell apex engine to quit              
            return false;  
        end if;  
          
    else -- application session cookie not valid; we need a new apex session
        wwv_flow.debug('oamPageSentry: current session ' || l_current_sid || ' is not valid');
        
        l_current_sid := apex_custom_auth.get_next_session_id;
        
        wwv_flow.debug('oamPageSentry: generated new session id ' || l_current_sid);
        
        apex_custom_auth.define_user_session(  
            p_user=>l_authenticated_username,   
            p_session_id=> l_current_sid );  
            
        apex_application.g_unrecoverable_error := true; -- tell apex engine to quit  
        
        if owa_util.get_cgi_env('REQUEST_METHOD') = 'GET'  then  
            wwv_flow.debug('oamPageSentry: GET request, remembering deep link ' || l_url);
            wwv_flow_custom_auth.remember_deep_link(p_url => 'f?'|| l_url );  
        else
            l_url := 'f?p='||  
                to_char(apex_application.g_flow_id)||':'||  
                to_char(nvl(apex_application.g_flow_step_id,0))||':'||  
                to_char(apex_application.g_instance);
            
            wwv_flow.debug('oamPageSentry: POST request, remembering deep link ' || l_url);
            
            wwv_flow_custom_auth.remember_deep_link(p_url=> l_url );  
        end if;  

        -- in previous versions of apex the remember_deep_link call would actually work and cause
        -- post_login to redirect to the target URL. This doesnt work any more in 4.0. Instead,
        -- we'll pass the target page in to the post_login call directly. Post login will blindly
        -- append the session ID to the end of p_app_page when it redirects, but we
        -- clean that up with the first cleanup redirect at the beginning of the function
        l_app_page := SUBSTR(l_url, 3, LENGTH(l_url) - 2);
        wwv_flow.debug('oamPageSentry: post_login for ' || l_authenticated_username || ' app_page ' || l_app_page );
        apex_custom_auth.post_login(   
            p_uname      => l_authenticated_username,  
            p_session_id => nv('APP_SESSION'),  
            p_app_page => l_app_page
        );  
            
            
        return false;          
    end if;      
end oamPageSentry;
/

This code is designed to solve a known issue in APEX 4.0.2 (Issue # 10347091). Oracle has identified this issue and released a patch on MetaLink, public notes about the issue are here