oracle数据库登录、DDL触发器的应用

from http://blog.sina.com.cn/s/blog_69c01c32010175kq.html

登录触发器
oracle登录记录触发器:
1.创建日志记录表：
CREATE TABLE SYSTEM.LOGIN_LOG
(
    SESSION_ID                     NUMBER,
    LOGIN_ON_TIME                  DATE,
    LOGIN_OFF_TIME                 DATE,
    USER_IN_DB                     VARCHAR2(30),
    PROCESS      VARCHAR2(12),
    TYPE      VARCHAR2(10),
    MACHINE                        VARCHAR2(64),
    OSUSER      VARCHAR2(30),
    IP_ADDRESS                     VARCHAR2(20),
    RUN_PROGRAM                    VARCHAR2(48)
) tablespace TS_AUDIT_MON;
create index SYSTEM.IDX_LOGIN_LOG1 on SYSTEM.LOGIN_LOG (LOGIN_ON_TIME desc,SESSION_ID) tablespace TS_AUDIT_MON;
create index SYSTEM.IDX_LOGIN_LOG2 on SYSTEM.LOGIN_LOG (USER_IN_DB,SESSION_ID) tablespace TS_AUDIT_MON;


2.创建登录触发器：
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,
     process,
     TYPE,
     machine,
     osuser,
     ip_address,
     run_program)
    SELECT audsid,
           SYSDATE,
           NULL,
           sys.login_user,
           process,
           TYPE,
           machine,
           osuser,
           sys_context('USERENV', 'IP_ADDRESS'),
           program
      FROM v$session
     WHERE audsid = userenv('SESSIONID');
END;
 
3.创建登出触发器：
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;


4.日期筛选语句：
查询所有记录
set linesize 300
col USER_IN_DB for a10
col MACHINE for a10
col osuser for a10
col RUN_PROGRAM for a40
SELECT session_id,
       to_char(login_on_time, 'YYYYMMDD HH24:MI:SS'),
       to_char(login_off_time, 'YYYYMMDD HH24:MI:SS'),
       user_in_db,
       process,
       TYPE,
       machine,
       osuser,
       ip_address,
       run_program
  FROM system.login_log;

DDL触发器
1. 创建ddl记录表
create table system.ddl_log
(
 ddl_date date,
 session_id number,
 os_user VARCHAR2(30),
 machine  VARCHAR2(64),
 user_name varchar2(30),
 ip_addr VARCHAR2(20),
 obj_name VARCHAR2(128),
 ddl_type      VARCHAR2(30),
 object_type   VARCHAR2(19),
 owner         VARCHAR2(30),
 SQL_TEXT      VARCHAR2(1000)
) tablespace TS_AUDIT_MON;
create index system.idx_ddl_log1 on system.ddl_log (ddl_date desc,session_id) tablespace TS_AUDIT_MON;
create index system.idx_ddl_log2 on system.ddl_log (user_name,session_id) tablespace TS_AUDIT_MON;


2.创建数据库级DDL触发器，记录所有ddl操作
CREATE OR REPLACE TRIGGER sys.ddl_audit
  AFTER CREATE OR ALTER OR drop OR truncate OR grant OR revoke OR rename ON DATABASE
DECLARE
  session_id_var NUMBER;
  osuser_var     VARCHAR2(30);
  machine_var    VARCHAR2(64);
  ipaddr_var     VARCHAR2(20);
  stext          VARCHAR2(1000);
BEGIN
  BEGIN
    SELECT sys_context('USERENV', 'SESSIONID'),
           sys_context('USERENV', 'OS_USER'),
           sys_context('USERENV', 'HOST'),
           sys_context('USERENV', 'IP_ADDRESS')
      INTO session_id_var, osuser_var, machine_var, ipaddr_var
      FROM dual;
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
  BEGIN
    SELECT sql_text
      INTO stext
      FROM v$open_cursor
     WHERE upper(sql_text) LIKE 'ALTER%'
        OR upper(sql_text) LIKE 'TRUNCATE%'
        OR upper(sql_text) LIKE 'DROP%'
        OR upper(sql_text) LIKE 'CREATE%'
        OR upper(sql_text) LIKE 'GRANT%'
        OR upper(sql_text) LIKE 'REVOKE%'
        OR upper(sql_text) LIKE 'RENAME%';
  EXCEPTION
    WHEN OTHERS THEN
      stext := '-';
  END;
  INSERT INTO system.ddl_log
    (ddl_date,
     session_id,
     os_user,
     machine,
     user_name,
     ip_addr,
     obj_name,
     ddl_type,
     object_type,
     owner,
     sql_text)
  VALUES
    (SYSDATE,
     session_id_var,
     osuser_var,
     machine_var,
     USER,
     ipaddr_var,
     nvl(ora_dict_obj_name, '-'),
     nvl(ora_sysevent, '-'),
     nvl(ora_dict_obj_type, '-'),
     nvl(ora_dict_obj_owner, '-'),
     stext);
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/


3.查询语句
查询所有记录
set linesize 300
col MACHINE for a10
col os_user for a10
col USER_NAME for a10
col ip_addr for a16
col obj_name for a15
col DDL_TYPE for a10
col object_type for a10
col owner for a10
col sql_text for a30
SELECT to_char(ddl_date, 'YYYYMMDD HH24:MI:SS'),
       session_id,
       os_user,
       machine,
       user_name,
       ip_addr,
       obj_name,
       ddl_type,
       object_type,
       owner,
       sql_text
  FROM system.ddl_log;