連結：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

--&gt編寫順序研究

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

--&gtDDL部分限制ORA_SYSEVENT部分

DECLARE

BEGIN

IF /*ORA_SYSEVENT() IN ('DROP' , 'CREATE', 'RENAME', 'ALTER','GRANT' ) */ --&gt 觸發器的事件

/* AND ORA_DICT_OBJ_OWNER() IN ('SINOTEST')--&gt..DBLINK 沒有所有者..ORA_DICT_OBJ_OWNER一說*/

ORA_DICT_OBJ_TYPE NOT IN --&gt物件型別還包括：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 索引限制...∴索引先不限制..

--&gt登陸使用者

AND ORA_LOGIN_USER NOT IN ( 'TEST' ) --&gt登入使用者'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 分類目錄。將固定連線加入收藏夾。

Oracle 觸發器 限制DDL操作

相關文章

Oracle 觸發器限制DDL操作