创建DDL触发器

来源：互联网发布：金山软件成都招聘编辑：程序博客网时间：2024/06/05 16:52

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

创建用户并授权

create or replace trigger tr_ddl  before ddl on databasedeclare  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_DDLAFTER 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;

0 0