13 Replies Latest reply: Feb 28, 2013 5:24 AM by zagirov RSS

    APEX 4.1 Login page kills existing session cookie

    Capt. Egg
      Up until now the session cookie allowed us to figure out if the user already had a session and redirect them to the page they wanted using that session (via a page process on the login page). Just testing APEX 4.1 and it seems that this quick hack is not going to work any more because:
      a. The session cookie has been replaced by the time the login page loads
      b. The login page gives no clues as to the destination the user actually wanted (used to be able to check OWA_UTIL.GET_CGI_ENV('QUERY_STRING') to see what the URL looked like, but now the URL is just the login page)

      The problem I'm trying to solve is for a job logging system where users get lots of notifications via email. With the standard behaviour of APEX, they would have to login every time they click a link in an email. Given that this is pretty non-standard behaviour for any other web application I've used, has any one got any ideas for how to get APEX to stop killing sessions by overriding the cookie everytime it sees a URL it doesn't like? Have tried using session 0, but still it clobbers my cookie.
        • 1. Re: APEX 4.1 Login page kills existing session cookie
          Christian Neumueller-Oracle
          Hi Capt. Egg,

          in 4.1, we made a lot of changes to the authentication and session handling subsystem, to support plugin-based authentications and improve security.

          What authentication scheme are you using? I think for this type of scenario, some single sign-on would be ideal, or you could implement a custom sentry function / plugin. Can you post your authentication code or create an example on apex.oracle.com so we can inspect the details?

          Regards,
          Christian
          • 2. Re: APEX 4.1 Login page kills existing session cookie
            Capt. Egg
            I got a solution together and I must say it's far less hacky now. I noticed the Custom Auth scheme in apex 4.1 has a new Invalid Session Procedure text field, so I added this function to the PL/SQL block and entered "try_cookie_session" into the text box then the clouds parted and the heavens opened up...
            procedure try_cookie_session is
              l_cookie_session_id number;
              l_cookie_session_check number;
              l_query_string  varchar2(32767) := null;
              l_app           varchar2(30)    := null;
              l_page          varchar2(30)    := null;
              l_session       varchar2(256)   := null;
              l_tail          varchar2(32767) := null;
            begin
            -- Designed to be called only from APEX4.1 "Custom Authentication Scheme" as an
            -- "Invalid Session Procedure". If page id omitted, apex always redirects to the home
            -- page before this procedure is triggered (so we can assume page id always exists).
              
            -- Undocumented functions gleaned from Scott Spadafore's post here...
            -- Application Link
            -- See also: http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/concept_url.htm#HTMDB03017
            
              l_cookie_session_id := APEX_CUSTOM_AUTH.GET_SESSION_ID_FROM_COOKIE;
              
              if l_cookie_session_id != :SESSION then
                
                select COUNT(*) into l_cookie_session_check from APEX_WORKSPACE_SESSIONS
                where apex_session_id = l_cookie_session_id;
                
                if l_cookie_session_check > 0 then
                  -- The session in the cookie is a valid session now need to inject it into the query string
                  
                  -- Undocumented functions that apparently break apart the query string...
                  l_query_string := WWV_FLOW_UTILITIES.URL_DECODE2(OWA_UTIL.GET_CGI_ENV('QUERY_STRING'));
                  WWV_FLOW_UTILITIES.PARSE_QUERY_STRING(l_query_string,l_app,l_page,l_session,l_tail); 
                  
                  --Redirect to the old session...
                  htp.init;
                  owa_util.redirect_url('f?p='||:APP_ID||':'||l_page||':'||l_cookie_session_id||':'||l_tail);
                  apex_application.g_unrecoverable_error := true;
                end if;
                
              end if;
            
            end try_cookie_session;
            • 3. Re: APEX 4.1 Login page kills existing session cookie
              Christian Neumueller-Oracle
              Hi Capt. Egg,

              nice to see that you investigated and found a solution. However, I think things can be improved a bit. It seems you already employed a custom authentication scheme, that's good, because it allowed you to use hooks for implementing session joining. However, I think this does not exactly do what you expect.

              During session setup, at the beginning of Apex' request processing, it approximately runs this code:

              1. Write session id from the request (URL or POST parameter) into a global variable
              2. Load application authentication metadata (cookie name, sentry function, invalid session function, etc.)

              3. If a session global exists, run "Builtin Cookie Sentry":
              3.1. Query the session table by the cookie value.
              3.2. If the query's session id equals the global session id, the session information matches.
              3.3. Otherwise, the session information is incomplete, reset session variable.

              4. If this is not the login page, run application-specific sentries:
              4.1. Run session sentry function's result if a function is defined
              4.2. If the sentry function returned true, run the validation function if it is defined

              5. "Create / Reuse Session"
              5.1. If the session variable points to a valid session record, read user and other variables from the session record
              5.2. Otherwise, create a new, unauthenticated session

              6. Write a new HTTP session cookie if a new session was created

              7. If the sentries (4.) returned false, run "Invalid Session Handling":
              7.1. Save deep link to current page
              7.2. Run invalid session function if a function is defined
              7.3. Redirect to authentication's "Session Not Valid" url

              This is only an overview and implementation details may change. However, I think it can show the main problems with using the invalid session function for session joining:

              - In (4.1), the engine creates a new session record and session id
              - In (6), the engine writes a session cookie
              - The invalid session function changes the global session id variable back to the old session, but there is still a newly created session
              - The invalid session function re-inits the htp buffer and thereby undoes (6) from above. The session joining would create a new session for each request, otherwise.

              My proposal is that you use a session sentry function instead. Here's an example:
              function session_joining_sentry return boolean is
                l_cookie_session_id number;
                l_user              APEX_WORKSPACE_SESSIONS.USER_NAME%TYPE;
                l_result            boolean;
                procedure dbg(p_str in varchar2)
                is
                begin
                  apex_application.debug('session_joining_sentry: '||p_str);
                end dbg;
              begin
                if APEX_CUSTOM_AUTH.GET_SESSION_ID is not null then
                  dbg('apex could already determine session by URL session id and cookie value');
                  l_result := true;
                else
                  dbg('apex could not determine session by URL session id and cookie value');
                  l_cookie_session_id := APEX_CUSTOM_AUTH.GET_SESSION_ID_FROM_COOKIE;
              
                  if l_cookie_session_id is not null then
                    dbg('apex found session via cookie. we try to re-use this as our current session id');
                    begin
                      select user_name
                        into l_user
                        from APEX_WORKSPACE_SESSIONS
                       where apex_session_id = l_cookie_session_id;
                      l_result := true;
                    exception when NO_DATA_FOUND then
                      dbg('session could not be found in session table - sentry fails');
                      l_result := false;
                    end;
              
                    if l_result then
                      dbg('re-using session for user '||l_user);
                      APEX_CUSTOM_AUTH.DEFINE_USER_SESSION (
                        p_user       => l_user,
                        p_session_id => l_cookie_session_id);
                    end if;
                  else
                    dbg('apex could not find the session cookie. sentry fails');
                    l_result := false;
                  end if;
                end if;
                
                return l_result;
              end session_joining_sentry;
              It's necessary to also mention that session joining is insecure. If you plan to implement this, please make sure you understand the dangers of cross-site request forgery:

              http://en.wikipedia.org/wiki/Cross-site_request_forgery

              Regards,
              Christian

              Edited by: Christian Neumueller on Mar 2, 2012 4:24 AM
              • 4. Re: APEX 4.1 Login page kills existing session cookie
                Capt. Egg
                Thanks, that's an excellent summary of the login process. Geez, I'll be the go to guy for authentication schemes back at work now though.

                Also, good point about the cross-site request forgery. We certainly don't want people performing DML operations from any random links they might happen to click on. However, we certainly do want them to maintain their session when simply pulling back a record or a report from a link. Our users expect to be able to freely share links with one another and view the record if they have access to without logging in constantly, or even worse, losing work in another tab. In this case it's a low risk app. I'll certainly rethink my approach to this problem though as the risk is still a very real one.

                Perhaps a procedure that only redirects to a current session based on a whitelist of pages, with no REQUEST parameter allowed. I take it the standard DML processes are triggered by the REQUEST variable alone so unless a developer does something fandangled, we should be safer.
                • 5. Re: APEX 4.1 Login page kills existing session cookie
                  Christian Neumueller-Oracle
                  Thank you.

                  Restricting session joining to requests which have no REQUEST variable set is a great idea, that's much better.

                  Btw, we internally have a couple of enhancement requests, about not always requiring a session id in the URL and session joining. It may be that we implement such a feature in the engine itself some time.

                  Regards,
                  Christian
                  • 6. Re: APEX 4.1 Login page kills existing session cookie
                    Capt. Egg
                    That's good news, the inability to share links seems entirely counter collaborative. I'd love to see something as simple as a checkbox on the custom authentication scheme that allows you to open this (can of worms) up if you so chose. Preferably in the least hacky way possible according to the APEX security pros so I don't have to come up with some of the harebrain ideas as displayed above.

                    Many web apps these days seem to go for the 'admin mode' approach to this problem where you unlock your session further by reentering your password to do anything that could really hurt in the hands of evil doers. Then you get a nice warning that you're privileges are escalated until you downgrade them or it times out. Perhaps a special standard authorisation scheme could serve this purpose? Special standard might be a bit of an oxymoronical way to put it but you know what I mean, inbuilt in APEX for doing this.

                    The problem I have with solving these rather deep issues at a high level is that I don't really understand APEX under the hood. When I do find what seems to be a great solution and implement it across the board for all our apps, there's always the risk it's either going to break with the next upgrade or (ideally) be obsoleted by a new feature (in which case I wasted effort even thinking about the problem, but at least it's solved properly). So on that note and based on the fact that APEX really is evolving in leaps and bounds I'll just leave it to you guys to find the ultimate solution to this chestnut. Thanks again for your fantastic and timely responses, I really do appreciate it.
                    • 7. Re: APEX 4.1 Login page kills existing session cookie
                      Capt. Egg
                      Hi Christian,

                      I'm just attempting to put all these ideas together into an authentication plugin built from scratch. I've installed the plugin so far on this app... http://apex.oracle.com/pls/apex/f?p=64083

                      I think I'm missing something, because I seem to be able to navigate to non-public pages without a session. There is a link to my plugins github page if anyone gets a chance to check it out and can tell me what I've done wrong.

                      Cheers!
                      • 8. Re: APEX 4.1 Login page kills existing session cookie
                        Christian Neumueller-Oracle
                        Hi Capt. Egg,

                        Can you please document the steps where you think that somebody can navigate to non-public pages without a session?

                        A few suggestions and ideas from me in the meantime:

                        - The call to apex_custom_auth.is_session_valid is practically the same as checking if :SESSION (or p_authentication.session_id) is not null. In that case, the sentry can probably immediately return true.
                        - You can also use p_authentication.username instead of :APP_USER if you want
                        - I like the confirmation page, but at the time this is rendered, the session is already joined. We'll have to think about whether this can already be exploited.
                        - Limiting the number of pages where sessions can be joined is a good idea
                        - You should probably also implement your idea about not allowing joining if a REQUEST is set
                        - In dynamic_authentication, I'd change the l_dynsql to the code below, so users of the plugin can re-use the PL/SQL block in the authentication scheme for the authentication function
                          l_dynsql :=  'declare
                                          l_result boolean;'||
                                          p_authentication.plsql_code||'
                                        begin
                                          l_result := '||l_auth_func||'(:1, :2);
                                             :3 := sys.diutil.bool_to_int(l_result);
                                        end;';
                        Regards,
                        Christian

                        PS: You are right about blogging.
                        • 9. Re: APEX 4.1 Login page kills existing session cookie
                          Capt. Egg
                          Just a quick update, I noticed navigating to page 7 for instance with no session after previously logging in allowed me to land on the page as 'nobody' (I'd expected to end up on the login page, as page 7 isn't in my whitelist). I'll put together exact steps to reproduce later.

                          REQUEST is silently cleared in either cases at the moment, though I plan to make that behaviour a little more interactive (perhaps an error page and refusing to even redirect to login - hence maintaining the existing session and avoiding losing any half entered form data).
                          • 10. Re: APEX 4.1 Login page kills existing session cookie
                            Capt. Egg
                            Hi Christian,

                            My problem seemed to be apex_custom_auth.is_session_valid returns true even when user is 'nobody'. So it seems I also have to check that too.

                            On your advice, I've added the suggestion of using p_authentication.plsql_code. It's a cool little addition, but I must admit I'm abit aprehensive about letting code go straight into dynamic sql string. I feel a little safer simply disabling the custom PL/SQL block entirely and just call a stored function (similar to old default APEX auth scheme, where you just have the option of replacing "-BUILTIN-" with "return AUTH_FUNCTION").

                            The plugin is functioning as required now, though as you mentioned it's probably checking the session more than it needs to. If nothing else, it's a fun little side project leading up to our looming APEX 4.1 application upgrades.

                            Cheers
                            • 11. Re: APEX 4.1 Login page kills existing session cookie
                              Christian Neumueller-Oracle
                              Hi Capt. Egg,

                              is_session_valid returns true when the session cookie in the request header matches a record in the sessions table. That should be the case after the 1st page request. This says nothing about whether the user is authenticated or still 'nobody'.

                              It's your plugin, so I'd never want to define how it should do it's job. If you prefer a simple field for the auth function name, that's fine.

                              Btw, maybe you should start blogging. It will be the perfect opportunity for me to write a 2nd posting, where I can refer to your authentication plugin ;-)

                              Cheers,
                              Christian
                              • 12. Re: APEX 4.1 Login page kills existing session cookie
                                Capt. Egg
                                Don't worry, my blog is also sadly in need of attention. It may well be time to blow the dust off now I've actually got something worth sharing.
                                Christian Neumueller wrote:If you prefer a simple field for the auth function name, that's fine.
                                It's just my mother always told me never to inject user entered data straight into dynamic SQL strings. Maybe I'll add a comment in there saying something like "For plugin use only, DO NOT COPY/PASTE - under penalty of sql injection and broken fingers!". I think I was really just fishing for a safe way to do it. Like maybe APEX has some super secret undocumented features for achieving the same thing? APEX must do something similar under the hood, but I guess it always just runs the code as the workspace schema so there's nothing more that can be done here anyway.

                                Cheers!
                                • 13. Re: APEX 4.1 Login page kills existing session cookie
                                  zagirov
                                  The dummy authentication doesn't pass.

                                  function my_authentication (
                                  p_username in varchar2,
                                  p_password in varchar2 )
                                  return boolean
                                  is begin return true; end;

                                  While there is no authentication - in pages from the white list doesn't let.
                                  The plug-in Plasti_auth doesn't work in 4.2?

                                  Loosing APP_SESSION in APEX 4.2