oracle安全审计之登录登出、ddl操作记录触发器

oracle登录记录: 创建记录表： CREATE TABLE SYSTEM.LOGIN_LOG ( SESSION_ID NUMBER(8,0) NOT NULL, LOGIN_ON_TIME DATE, LOGIN_OFF_TIME DATE, USER_IN_DB VARCHAR2(50), MACHINE VARCHAR2(50), IP_ADDRESS VARCHAR2(20), RUN_PROGRAM VARCHAR2(50) ); 记录登录信息： CREATE OR REPLACE TRIGGER SYS.LOGIN_ON_INFO AFTER LOGON ON DATABASE BEGIN INSERT INTO SYSTEM.LOGIN_LOG(session_id,login_on_time,login_off_time,user_in_db,machine,ip_address,run_program) SELECT AUDSID,sysdate,null,sys.login_user,machine,SYS_CONTEXT('USERENV','IP_ADDRESS'),program FROM v$session WHERE AUDSID=USERENV('SESSIONID'); END; 记录登出信息： CREATE OR REPLACE TRIGGER SYS.LOGIN_OFF_INFO BEFORE LOGOFF ON DATABASE Begin update system.login_log set login_off_time=sysdate where session_id=USERENV('SESSIONID'); exception when others then null; END; 日期筛选语句： select * from SYSTEM.LOGIN_LOG where SESSION_ID>0 AND LOGIN_ON_TIME > TO_DATE('2011-8-11 上午 10:22:07','YYYY-MM-DD AM HH:MI:SS') delete from SYSTEM.LOGIN_LOG where LOGIN_ON_TIME < TO_DATE('2011-8-11 上午 10:22:07','YYYY-MM-DD AM HH:MI:SS')