Session Management

to manage user connection from many workstation and mobiles.

---

Preface

MasonSQL framework uses authentication based on Cookie.

If the user opens a connection to a different browser, the system alert the user to close previous connection. It is not possible to use multiple connections!

For many years, this feature was desired, but now that we must also manage the mobile connection, it is a limit. So we must change the session management in the framework. (It's normal that the user can connect from browser and from mobile at the same time.)

Current implementation

Current code uses login to manage a single active connection at the same time.

In the framework the Session is related to the users, so it is not possible to open more connections in different browsers.

The session data is saved in the table `public.anagrafiche`.

Furthermore, the management of the database cache uses the user ID as reference.

The code involved in the management of the session is mainly present in:

• /opt/masonsql/lib/Auth_AC - Cookie session management based on Apache2::AuthCookie authentication module
• /opt/masonsql/htdocs/lib/dbms_library.comp - Recordset database management - mainly in the functions RecordsetCache and getPersistentRecords .

New implementation

• Creating a session table `public.session` to save the session data.
• Disabling multiple connection of the user with the option in the user profile `public.anagrafiche`.
• Allowing the user to view his sessions and to erasing or closing them.
• Changing the recordset database cache (`public.recordset` and `public.recordset_rows` tables, using the user ID) to use the session reference related to the connected client.

Session Management Implementation

• Database schema changes
  • New column public.anagrafiche.concurrent_sessions will be added to set flag to enable / disable multiple sessions at user level.
  • New table public.session will be created to store multiple session information.

    Column	Type	Description	Example
    session_time	timestamp without time zone	Authentication phase time	2016-12-08 14:58:14.417584
    user_ip	character varying(15)	Browser IP address	111.112.113.114
    session_inactive_time	integer	Last access time minutes from 01-01-1970 00:00	24722284
    previus_session_time	timestamp without time zone	previous auth. phase time equal to session_time at first time	2016-12-07 14:53:41.169064
    id	sequence	Primary key	111
    session	character varying(32)	Random string	cd3bb.7561b.ca4a9.62687fe096ff51
    user_agent	character varying	User agent	'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:50.0) Gecko/20100101 Firefox/50.0'
    id_anagrafiche	integer	User identification with PK anagrafiche.id	22

  • New primary key constraint session_pkey will be added on session.id column.
  • New index session_idx will be created on recordset table for recordset.session column.
  • New on_delete_cascade constraint recordset_session will be added on table recordset.
• SQL update queries

CREATE TABLE public.session (
  id serial,
  id_anagrafiche integer,
  user_agent character varying,
  user_ip character varying(15),
  session character varying(32),
  session_time timestamp without time zone,
  previus_session_time timestamp without time zone,
  session_inactive_time integer,
  CONSTRAINT session_pkey PRIMARY KEY (id),
  CONSTRAINT session_id_anagrafiche FOREIGN KEY (id_anagrafiche)
      REFERENCES public.anagrafiche (id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE CASCADE
);
CREATE INDEX fki_session_id_anagrafiche ON public.session USING btree (id_anagrafiche);
CREATE UNIQUE INDEX session_idx ON public.session USING btree (session);
ALTER TABLE public.recordset RENAME id_anagrafiche TO id_session;
-- Application permissions
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE public.session TO masonsql;
GRANT SELECT, UPDATE ON SEQUENCE public.session_id_seq TO masonsql;

• Processing Detail - Modifications in current system
  • New session table will keep records for single or multiple sessions. Currently used field ( session ) from public.anagrafiche table will not be used in future.
  • User should able to login using multiple browser. Using Apache2::AuthCookie perl package a cookie will be sent to the browser to distinguish it. The cookie will contain the identifier of the current session equal to the field public.session.session
  • New field public.anagrafiche.concurrent_session will be added to manage concurrent sessions for single user. If value set to true then concurrent sessions will be allowed
  • If user has set ( anagrafiche.concurrent_sessions = null ) then only one session whith the same user it is possible in the table public.session (the frontend beavior remain the same of current but using the new table public.session ). This is default behavior of system for each user.
  • If user has set ( anagrafiche.concurrent_sessions = true ) then session will get selected based on ( public.session.session ) criteria only.
  • New field public.recordset.id_session will be added to manage recordset cache based on current session ( public.session.id )
  • When a session is deleted you must also clear the cache.
  • session table entries will be deleted once session gets expired or logout action called by user. Only active sessions will be kept in session table.

• Affected files on the server
  • /opt/masonsql/htdocs/init.comp
  • /opt/masonsql/htdocs/lib/dbms_library.comp
  • /opt/masonsql/htdocs/data/public/anagrafiche.mql
  • /opt/masonsql/htdocs/data/public/session.mql
  • /opt/masonsql/htdocs/data/public/user_session.mql
  • /opt/masonsql/htdocs/AuthCookieLoginForm.html
  • /opt/masonsql/htdocs/logout.html
  • /opt/masonsql/lib/Auth_AC/AuthCookieHandler.pm
  • /opt/masonsql/lib/Auth_AC/AuthBasicHandler.pm

• Testing done
  • Verified that when new user get added, public.anagrafiche.concurrent_sessions column remain null (default value).
  • Verified that anagrafiche GUI is showing sessioni simultanee: checkbox to allow user to enable/disable concurrent sessions.
  • Verified that MasonSQL system allowing multiple sessions when public.anagrafiche.concurrent_sessions set to true.
  • Verified that MasonSQL system does not allow multiple sessions when public.anagrafiche.concurrent_sessions set to null.
  • Verified that when user access any GUI pages, then corresponding cache recordsets get added into recordset table and also get deleted when session get expired.
  • Verified that correct session get expired when it reaches to maximum inactivity time.
  • Verified that system should not check for password expiry when anagrafiche.no_psw_expiration set to true.
  • Verified that system should force to change password when password has expired and anagrafiche.no_psw_expiration set to null.
  • Verified that system should auto delete all user relevant inactive sessions from public.session table when user will logs in.