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 database or 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
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 have to create this entity as follows (if you don't, jump to step 5)
1. Create a basic entity UserConnection(UserConnectionID (sequence), ConnectedUser, ConnectionDate)
2. Create the application components automatically (no edition needed, browsing actions only)
3. Create a job LOAD_USER_CONNECTIONS
that has a single task for the UserConnection entity
4. Create the PL-SQL function in the Hub schema as follows:
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;
/
5. 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
6. Create a CONNECTION_TRIGGER model variable in the model, that uses the newly created Datasource Variable Provider
Populate it with the following SQL statement
select usp_on_login_trigger(:V_USERNAME) from dual
Attention: The PL-SQL function can be modified to fit your requirements, or to do a simple insert in a non-Semarchy table if need be. Since regular SQL select statements do not modify data, and since scalar PL-SQL functions are not supposed to create new records, the function needs to be tagged with pragma autonomous_transaction, to let oracle know that this function is explicitly allowed to create new records. Also, keep in mind that |