This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Oct 28, 2013 10:47 AM by CaioAndreatta RSS

Apex 4.2 - Page sentry error

VANJ Journeyer
Currently Being Moderated
When I login to my workspace (vikasa) on apex.oracle.com and run app 24317, I get an error

Error     Error processing session sentry function.
          ORA-02291: integrity constraint (APEX_040200.WWV_FLOW_DATA_SESSION_FK) violated - parent key not found

When I start a new browser session and run it directly http://apex.oracle.com/pls/apex/f?p=24317:500 without logging in to the App Builder, there is no error. In the same browser session, when I login to the Builder, again I can't reproduce the error.

Not sure what is going on. The Authentication scheme uses a custom page sentry function as follows http://pastie.org/5103748
  • 1. Re: Apex 4.2 - Page sentry error
    Christian Neumueller Expert
    Currently Being Moderated
    Hello Vikas,

    sorry, it seems like I introduced a bug with the 4.2 optimizations to session handling. It can cause this problem with certain custom sentry functions. I filed #14784118 for this issue and we'll get a PSE out.

    The root cause for this problem is that a function which returns session data now caches the information, to avoid querying for each access. However, it does not create a new session for the ID your code created with wwv_flow_custom_auth.get_next_session_id, if it already has the builder session in it's cache. When your sentry tries to save the deep link in session state, this fails because the session record itself does not yet exist.

    I'll update the thread when the PSE is ready.

    Regards,
    Christian
  • 2. Re: Apex 4.2 - Page sentry error
    VANJ Journeyer
    Currently Being Moderated
    Thanks. A few thoughts come to mind

    1. I participated in the early adopter/beta program and didn't encounter this bug. It must have been introduced just before release.
    2. It is a little troubling to see similar bugs in the same area for the third time in as many releases. You may recall our Apex 4.1 - Websheets with custom authentication scheme Re: 4.1 POST_LOGIN question on this topic. How do these bugs slip past a public beta and Oracle's own internal QA process?
    3. If I understand you correctly, this bug only shows up when running the app when logged into the App Builder. So it is safe to deploy 4.2 in a Production environment without running into this bug?
    4. As I described earlier, even in a Builder session, when I initially login and run my app I get the error. Now when I logout from Builder and log back in and run the app, I DO NOT get the error. Why is this?

    Thanks
  • 3. Re: Apex 4.2 - Page sentry error
    Christian Neumueller Expert
    Currently Being Moderated
    Hi Vikas!

    (1) The bug was introduced on 2012-05-10.

    (2) Sorry again. I can not describe our internal processes, but we (the development team) are actively working on additional test automation that should catch these kinds of issues in the future.

    (3) The bug only shows up if Apex can find a builder session for the same workspace. On a runtime (i.e. production) env, there can never be a builder session.

    (4) I can not reproduce that. If I'm logged into the builder for the same workspace, the app sentry throws this error. It does not matter if I logout and login again.

    Regards,
    Christian
  • 4. Re: Apex 4.2 - Page sentry error
    VANJ Journeyer
    Currently Being Moderated
    Regarding (4), here is what I observed.

    0. Firefox - Use Tools/Options/Privacy/Cookies to clear all apex.oracle.com cookies
    1. Tab 1 - Login to apex.oracle.com. Run app 24317 via the Builder. Get the error.
    2. Tab 1- Logout of the builder.
    3. Tab 2 - In a new tab, run the app directly by typing in http://apex.oracle.com/pls/apex/f?p=24317 , no error
    4. Tab 1 - Repeat step 1. This time there is no error!

    Note that the session id in the browser location bar in step 3 (no dev toolbar at the bottom of the page since no builder session was active at that point in time) and step 4 is different. But clicking around the app in the tab in step 3 now does show the dev toolbar at the bottom indicating that it has "joined" the now active builder session.

    Does that help? Are they all symptoms of the same underlying issue?
  • 5. Re: Apex 4.2 - Page sentry error
    kempiak Newbie
    Currently Being Moderated
    Hello Christian,

    I know that the question is marked as answered but I wanted to let you know that we have similar problem and in my opinion the problem does not correspond to the builder session and my happen on production env.

    Recently we have upgraded to 4.2. Our sentry function looks very similar to this one from Joel R. Kallman blog: [http://joelkallman.blogspot.fr/2010/10/custom-authentication-scheme-for-oracle.html]
    It is a http header variable authentication. Unfortunately because of user requirements we could not use the one which is coming with APEX. Our URL which is used to access APEX application is protected and can be only accessed by the users which have authenticated in our SSO. In this case http header variable will be injected to the HTTP request.

    Get to the point. When user first access application and authenticate problem will appear - ORA-02291: integrity constraint (APEX_040200.WWV_FLOW_DATA_SESSION_FK) violated - parent key not found. If you delete session id from the URL it will start to work.

    Could you please help us? Thank you in advance.

    Maciej
  • 6. Re: Apex 4.2 - Page sentry error
    Christian Neumueller Expert
    Currently Being Moderated
    Hello Maciej,

    I tested Joel's sentry function and it showed the same behaviour as Vikas' function. However, you are correct that the error can also occur when the URL contains a session id that already exists, thanks for pointing that out. It turns out that the session gets loaded into the cache, but it can not be used by the request, since the session cookie is null after a browser restart. My initial assumption that this can only occur when a builder session exists was wrong, but the underlying issue with the caching mechanism is the same. The fix I implemented solves both problems. It's currently under review but we'll have a patch available soon.

    Regards,
    Christian
  • 7. Re: Apex 4.2 - Page sentry error
    VANJ Journeyer
    Currently Being Moderated
    Christian - Did you review my last post to the thread? I am not sure I understand if this is a 3rd symptom of the same bug or not. The 2 symptoms you have identified so far are 1) app session run via Builder and 2) URL has session id but no session cookie. The step-by-step I provided shows how logging out and running the app without session id in another tab and then logging in to the Builder results in no error. What explains this?

    Also, users frequently email links containing session ids around. For example, user A copies the link he sees in the browser f?p=123:45:12345 and sends it to user B. User B clicks on the link and the since User B's browser doesn't have a session cookie for Session 12345, the page sentry transparently creates a new session, sets the cookie for it, and redirects to it. But because of this bug (Maciej's symptom), User B would get the error, right?

    So, are you still confident about your earlier recommendation that we will not encounter this bug in a Production environment in the absence of a builder session? Or should we wait for the patch you are working on before upgrading Production to 4.2? Please advise.

    Thanks
  • 8. Re: Apex 4.2 - Page sentry error
    Christian Neumueller Expert
    Currently Being Moderated
    Hi Vikas,

    I did and I can explain this behaviour. The difference is that in your step 4, a valid cookie for your app session exists. Before Apex executes the application's sentry, it already tries to set up the session, based on the session id in the URL. Now that is the builder session if you run from the builder, but there is a special optimization in that setup code that switches g_instance (i.e. the session id during the request) to the application session. That code requires that the URL's session id is for the builder, a valid builder cookie exists and the app session cookie references a valid application session. If these conditions are met, Apex treats the request as if it was executed with the application session id which matches the app session cookie. If we didn't have this code, each switch between builder and runtime would invalidate the application session and you would have to login again to the app.

    Maciej's finding shows that the error can also occur in a production environment, in the absence of a builder session. My fix should be downloadable from Oracle Support soon, I guess some time this week, but that's outside of my control. Based on the scenario you described, with users sending links around, it might make sense to wait. For others it might be ok to upgrade right now.

    Regards,
    Christian

    Edited by: Christian Neumueller on Oct 23, 2012 7:12 AM
  • 9. Re: Apex 4.2 - Page sentry error
    Christian Neumueller Expert
    Currently Being Moderated
    Hi,

    a patch for bug #14784118 is available on support.oracle.com. Just search for the bug number in Patches & Updates, download and follow the README.txt file.

    Regards,
    Christian
  • 10. Re: Apex 4.2 - Page sentry error
    790896 Newbie
    Currently Being Moderated
    Christian,
    I just applied your patch, but it is still not working. This time the URL gets refreshed with new sessionIDs and I can see the numbers changing in the URL(and it tries to reload the page like several times in a minute) and it does not render the page. We are not getting the FK violation error though.

    Thanks
    Venkat
  • 11. Re: Apex 4.2 - Page sentry error
    Christian Neumueller Expert
    Currently Being Moderated
    Hi Venkat,

    that's probably a different issue. Can you post your sentry function or install it on an apex.oracle.com workspace?

    Regards,
    Christian
  • 12. Re: Apex 4.2 - Page sentry error
    790896 Newbie
    Currently Being Moderated
    Thanks Christian for helping me out. This is when I run on IE
    CREATE OR REPLACE FUNCTION LMC_WS.siteminder_page_sentry
       RETURN BOOLEAN
    IS
       l_username     VARCHAR2 (512);
       l_session_id   NUMBER;
       l_user         VARCHAR2 (128);
       l_auth         VARCHAR2 (512);
       l_htp_buffer   HTP.htbuf_arr;
       l_htp_rows     INTEGER;
       l_url          VARCHAR2 (500);
    BEGIN
    
      /*
       * IMPORTANT NOTE: In order for this APEX page sentry function to work the
       *                 following must be completed:
       *
       * 1. The text 'PlsqlCGIEnvironmentList SM_USER' must be added to the dads.conf
       *    entry where APEX is defined.
       * 2. Netegrity siteminder agent must be configured for the application server
       *    hosting APEX and the url pattern you want protected.
       */
    
    --   write_log ('1. test for apex_public_user');
    
       -- check to ensure that we are running as the correct database user.
       IF USER != 'APEX_PUBLIC_USER'
       THEN
          RETURN FALSE;
       END IF;
    
       -- get sessionid.
       l_session_id := wwv_flow_custom_auth_std.get_session_id_from_cookie;
    
       -- check application session cookie.
       IF wwv_flow_custom_auth_std.is_session_valid
       THEN
          apex_application.g_instance := l_session_id;
          l_username := wwv_flow_custom_auth_std.get_username;
          wwv_flow_custom_auth.define_user_session (p_user            => l_username,
                                                    p_session_id      => l_session_id
                                                   );
          --write_log ('2. session valid returned true ' || l_username);
          RETURN TRUE;
       ELSE
          --write_log ('3. New session asking siteminder who you are.');
          -- get username using Siteminder (SM_USER)
          l_auth := OWA_UTIL.get_cgi_env ('SM_USER');
          --write_log ('4. sm_user = ' || l_auth);
    
          IF l_auth IS NULL
          THEN
             OWA_UTIL.status_line (nstatus            => 401,
                                   creason            => 'Unauthorized',
                                   bclose_header      => FALSE
                                  );
             HTP.p ('WWW-Authenticate: Siteminder');
             OWA_UTIL.mime_header ('text/html', FALSE, 'utf-8');
             OWA_UTIL.http_header_close;
             wwv_flow.g_unrecoverable_error := TRUE;
             RETURN FALSE;
          END IF;
    
          l_username := LOWER (l_auth);
    
          -- enforcing the following through authorization
          /*--test if user is a dba
          IF NOT is_dba (l_username)
          THEN
             RETURN FALSE;
          END IF;*/
          -- --write_log ('5. Made it this far, I am a dba, next create my session');
          -- application session cookie not valid --> define a new apex session.
          wwv_flow_custom_auth.define_user_session
                                     (p_user            => l_username,
                                      p_session_id      => wwv_flow_custom_auth.get_next_session_id
                                     );
          -- tell apex engine to quit.
          apex_application.g_unrecoverable_error := TRUE;
    
          IF OWA_UTIL.get_cgi_env ('REQUEST_METHOD') = 'GET'
          THEN
             /* write_log (   '6. request method = get, forwarding to '
                        || OWA_UTIL.get_cgi_env ('QUERY_STRING')
                       ); */
             wwv_flow_custom_auth.remember_deep_link
                (p_url      =>    'f?'
                               || wwv_flow_utilities.url_decode2
                                                         (OWA_UTIL.get_cgi_env ('QUERY_STRING')
                                                         )
                );
          ELSE
             /*write_log (   '7. request method = post, forwarding to 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_custom_auth.remember_deep_link
                                    (p_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)
                                    );
          END IF;
    
          -- register the session in apex sessions table, set cookie, redirect back.
          wwv_flow_custom_auth_std.post_login
                                        (p_uname              => l_username,
                                         p_session_id         => nv ('APP_SESSION'),
                                         p_flow_page          =>    apex_application.g_flow_id
                                                                 || ':'
                                                                 || NVL
                                                                       (apex_application.g_flow_step_id,
                                                                        0
                                                                       ),
                                         p_preserve_case      => TRUE
                                        );
          /* write_log (   '8. redirecting to: '
                     || apex_application.g_flow_id
                     || ':'
                     || NVL (apex_application.g_flow_step_id, 0)
                    ); */
          -- get HTP output wwv_flow_custom_auth_std.post_login has written,
          -- it contains the session cookie we need.
          -- Thanks to Patrick Wolf for the following code
          l_htp_rows := 15;           /* where and how to get an actual value for irows???? */
          HTP.get_page (thepage => l_htp_buffer, irows => l_htp_rows);
          -- reset the HTP buffer so that we can write our own header, ...
          HTP.init;
          -- See http://www.nabble.com/Empty-POST-requests-on-IE-td15332680.html
          -- We have to trick IE that he thinks the authentication fails, otherwise
          -- he doesn't send any data when issueing a POST because he wants to
          -- do the NTLM stuff again
          OWA_UTIL.status_line (nstatus            => 401,
                                creason            => 'Unauthorized',
                                bclose_header      => FALSE
                               );
    
          -- write the session cookie into our output
          FOR ii IN 1 .. l_htp_rows
          LOOP
             IF l_htp_buffer (ii) LIKE 'Set-Cookie:%'
             THEN
                HTP.p (RTRIM (l_htp_buffer (ii), CHR (10)));
                --write_log ('9. ' || RTRIM (l_htp_buffer (ii), CHR (10)));
             END IF;
          END LOOP;
    
          --
          l_url :=
                'f?p='
             || apex_application.g_flow_id
             || ':'
             || NVL (apex_application.g_flow_step_id, 0)
             || ':'
             || apex_application.g_instance;
          --write_log ('10. ' || l_url);
    
          --
          IF wwv_flow.get_browser_version = 'NSCP'
          THEN
          --  --write_log ('11. Browser is Firefox');
             -- Firefox: redirect can be set with a HTTP header attribute
             HTP.p ('Location: ' || l_url);
             OWA_UTIL.http_header_close;
          ELSE
           --  --write_log ('12. Browser is Internet Explorer');
             -- For IE: The javascript is required so that we are redirected to the page as
             -- the wwv_flow_custom_auth_std.post_login would normally do with the
             -- HTTP 302 redirect
             OWA_UTIL.http_header_close;
             HTP.p ('<html><head>');
             HTP.p ('<script type="text/javascript">');
             HTP.p ('  location.href="' || l_url || '";');
             HTP.p ('</script>');
             HTP.p ('<noscript>');
             HTP.p ('<meta http-equiv="Refresh" content="0; URL="' || l_url || '">');
             HTP.p ('</noscript>');
             HTP.p ('</head>');
             HTP.p ('<body>');
             HTP.p (   'You were logged in successfully. Click <a href="'
                    || l_url
                    || '">here</a> to continue.'
                   );
             HTP.p ('</body>');
             HTP.p ('</html>');
          END IF;
    
          RETURN FALSE;
       END IF;
    END siteminder_page_sentry;
    /
    {code}
    
    I get the following error using Firefox:
    
    The page isn't redirecting properly
             
              Firefox has detected that the server is redirecting the request for this address in a way that will never complete.
            
      This problem can sometimes be caused by disabling or refusing to accept
        cookies.
    
    Edited by: venkat on Oct 24, 2012 8:05 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 13. Re: Apex 4.2 - Page sentry error
    Patrick Wolf Employee ACE
    Currently Being Moderated
    Hi Venkat,

    as a workaround you might want to have a look at our "HTTP Header Variable" authentication scheme which we introduced in APEX 4.1. I think there is no need anymore to use a custom authentication to authenticate against Siteminder.

    Here is what you have to do:

    1) Create a new authentication scheme using the type "HTTP Header Variable"
    2) Set "HTTP Header Variable Name" to SM_USER
    3) Set "Action if Username is Empty" to Redirect to built-in URL (see online help for other options if you want to use your own URL)
    4) Configure Siteminder to protect the URL */apex/apex_authentication.callback* to enforce an authentication of your application with Siteminder.
    5) Check if Siteminder always sets the SM_USER environment variable for following requests, if not set "Verify Username" to "After Login".

    Hope that gets you going.

    Regards
    Patrick
    -----------
    My Blog: http://www.inside-oracle-apex.com
    APEX Plug-Ins: http://apex.oracle.com/plugins
    Twitter: http://www.twitter.com/patrickwolf
  • 14. Re: Apex 4.2 - Page sentry error
    790896 Newbie
    Currently Being Moderated
    Thanks Patrick, that is what I am doing in development environment now. But the problem is, we have lots of application using this custom SSO, and when I upgrade the apex instance in production all the applications will stop working, and it is very difficult to coordinate all the stake holders to roll out their app as soon as the upgrade is done.

    Venkat
1 2 Previous Next

Legend

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