Auditing Database logins

[sampath]

Sometimes DBA may have to audit the user login details. Here is simple
trigger to monitor user logon details.

1) create a table to store the audit details

create table LOGON_AUDIT_TBL(LOGON_DATE DATE,USER_NAME VARCHAR2
(50),IP_ADDRESS VARCHAR2(25),HOST_NAME VARCHAR2(15),CLIENT_INFO
VARCHAR2(50));


2) Create trigger to insert logon details in the table created in
step-1

CREATE OR REPLACE TRIGGER logon_audit
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO LOGON_AUDIT_TBL
VALUES
(SYSDATE,SYS_CONTEXT('USERENV','SESSION_USER'),sys_context
('USERENV','IP_ADDRESS'),sys_context('USERENV','HOST'),sys_context
('USERENV', 'CLIENT_INFO') );

END logon_audit;