Create an AFTER LOGON trigger to log sessions

Create an AFTER LOGON trigger to log sessions

CREATE TABLE LOGON_SESSIONS_HIST (

  schema_name VARCHAR2(30),

  os_user VARCHAR2(30),

  machine VARCHAR2(100),

  program VARCHAR2(100),

  client_ip VARCHAR2(15),

  logon_time DATE) 

TABLESPACE IGT_TABLE;

CREATE OR REPLACE TRIGGER LOGON_LOGGER_TRG

AFTER LOGON ON VRS_GROUP_SHARE.SCHEMA

BEGIN

  INSERT INTO LOGON_SESSIONS_HIST(schema_name, os_user, machine, program, client_ip, logon_time)

  SELECT sys_context ('USERENV', 'SESSION_USER') as schema_name, 

         sys_context ('USERENV', 'OS_USER') as os_user,

         sys_context ('USERENV', 'HOST') as machine, 

         sys_context ('USERENV', 'MODULE') as program,

         sys_context ('USERENV', 'IP_ADDRESS') as client_id,

         SYSDATE  as login_time   

         FROM DUAL; 

  COMMIT;

EXCEPTION

  WHEN OTHERS THEN

    NULL;

END;

/

SELECT * FROM LOGON_SESSIONS_HIST ;

 

SCHEMA_NAME     OS_USER  MACHINE      PROGRAM        CLIENT_IP      LOGON_TIME

--------------- -------- ------------ -------------- -------------- -----------------

VRS_GROUP_SHARE oracle   qadev-aps-01                               20210804 12:45:24

VRS_GROUP_SHARE os_user  DOMAIN\USER  plsqldev.exe   10.135.251.200 20210804 12:45:32

VRS_GROUP_SHARE oracle   qadev-aps-01                               20210804 12:46:29

VRS_GROUP_SHARE oracle   qadev-aps-01                               20210804 12:47:04

VRS_GROUP_SHARE oracle   qadev-aps-01                               20210804 12:48:09

VRS_GROUP_SHARE os_user  DOMAIN\USER  plsqldev.exe   10.135.251.200 20210804 12:48:28

VRS_GROUP_SHARE oracle   qadev-aps-01                               20210804 12:49:14

VRS_GROUP_SHARE os_user  DOMAIN\USER  plsqldev.exe   10.135.251.200 20210804 12:45:32

ALTER TRIGGER LOGON_LOGGER_TRG DISABLE;