Oracle 10g user login Audit

you can audit user login or logoff request with triggers as well.


following needed

1. Table that will hold trigger output
2. Login trigger (in sys schema)
3. Logoff trigger (in sys schema)

 --------------------------------------------------------
--  DDL for Trigger output table
--------------------------------------------------------

CREATE TABLE "Schema"."TBLUSERCONNECTIONAUDIT"
   (    "DATE_CON" DATE,
    "OSUSER" VARCHAR2(100 BYTE),
    "PROGRAM" VARCHAR2(100 BYTE),
    "MACHINE" VARCHAR2(100 BYTE),
    "IP" VARCHAR2(100 BYTE),
    "ACTION" VARCHAR2(30 BYTE)
   )

--------------------------------------------------------
--  DDL for Trigger TRG_LOGON_AUDIT
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "SYS"."TRG_LOGON_AUDIT"
AFTER LOGON ON DATABASE
DECLARE
v_prog SYS.v_$session.program%TYPE;
v_owner SYS.v_$session.username%TYPE;
v_osuser SYS.v_$session.osuser%TYPE;
v_machine SYS.v_$session.machine%TYPE;
v_ip VARCHAR2 (20);
BEGIN
-- Change user name as required
IF sys_context('USERENV','BG_JOB_ID') is null and user = 'USERNAME' THEN
SELECT program, username, osuser, machine,
SYS_CONTEXT ('USERENV', 'IP_ADDRESS')
INTO v_prog, v_owner, v_osuser, v_machine,
v_ip
FROM SYS.v_$session
WHERE audsid = USERENV ('SESSIONID')
AND audsid != 0
AND ROWNUM = 1;

IF UPPER (v_prog) NOT LIKE '%PL%SQL%'
THEN
insert into sanmar.tblUserConnectionAudit
(DATE_CON,
osuser, program, machine, ip,action
)
VALUES (SYSDATE,
v_osuser, v_prog, v_machine, v_ip,'LOGON'
);
ELSE
NULL; -- Logging the error
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
--* Logging the error

RAISE;
END;
/
ALTER TRIGGER "SYS"."TRG_LOGON_AUDIT" ENABLE;



--------------------------------------------------------
--  DDL for Trigger TRG_LOGOFF_AUDIT
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "SYS"."TRG_LOGOFF_AUDIT"
Before LOGOFF ON DATABASE
DECLARE
v_prog SYS.v_$session.program%TYPE;
v_owner SYS.v_$session.username%TYPE;
v_osuser SYS.v_$session.osuser%TYPE;
v_machine SYS.v_$session.machine%TYPE;
v_ip VARCHAR2 (20);
BEGIN
-- Change user name as required
IF sys_context('USERENV','BG_JOB_ID') is null and user = 'USERNAME' THEN
SELECT program, username, osuser, machine,
SYS_CONTEXT ('USERENV', 'IP_ADDRESS')
INTO v_prog, v_owner, v_osuser, v_machine,
v_ip
FROM SYS.v_$session
WHERE audsid = USERENV ('SESSIONID')
AND audsid != 0
AND ROWNUM = 1;

IF UPPER (v_prog) NOT LIKE '%PL%SQL%'
THEN
insert into sanmar.tblUserConnectionAudit
(DATE_CON,
osuser, program, machine, ip,action
)
VALUES (SYSDATE,
v_osuser, v_prog, v_machine, v_ip,'LOGOFF'
);
ELSE
NULL; -- Logging the error
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
--* Logging the error

RAISE;
END;
/
ALTER TRIGGER "SYS"."TRG_LOGOFF_AUDIT" ENABLE;