Logging user connection/login to applications

You may want to log user connections to your Semarchy application. The built-in audit trail does not keep track of user connections, but only of user actions (creating / updating records, starting a workflow, ...).

This article explains how to use the Semarchy platform mechanisms to log this information into a table (a regular table or a model entity).

The proposed method relies on model variables to persist this information into the database.

Model variables are typically used to implement security rules or to retrieve information from a database or from a LDAP, when this information does not need to be duplicated within the hub.

They are populated at login time.

Since a model variable can be populated by a select statement, you can use this possibility to execute some code, if the select statement uses a PL-SQL function (or its equivalent in any other RDBMS). This example uses an oracle PL-SQL function.

Step-by-step guide

1. If you want to persist this information into a semarchy entity rather than in a regular table, to allow application administrators to keep track of user connections, you'll have to create this entity as follows (if you don't jump to step 5)
2. Create a basic entity UserConnection(UserConnectionID (sequence), ConnectedUser, ConnectionDate)
3. Create the application components automatically (no edition needed, browsing actions only)
4. Create a job LOAD_USER_CONNECTIONS that has a single task for the UserConnection entity

5. Create the PL-SQL function in the Hub schema as follows 

   PL SQL Function

   create or replace function usp_on_login_trigger(p_user_name varchar2) return varchar2 is
     pragma autonomous_transaction;
   	currentLoadId number; 
   	ret number;
   begin
     if p_user_name <> 'semadmin' then
       currentLoadId:=SEMARCHY_REPO.integration_load.get_new_loadid('YourDataLocationName', 0, 'Log', 'Log', 'semadmin');
       insert into SA_USER_CONNECTION(B_LOADID, USER_CONNECTION_ID, B_CLASSNAME, B_CREDATE, B_CREATOR, CONNECTED_USER, CONNECTION_DATE)
       values(currentLoadId, SEQ_USER_CONNECTION.NEXTVAL, 'UserConnection', sysdate, 'TRIGGER', p_user_name, sysdate);
       commit;
       ret:=SEMARCHY_REPO.integration_load.submit_load(currentLoadId, 'LOAD_USER_CONNECTIONS', 'semadmin') ;
     end if;
     return 'done';
   end;
   /

6. Create a new variable value provider that uses the JDBC connection of the data location you want to monitor. How to configure a Variable Value Provider
7. Create a CONNECTION_TRIGGER model variable in the model, that uses the newly created Datasource Variable Provider

8. Populate it with this sql statement 

   Variable provider select statement

   select usp_on_login_trigger(:V_USERNAME) from dual

Related articles