1 2 Previous Next 20 Replies Latest reply on Mar 25, 2013 8:09 PM by VC

    Deny multiple simultaneous authentication for a single user

    Stefano_i0600006
      This question has already been asked long time ago in this thread Deny multiple authentication for a single user but was not given a clear answer.

      I want to deny multiple login for the same user (Username - APP_USER) (from different browsers/machines etc).
      In practice, i would like to allow a user to have only one "active" apex session. All owners of active apex session (to understand the session in Administration>Monitor Activity>Active Sessions) must be distinct.

      If it already exists an active session for the user who wants to log in, I want to show a warning message like: "there is already an application session active for user xxx, going forward will close the current session and open a new session".

      Are there any API or db views to test this condition?

      Thanks,
      Stefano
        • 1. Re: Deny multiple simultaneous authentication for a single user
          VC
          Stefano_i0600006 wrote:
          If it already exists an active session for the user who wants to log in, I want to show a warning message like: "there is already an application session active for user xxx, going forward will close the current session and open a new session".
          How are you planning to close the current session?
          • 2. Re: Deny multiple simultaneous authentication for a single user
            Stefano_i0600006
            I was hoping there was an API to close certain session id.
            The user INTERNAL has a utility in the framework to close active sessions, so the functionality is implemented, but perhaps it is not exposed as API ...

            However, I was most interested to know if there is a way to know if a particular user already has an active autenticated session on a application.
            I want to prevent multiple simultaneous logins by the same user in my application.

            Thank you.
            Stefano
            • 3. Re: Deny multiple simultaneous authentication for a single user
              Stefano_i0600006
              Using some db views I can generate a report with the list of active sessions for my application.
              Something like this:

              SELECT q1.apex_session_id, q1.user_name, q1.session_created, q2.application_id, q2.first_access, q2.last_access, q2.requests
              +FROM APEX_WORKSPACE_SESSIONS q1,+
              +(SELECT apex_session_id, application_id, MAX(view_date) last_access, MIN(view_date) first_access, count(*) requests+
              +FROM APEX_WORKSPACE_ACTIVITY_LOG+
              where application_id = :APP_ID
              GROUP BY apex_session_id, application_id
              +) q2+
              WHERE q1.apex_session_id =q2.apex_session_id
              and q1.user_name != 'nobody';

              I can understand so if a user has already logged on my application.

              The problem is that the only way to close old sessions using the API wwv_flow_cache.purge_sessions, which takes as a parameter session id but only p_purge_sess_older_then_hrs.
              There is no way to kill a session (session id) Apex?
              • 4. Re: Deny multiple simultaneous authentication for a single user
                VC
                Stefano_i0600006 wrote:
                Using some db views I can generate a report with the list of active sessions for my application.
                Something like this:

                SELECT q1.apex_session_id, q1.user_name, q1.session_created, q2.application_id, q2.first_access, q2.last_access, q2.requests
                +FROM APEX_WORKSPACE_SESSIONS q1,+
                +(SELECT apex_session_id, application_id, MAX(view_date) last_access, MIN(view_date) first_access, count(*) requests+
                +FROM APEX_WORKSPACE_ACTIVITY_LOG+
                where application_id = :APP_ID
                GROUP BY apex_session_id, application_id
                +) q2+
                WHERE q1.apex_session_id =q2.apex_session_id
                and q1.user_name != 'nobody';

                I can understand so if a user has already logged on my application.

                The problem is that the only way to close old sessions using the API wwv_flow_cache.purge_sessions, which takes as a parameter session id but only p_purge_sess_older_then_hrs.
                Why do you want to use an internal/undocumented/unsupported API??
                There is no way to kill a session (session id) Apex?
                Look into APEX_AUTHECNTICTATION.LOGOUT
                • 5. Re: Deny multiple simultaneous authentication for a single user
                  Stefano_i0600006
                  Thank you for your answer.
                  But with the function APEX_AUTHENTICATION.LOGOUT can only kill the current session, that is the session where you run the process that calls the function APEX_AUTHENTICATION.LOGOUT.

                  I do not think this function will close other sessions.
                  • 6. Re: Deny multiple simultaneous authentication for a single user
                    Christian Neumueller-Oracle
                    Hi Stefano,

                    I'm curious: what is the motivation for this requirement?

                    Regards,
                    Christian
                    • 7. Re: Deny multiple simultaneous authentication for a single user
                      Stefano_i0600006
                      The application provides users with several features of booking with the FIFO rule (First In First Out). The feature is active at a given time instant.
                      It happens that the same user opened multiple sessions on different browser or computer, to have more chances of success in the booking.
                      This increases considerably the number of simultaneous requests to the server in a limited period of time, decreasing performance.

                      It happened that the same user has opened more than 20 concurrent sessions with his credentials, asking for help from friends or colleagues.
                      With control over multiple sessions, you want to just limit this practice.

                      Stefano

                      P.S: I hope to be clear. Sorry for my English ...
                      • 8. Re: Deny multiple simultaneous authentication for a single user
                        VC
                        Stefano_i0600006 wrote:
                        The application provides users with several features of booking with the FIFO rule (First In First Out). The feature is active at a given time instant.
                        It happens that the same user opened multiple sessions on different browser or computer, to have more chances of success in the booking.
                        This increases considerably the number of simultaneous requests to the server in a limited period of time, decreasing performance.

                        It happened that the same user has opened more than 20 concurrent sessions with his credentials, asking for help from friends or colleagues.
                        With control over multiple sessions, you want to just limit this practice.
                        Then, the restriction should be implemented in your program code to check the logged in user > once the user has booked something from a session(within 20 other sessions) > restrict that user to do further transactions on other sessions (this can be simply a page validation to check the current user against some custom table)
                        • 9. Re: Deny multiple simultaneous authentication for a single user
                          Stefano_i0600006
                          There is already a restriction implemented in booking functionality that prevents the same user to perform the operation several times, but this restriction does not limit the number of concurrent requests at the precise moment in time at which the function is enabled.
                          Button to make a reservation is only active at any given time (hour minute second) and usually users in seconds immediately before, making repeated refresh of the page waiting for the enabling of the button.They made numerous requests to the server, reducing performance.
                          Also, once enabled the button, the process related to submit the page is executed multiple times for each click of the button on the different sessions of the same user. Only one will make the booking, but the others still perform a validation code generating additional traffic and computation.

                          The best way to avoid an overload of requests from the same user is to allow it to work with only one active session at a time.

                          Enable or disable a control over multiple sessions of the same user is a frequent practice on applications that manage multiple concurrent accesses, and there are numerous examples in the literature to implement the case in java or. Net.

                          If you can not do natively in Apex, means that the implement it manually with a post authentication procedure or a sentry function that goes to test the validity of an application session on a support table. This table records all user session_id and it enables only the last authenticated session.

                          Stefano

                          p.s: Sorry again for my english
                          • 10. Re: Deny multiple simultaneous authentication for a single user
                            Christian Neumueller-Oracle
                            Hi Stefano,

                            interesting use case. I also think this is an issue that should better be handled in your business logic. There are other scenarios where malicious users can bring down your server. Here is an example where this single session limitation would not work:

                            1. In browser, navigate to Stefano's app
                            2. Login
                            3. Save session id and cookie value
                            4. Run Perl script with id and cookie value as arguments
                            5. Script polls the server until the button appears, simulates a click and notifies user
                            6. User continues in browser

                            Instead of the script (which requires some programming knowledge), somebody could probably also use a tool like Selenium, which comes with a browser IDE.

                            Regards,
                            Christian
                            1 person found this helpful
                            • 11. Re: Deny multiple simultaneous authentication for a single user
                              Stefano_i0600006
                              Christian, you're right.
                              There are many ways to cause denial of service.
                              What you have well explained still requires a little experience regarding the management of sessions and cookies.
                              However, in my scenario I do not care so much malicious users, but "standard" users. This type of user has no plans to force the system maliciously, but just wants to take advantage of the possibility of multiple concurrent logins for the best chance of booking. This is a normal and accepted use of the application. For this I would at least disable this option. First, to give all users the same opportunity to booking, and also to prevent server overloading.

                              I think that an option to allow or deny multiple logins, might be easily implemented in authentication systems of future Apex's release. It could be a features appreciated.

                              Meanwhile implement it manually a mechanism to test multiple sessions of the same user.

                              Thank you again.


                              PS: my booking system also implements the Google Recaptcha to prevent the requests generated by robots.
                              • 12. Re: Deny multiple simultaneous authentication for a single user
                                VC
                                Stefano,

                                First make sure you use the features like page/region cache to reduce the impact on the performance.

                                Here is my idea on how to address your situation:

                                1. Create a table called APP_USER_SESSIONS with columns USER_NAME, SESSION_ID, ACTIVE_FLAG and who columns if you prefer.

                                2. In your application > post login procedure/after login > insert a record into the above table with values :APP_USER,:APP_SESSION,'Y'

                                3. Shared components > Application process > create > enter name as 'check_session' and process point as 'on load before header'
                                //runs every time the page is refreshed
                                declare
                                 v number;
                                begin
                                 select 1 into v from APP_USER_SESSIONS
                                 where user_name = :APP_USER 
                                and SESSION_ID =:APP_SESSION and ACTIVE_FLAG='Y';
                                exception when no_data_found then
                                  APEX_AUTHECNTICTATION.LOGOUT(:APP_SESSION,:APP_ID);
                                end;
                                4. In your application login page > add a validation to check if any active sessions exist in APP_USER_SESSIONS and prompt them to confirm to proceed, and by proceeding the other sessions will be terminated.
                                <pre>
                                I would recommend you to implement this login page as a two page wizard (login and confirmation page)
                                Login >
                                after login check if there are any other active session (don't go to landing page) >
                                Yes > Show a page with confirm and cancel (confirm will update the custom table as outlined below
                                - where as cancel will logout the current session)
                                No > login as usual
                                </pre>
                                If the user confirm to proceed then simply update the APP_USER_SESSIONS to set the active flag to N for other sessions for that user
                                update APP_USER_SESSIONS set active_flag='N'
                                where user_name = :APP_USER
                                and session_id != :APP_SESSION;
                                When the user refreshes the browser in multiple sessions they will be verified and terminated accordingly!

                                This should address your situation.
                                1 person found this helpful
                                • 13. Re: Deny multiple simultaneous authentication for a single user
                                  VC
                                  Stefano_i0600006 wrote:
                                  Christian, you're right.
                                  There are many ways to cause denial of service.
                                  What you have well explained still requires a little experience regarding the management of sessions and cookies.
                                  However, in my scenario I do not care so much malicious users, but "standard" users. This type of user has no plans to force the system maliciously, but just wants to take advantage of the possibility of multiple concurrent logins for the best chance of booking. This is a normal and accepted use of the application. For this I would at least disable this option. First, to give all users the same opportunity to booking, and also to prevent server overloading.

                                  I think that an option to allow or deny multiple logins, might be easily implemented in authentication systems of future Apex's release. It could be a features appreciated.

                                  Meanwhile implement it manually a mechanism to test multiple sessions of the same user.

                                  Thank you again.


                                  PS: my booking system also implements the Google Recaptcha to prevent the requests generated by robots.
                                  So a custom solution is the right approach, you can extend my above example to add some more tables/columns so that you can easily implement functionality to enable and disable multiple sessions for each user.
                                  • 14. Re: Deny multiple simultaneous authentication for a single user
                                    Jeff E
                                    Another possible solution:

                                    Create an authorization that compares the current session id to the most recent session id for the user. Use the authorization on the pages or the application to limit each user to one session that can be used.

                                    For example, create an authorization:
                                    Name: Most Recent Session
                                    Scheme Type: Exists SQL Query
                                    SQL Query:
                                    select 1
                                      FROM apex_workspace_sessions
                                     WHERE user_name = :APP_USER
                                       AND session_created = (SELECT MAX(session_created) FROM apex_workspace_sessions WHERE user_name = :APP_USER )
                                       AND apex_session_id = :APP_SESSION
                                    Identify error message displayed when scheme violated: This session is no longer active because you have logged into another session after this one.
                                    Validate authorization scheme: Once per page view

                                    Then edit the application definition and set the application's authorization scheme to your "Most Recent Session" authorization.
                                    1 person found this helpful
                                    1 2 Previous Next