登录审计trigger

登录审计trigger,记录所有用户登录时的主机,用户名等,可以用作审计:

CREATE TABLE perfstat.TA$LOGIN_LOG
(
  Login_User       VARCHAR2(30 BYTE),
  INSTANCE_NUMBER  NUMBER,
  AUDSID           NUMBER,
  IPADDRESS        VARCHAR2(20 BYTE),
  HOST             VARCHAR2(60 BYTE),
  TERMINAL         VARCHAR2(30 BYTE),
  OSUSER           VARCHAR2(30 BYTE),
  login_TIME         DATE,
  PROGRAM          VARCHAR2(48 BYTE)
);

DROP TRIGGER   logon_audit_trigger;

CREATE OR REPLACE TRIGGER logon_audit_trigger
AFTER LOGON ON DATABASE
DECLARE
  V_PROGRAM VARCHAR2 (48 BYTE);
BEGIN
SELECT PROGRAM INTO V_PROGRAM
FROM V$SESSION
WHERE AUDSID = SYS_CONTEXT('USERENV', 'SESSIONID')
AND ROWNUM<2;
INSERT INTO perfstat.TA$LOGIN_LOG
    (Login_User,
     instance_number,
     audsid,
     ipaddress,
     host,
     terminal,
     osuser,
       login_TIME,
  program)
  VALUES
    (ora_login_user,
     USERENV('instance'),
     USERENV('SESSIONID'),
     SYS_CONTEXT('userenv', 'ip_address'),
     SYS_CONTEXT('userenv', 'host'),
     SYS_CONTEXT('userenv', 'terminal'),
     SYS_CONTEXT('userenv', 'os_user'),
     SYSDATE,
   V_PROGRAM
  );
END;
/

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10834762/viewspace-515975/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10834762/viewspace-515975/