[OTN] 특정 USER에서 DDL 등의 COMMAND 실행을 제한하는 방법 - DDL EVENT TRIGGER

출처 :  http://kr.forums.oracle.com/forums/thread.jspa?threadID=477235&tstart=15



제품 : ORACLE SERVER


특정 USER에서 DDL 등의 COMMAND 실행을 제한하는 방법 - DDL EVENT TRIGGER
===============================================================================



Purpose
-------------------------------------------------------------------------------

User에 있는 table등에 DDL 문장이 실행되지 않도록 막고 싶은 경우가 있다.
Oracle8.1.6 부터 사용가능한 system trigger에 의해 이런 기능을 구현해 보자.




Explanation
-------------------------------------------------------------------------------

Oracle8.1.6 의 new feature인 DDL event trigger를 이용하여 특정 user에서 특정 DDL
(예를 들어 create, drop, truncate 등)이나 모든 DDL이 실행할 때 에러를 발생시킨다거나
특정한 action을 하도록 설정할 수 있다. DML 의 경우는 기존의 trigger 대로 각 object에
대해 각각 생성하여야 한다.

이 자료에서는 주로 DDL 이나 DML 이 실행될 때 에러를 발생하도록 하여 해당 문장이
실행되지 않도록 하는 방법을 기술하였다.
(system or ddl event trigger에 대한 다른 자료로 Bulletin 11903,11848 참고)

DDL event trigger 를 이용하기 위해서는 $ORACLE_HOME/dbs/initSID.ora file에서
COMPATIBLE parameter의 값이 "8.1.6" 이상으로 설정되어 있어야 한다.

DDL event trigger 는 각 DDL이 발생할 때에 실행되는 trigger로 다음과 같은 시점에서
실행되도록 만들 수 있다.


BEFORE ALTER, AFTER ALTER, BEFORE DROP, AFTER DROP,
BEFORE ANALYZE, AFTER ANALYZE, BEFORE ASSOCIATE STATISTICS,
AFTER ASSOCIATE STATISTICS, BEFORE AUDIT, AFTER AUDIT,
BEFORE NOAUDIT, AFTER NOAUDIT, BEFORE COMMENT, AFTER COMMENT,
BEFORE CREATE, AFTER CREATE, BEFORE DDL, AFTER DDL,
BEFORE DISASSOCIATE STATISTICS, AFTER DISASSOCIATE STATISTICS,
BEFORE GRANT, AFTER GRANT, BEFORE RENAME, AFTER RENAME,
BEFORE REVOKE, AFTER REVOKE, BEFORE TRUNCATE, AFTER TRUNCATE



Example
-------------------------------------------------------------------------------

* 아래의 trigger 를 system 등의 별도로 관리하는 dba user에서 생성한다.

[예제1] EJ user에서 table과 index에 해당하는 DDL의 실행을 막는 경우

$ sqlplus system/manager

CREATE OR REPLACE TRIGGER ej_no_ddl
before DDL ON ej.schema
WHEN (ora_dict_obj_type = 'TABLE' or
ora_dict_obj_type = 'INDEX')
begin
raise_application_error (-20101, 'Cannot execute any DDL !!');
end;
/

-> 위의 trigger는 ej user의 schema 에서 Table과 Index 에 대한 DDL이 실행될 때
user-defined error ora-20101 이 발생하도록 한 것이다.



[예제2] EJ user에서 실행되는 모든 DDL을 막는 경우

$ sqlplus system/manager

CREATE OR REPLACE TRIGGER ej_no_ddl
before DDL ON ej.schema
begin
raise_application_error (-20101, 'Cannot execute any DDL !!');
end;
/

-> 위의 예제는 모든 DDL 이 실행될 때 에러를 발생시키게 된다.



예제1과 2의 경우 EJ user에서 DDL 실행시 아래와 같은 에러가 발생한다.

$ sqlplus ej/ej

SQL> create table test ( a number );
create table test ( a number )
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20101: Cannot execute any DDL !!
ORA-06512: at line 2


SQL> drop table dept;
drop table dept
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20101: Cannot execute any DDL !!
ORA-06512: at line 2




[예제3] EJ user에서 실행되는 drop과 truncate 문장을 막는 경우

$ sqlplus system/manager

CREATE OR REPLACE TRIGGER ej_no_ddl
before drop or truncate ON ej.schema
begin
raise_application_error (-20102, 'Cannot execute DROP or TRUNCATE !!');
end;
/

-> 위와 같이 trigger를 생성한 경우 EJ user에서 table의 생성은 되지만 drop은 할 수 없다.


$ sqlplus ej/ej

SQL> create table test2 ( a number );

Table created.

SQL> drop table test2;
drop table test2
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20102: Cannot execute DROP or TRUNCATE !!
ORA-06512: at line 2




[예제4] EJ user의 docu2 table에 대한 dml을 막는 경우

$ sqlplus system/manager

CREATE OR REPLACE TRIGGER ej_no_dml_docu2
before insert or update or delete on ej.docu2
begin
raise_application_error (-20103, 'Cannot execute DML');
end;
/


$ sqlplus ej/ej

SQL> delete from docu2 where docu_id=2;
delete from docu2 where docu_id=2
*
ERROR at line 1:
ORA-20103: Cannot execute DML
ORA-06512: at "SYSTEM.EJ_NO_DML_DOCU2", line 2
ORA-04088: error during execution of trigger 'SYSTEM.EJ_NO_DML_DOCU2'


* table의 작업을 위해 일시적으로 trigger의 기능을 disable 또는 enable시킬 수 있다.

$ sqlplus system/manager

SQL> alter trigger ej_no_ddl disable;

or

SQL> alter trigger ej_no_ddl enable;