AFTER LOGON ON DATABASE Trigger by Example

--Create Logon events table

 CREATE TABLE LOGON_LOG(
 os_user      VARCHAR2(30),
 user_name    VARCHAR2(30),
 session_user VARCHAR2(30),
 ip_address   VARCHAR2(15),
 program      VARCHAR2(30),
 machine      VARCHAR2(30),
 logon_time   DATE 
) TABLESPACE IGT_TABLE;

--Create Log  table

CREATE TABLE SGA_W_LOG

(

  procedure_name   VARCHAR2(100) not null,

  data             VARCHAR2(1500) not null,

  ts_last_modified DATE not null

)

TABLESPACE IGT_TABLE

--Create Logon Trigger

CREATE OR REPLACE TRIGGER ON_LOGON_MY_USER AFTER LOGON ON DATABASE

-- As sys, grant

--GRANT SELECT ON SYS.V_$MYSTAT TO MY_USER;

--GRANT SELECT ON SYS.V_$SESSION TO MY_USER;

DECLARE

  v_os_user     VARCHAR2(30);

  v_user_name   VARCHAR2(30);

  v_sess_user   VARCHAR2(15);

  v_sid         NUMBER;

  v_program     VARCHAR2(30);

  v_machine     VARCHAR2(30);

  v_ip          VARCHAR2(15);

  v_msg_text    SGA_W_LOG.data%TYPE;

  v_step        VARCHAR2(30);

BEGIN

  v_step := '1';

  SELECT SYS_CONTEXT('userenv','SESSION_USER') INTO v_sess_user FROM DUAL;  

  --Limit by session user

  IF v_sess_user NOT IN ('MY_USER') THEN 

    RETURN;

  END IF;

  v_step := '2';  

  SELECT DISTINCT sid INTO v_sid FROM SYS.V_$MYSTAT;

  

  v_step := '3';

  SELECT osuser, username, SUBSTR(program,1,30), SUBSTR(machine,1,30)

    INTO v_os_user, v_user_name, v_program, v_machine 

    FROM SYS.V_$SESSION WHERE sid = v_sid;

  --Limit by machine and program, skip Jobs

  IF v_machine = 'my_server' AND v_program LIKE '%J0%' THEN

    RETURN;

  END IF;    

  v_step := '4';  

  SELECT SYS_CONTEXT('userenv','IP_ADDRESS') INTO v_ip FROM DUAL;

  

  v_step := '5';

  INSERT INTO LOGON_LOG (os_user, user_name, session_user, ip_address, program, machine, logon_time)

  VALUES (v_os_user, v_user_name, v_sess_user, v_ip, v_program, v_machine, SYSDATE);

  commit;

EXCEPTION

  WHEN OTHERS THEN

    v_msg_text := 'Unexpected Error in step '||v_step||' : '||SQLERRM;

    INSERT INTO SGA_W_LOG(procedure_name, data, ts_last_modified)  

    VALUES ('ON_LOGON_CGW', v_msg_text, SYSDATE);

    commit;

END ON_LOGON_MY_USER;