创建DDL触发器

转自 ：http://blog.csdn.net/jaune161/article/details/47300697

创建用户并授权

create or replace trigger tr_ddl
  before ddl on database
declare
  l_errmsg varchar2(100) := '请按照公司流程进行申请';
begin
  if (Ora_dict_obj_type = 'TABLE' or Ora_dict_obj_type = 'INDEX' or
     Ora_dict_obj_type = 'PROCEDURE' or Ora_dict_obj_type = 'TRIGGER') AND
     ora_login_user not in ('SYS', 'SYSTEM') and
     (Ora_sysevent = 'ALTER' OR Ora_sysevent = 'DROP' OR
     Ora_sysevent = 'CREATE') then
    raise_application_error(-20001,
                            ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' ||
                            l_errmsg);
  end if;


exception
  when no_data_found then
    null;
end;

#需要使用sys用户授权
CREATE USER DBADMIN IDENTIFIED BY DBADMIN;

GRANT CONNECT TO DBADMIN;
GRANT DBA TO DBADMIN;
GRANT SYS.V_$OPEN_CURSOR TO DBADMIN;

创建序列及表

DROP SEQUENCE SEQ_DDL_VERSION;
CREATE SEQUENCE SEQ_DDL_VERSION INCREMENT BY 1 START WITH 1 NOMAXVALUE NOMINVALUE NOCYCLE NOCACHE;

DROP TABLE TB_SYSTEM_DDL_LOGS CASCADE CONSTRAINTS;

/*==============================================================*/
/* TABLE: TB_SYSTEM_DDL_LOGS                                    */
/*==============================================================*/
CREATE TABLE TB_SYSTEM_DDL_LOGS 
(
   EVENT_ID             VARCHAR2(32)         DEFAULT SYS_GUID() NOT NULL,
   EVENT_NAME           VARCHAR2(20),
   TERMINAL             VARCHAR2(50),
   DB_NAME              VARCHAR2(50),
   OBJECT_NAME          VARCHAR2(30),
   OBJECT_NAME_LIST     VARCHAR(300),
   OBJECT_OWNER         VARCHAR2(30),
   OBJECT_TYPE          VARCHAR2(20),
   IS_ALTER_COLUMN      VARCHAR(10),
   IS_DROP_COLUMN       VARCHAR(10),
   SQL_ID               VARCHAR(13),
   SQL_TEXT             CLOB,
   CURRENT_USER         VARCHAR(30),
   CURRENT_USERID       NUMBER,
   SESSION_USER         VARCHAR(10),
   SESSION_USERID       NUMBER,
   PROXY_USER           VARCHAR(30),
   PROXY_USERID         NUMBER,
   CURRENT_SCHEMA       VARCHAR(30),
   HOST                 VARCHAR(100),
   OS_USER              VARCHAR(60),
   IP_ADDRESS           VARCHAR(32),
   DDL_TIME             DATE                 DEFAULT SYSDATE,
   SESSION_ID           VARCHAR(32),
   VERSION_NO           NUMBER,
   CONSTRAINT PK_TB_SYSTEM_DDL_LOGS PRIMARY KEY (EVENT_ID)
);


COMMENT ON TABLE TB_SYSTEM_DDL_LOGS IS '【数据库日志】DDL日志表';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.EVENT_ID IS '事件ID自动生成';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.EVENT_NAME IS '事件名称';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.TERMINAL IS '客户端操作系统终端的名称';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.DB_NAME IS '数据库名称';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.OBJECT_NAME IS 'DDL发生的对象名称';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.OBJECT_NAME_LIST IS '对象列表';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.OBJECT_OWNER IS
'DDL发生对象的宿主';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.OBJECT_TYPE IS
'对象类别';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.IS_ALTER_COLUMN IS
'当列被修改的时候为真，否则为假 ';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.IS_DROP_COLUMN IS
'当列被DROP的时候为真，否则为假 ';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.SQL_ID IS
'SQL_ID';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.SQL_TEXT IS
'SQL语句';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.CURRENT_USER IS
'当前SESSION拥有权限的用户的名称（比如说当前SESSION是SYS,但是正在执行system.myproc，那么current_user就是system）';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.CURRENT_USERID IS
'当前SESSION拥有的权限的用户的ID';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.SESSION_USER IS
'session所属的用户名';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.SESSION_USERID IS
'当前SESSION所属的用户id';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.PROXY_USER IS
'打开当前SESSION的用户的名称';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.PROXY_USERID IS
'打开当前SESSION的用户的ID';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.CURRENT_SCHEMA IS
'当前SESSION缺省的SCHEMA名称，可以用SESSION SET CURRENT_SCHEMA语句修改';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.HOST IS
'客户端的主机名称';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.OS_USER IS
'客户端的操作系统用户名';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.IP_ADDRESS IS
'客户端的IP地址';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.DDL_TIME IS
'修改时间';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.SESSION_ID IS
'SESSION_ID';


COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.VERSION_NO IS
'版本号';

序列的作用主要是为了标识DDL操作版本，采用的是一种类似与SVN版本控制的方式，每发生一次变化版本号就加一
表是用来存储变更记录的
创建触发器

CREATE OR REPLACE TRIGGER TRIG_MONITOR_SYSTEM_DDL
AFTER DDL ON DATABASE
/**
 * 创建时间：2014年7月1日09:49:02
 * 描述：监控DDL操作并将DDL操作及DDL语句记录到日志表中
 */
DECLARE
      PRAGMA AUTONOMOUS_TRANSACTION;
      TR_EVENT_ID VARCHAR2(32);
      TR_TERMINAL VARCHAR2(50);
      TR_IPADDR VARCHAR2(30);
      TR_CUR_USER VARCHAR2(30);
      TR_CUR_USERID NUMBER;
      TR_SE_USER VARCHAR2(30);
      TR_SE_USERID NUMBER;
      TR_PROXY_USER VARCHAR2(30);
      TR_PROXY_USERID NUMBER;
      TR_CUR_SC VARCHAR2(30);
      TR_HOST VARCHAR2(100);
      TR_OS_USER VARCHAR2(60);
      TR_SESSIONID VARCHAR2(32);
      TR_SQL_ID VARCHAR2(13);
      TR_SQL VARCHAR2(60);
      TR_VERSION_NO NUMBER;
      TR_N NUMBER;
      TR_STMT CLOB := NULL;
      TR_SQL_TEXT ORA_NAME_LIST_T;
BEGIN
  TR_EVENT_ID := SYS_GUID();
      --获取用户信息
      SELECT NVL(SYS_CONTEXT('USERENV','TERMINAL'),''),--客户端操作系统终端的名称
             NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'),''),--客户端操作系统终端的名称
             NVL(SYS_CONTEXT('USERENV','CURRENT_USER'),''),--当前SESSION拥有权限的用户的名称（比如说当前SESSION是SYS,但是正在执行SYSTEM.MYPROC，那么CURRENT_USER就是SYSTEM）
             NVL(SYS_CONTEXT('USERENV','CURRENT_USERID'),''),--当前SESSION拥有的权限的用户的ID
             NVL(SYS_CONTEXT('USERENV','SESSION_USER'),''),--SESSION所属的用户名
             NVL(SYS_CONTEXT('USERENV','SESSION_USERID'),''),--当前SESSION所属的用户ID
             NVL(SYS_CONTEXT('USERENV','PROXY_USER'),''),--打开当前SESSION的用户的名称
             NVL(SYS_CONTEXT('USERENV','PROXY_USERID'),''),--打开当前SESSION的用户的ID
             NVL(SYS_CONTEXT('USERENV','CURRENT_SCHEMA'),''),--当前SESSION缺省的SCHEMA名称
             NVL(SYS_CONTEXT('USERENV','HOST'),''),--客户端的主机名称
             NVL(SYS_CONTEXT('USERENV','OS_USER'),''),--客户端的操作系统用户名
             NVL(SYS_CONTEXT('USERENV','SESSIONID'),'')--SESSION的ID
      INTO TR_TERMINAL,TR_IPADDR,TR_CUR_USER,TR_CUR_USERID,TR_SE_USER,TR_SE_USERID,TR_PROXY_USER,TR_PROXY_USERID,
           TR_CUR_SC,TR_HOST,TR_OS_USER,TR_SESSIONID
      FROM DUAL;


      --获取DDL SQL语句，如果语句过长无法全部获得，可以根据SQL_ID查询
      BEGIN
        SELECT SQL_TEXT,SQL_ID INTO TR_SQL,TR_SQL_ID
           FROM V$OPEN_CURSOR
           WHERE UPPER(SQL_TEXT) LIKE 'ALTER%'
                 OR UPPER(SQL_TEXT) LIKE 'CREATE%'
                 OR UPPER(SQL_TEXT) LIKE 'DROP%';


           TR_N := ORA_SQL_TXT(TR_SQL_TEXT);


           FOR I IN 1 .. TR_N LOOP
             TR_STMT := TR_STMT || TR_SQL_TEXT(I);
           END LOOP;


          EXCEPTION WHEN OTHERS THEN
           TR_SQL_ID := NULL;
           TR_STMT := NULL;
      END;

--向TB_SYSTEM_DDL_LOGS日志表中插入DDL操作记录
      IF ORA_SYSEVENT <> 'TRUNCATE' AND ORA_DICT_OBJ_NAME NOT LIKE 'SYS_C%' THEN
        SELECT SEQ_DDL_VERSION.NEXTVAL INTO TR_VERSION_NO FROM DUAL;
        INSERT INTO TB_SYSTEM_DDL_LOGS
              (EVENT_ID,EVENT_NAME,TERMINAL,DB_NAME,OBJECT_NAME,OBJECT_OWNER,OBJECT_TYPE,
              IS_ALTER_COLUMN,IS_DROP_COLUMN,SQL_ID,SQL_TEXT,SESSION_ID,
              CURRENT_USER,CURRENT_USERID,SESSION_USER,SESSION_USERID,
              PROXY_USER,PROXY_USERID,CURRENT_SCHEMA,HOST,OS_USER,IP_ADDRESS,VERSION_NO)
        VALUES (TR_EVENT_ID,ORA_SYSEVENT,TR_TERMINAL,ORA_DATABASE_NAME,ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_OWNER,ORA_DICT_OBJ_TYPE,
              NULL,NULL,TR_SQL_ID,TR_STMT,TR_SESSIONID,
              TR_CUR_USER,TR_CUR_USERID,TR_SE_USER,TR_SE_USERID,
              TR_PROXY_USER,TR_PROXY_USERID,TR_CUR_SC,TR_HOST,TR_OS_USER,TR_IPADDR,TR_VERSION_NO
        );
        COMMIT;
      END IF;
END;