链接：http://blog.itpub.net/28602568/viewspace-1685213/

标题：Oracle 触发器限制DDL操作

注释：
工作中会有用于开发/测试的用户，可能在创建用户时权限设置的不严格

会存在开发人员自己创建一些对象(表、索引、dblink、等) 创建错误/临时使用完后没删除的情况；

导致上生产环境时，需要一一确认该对象是否有用(若存在离职同事..就会残留一些垃圾对象)；

所以特此写下如下触发器为限制一些环境做某些DDL操作；

也方便后续表结构或其他对象依据哪个环境同步到其他环境..那么‘源’环境就可以做限制啦~

一些注释都是当时写时遇到的，标记下的，若有不准或者有发现问题的，请说明一起探讨；

”限制一些环境做某些DDL操作 “的触发器：

******************* 只要操作DDL就打印20002错误 *********************

CREATE OR REPLACE TRIGGER DDL_RESTRICT

BEFORE DDL ON DATABASE

DECLARE

BEGIN

--出现DDL 直接打印20002错误号

RAISE_APPLICATION_ERROR ( NUM => - 20000 ,

MSG => INITCAP ( 'YOU ') ||

LOWER ('CAN''T EXECUTE ' || ORA_SYSEVENT() || ' ' ||

ORA_DICT_OBJ_NAME() || ' ' ||

ORA_DICT_OBJ_TYPE || ';' ));

END ;

*** 注释：直接全面限制DDL可能会存在问题 *****

*** 例如： synonym /procedure...等包含对象的对象，当包含的对象重新创建时

*** synonym/ procedure 随后调用时会出发一次DDL重新编译语句 , 会出现ORA- 04045

******************* 所以建议按照如下参数，配置自己想限制的对象类型即可 **********************

CREATE OR REPLACE TRIGGER DDL_RESTRICT

BEFORE DDL ON DATABASE

-->编写顺序研究

-->BEFORE部分限制ORA_SYSEVENT部分，例如：ORA_SYSEVENT=ALTER/RENAME; BEFORE=ALTER ,则RENAME不受限制.

-->DDL部分限制ORA_SYSEVENT部分

DECLARE

BEGIN

IF /*ORA_SYSEVENT() IN ('DROP' , 'CREATE', 'RENAME', 'ALTER','GRANT' ) */ --> 触发器的事件

/* AND ORA_DICT_OBJ_OWNER() IN ('SINOTEST')-->..DBLINK 没有所有者..ORA_DICT_OBJ_OWNER一说*/

ORA_DICT_OBJ_TYPE NOT IN -->对象类型还包括：SELECT DISTINCT OBJECT_TYPE FROM DBA_OBJECTS

( 'FUNCTION',

'PACKAGE' ,

'PROCEDURE' ,

'PACKAGE BODY' ,

'TYPE' ,

'TYPE BODY' ,

'TABLE' ,

'SYNONYM' ,

'VIEW' , --IMPDP 时导入用到VIEW... TABLE_EXISTS_ACTION=TRUNCATE.

'INDEX' ) --导入表是 TABLE_EXISTS_ACTION=REPLACE 索引限制...∴索引先不限制..

-->登陆用户

AND ORA_LOGIN_USER NOT IN ( 'TEST' ) -->登录用户'TEST'可操作本身/其他owner的object，其他用户不可操作'包括SYS用户'

--ORA_DICT_OBJ_OWNER() NOT IN ：表示该用户只可操作自己本身object，其他用户不可以

AND ORA_DICT_OBJ_OWNER() NOT IN ( 'TEST' )

--操作事件限制去除 truncate

AND ORA_SYSEVENT() NOT IN ( 'TRUNCATE' ) THEN

--如果违反了条件打印20002错误号

RAISE_APPLICATION_ERROR (NUM => - 20000,

MSG => INITCAP ( 'YOU ') ||

LOWER ('CAN''T EXECUTE ' || ORA_SYSEVENT() || ' ' ||

ORA_DICT_OBJ_NAME() || ' ' ||

ORA_DICT_OBJ_TYPE || ';' )); --如果违反了条件包20002错误号

END IF ;

---由于上文提到 dblink obj_type没有ORA_DICT_OBJ_OWNER 一说，所以特开启一部分编写DBLINK限制

IF ORA_DICT_OBJ_TYPE = 'DATABASE LINK' AND ORA_LOGIN_USER NOT IN ('TEST' ) THEN

RAISE_APPLICATION_ERROR (NUM => - 20000,

MSG => INITCAP ( 'YOU ') ||

LOWER ('CAN''T EXECUTE ' || ORA_SYSEVENT() || ' ' ||

ORA_DICT_OBJ_NAME() || ' ' ||

ORA_DICT_OBJ_TYPE || ';' ));

END IF ;

END ;

【源于本人笔记】若有书写错误，表达错误，请指正...

此条目发表在 Oracle 分类目录。将固定连接加入收藏夹。

ITPub博客

Oracle 触发器限制DDL操作

最新文章

ITPub博客

Oracle 触发器 限制DDL操作

最新文章

Oracle 触发器限制DDL操作