禁止使用者的DDL操作

kewin發表於2011-09-21
禁止使用者的DDL操作
Kevin Zou
2011-9-21

給使用者賦權過大有時就是一把雙刃劍，雖然可以減少DBA的操作，但如果使用者操作失誤，把就要DBA介入來做恢復 。合理的授權一直我們DBA倡導的，人只要做合適的事情，不要做過分的事情。
一般使用者就是擁有INSERT/DELETE/UPDATE 的許可權，如果需要做DDL，那就交給DBA來操作好了。如何禁止使用者對TABLE/INDEX等物件進行DDL的操作，有多種方法，常見的有兩種：
1）SCHEMA的ONWER和讀寫分離，就是有兩個USER，其中一個USER擁有這些物件，另外一個USER只能對這些物件進行DML；
2）在ORACLE 8I開始，ORACLE 允許在DDL做觸發器。在物件級別加入觸發器，禁止物件的修改。

這裡只列出OPETION 2 的例子：
SQL> select * from v$version where rownum < 2;

BANNER
------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL> conn /as sysdba
Connected.
SQL> create user kk identified by kk;

User created.

SQL> grant connect,resource to kk;

Grant succeeded.

SQL> conn kk/kk
Connected.
SQL> create table test (id int);

Table created.

SQL> insert into test values(100);

1 row created.

SQL> commit;

SQL> conn /as sysdba
Connected.

SQL> grant  select on  v_$session  to  kk;

Grant succeeded.

SQL> grant  execute  on  dbms_system to  kk;

SQL> CREATE or  replace TRIGGER db_ddl_trigger
  2  before ddl on  kk.schema
  3    declare
  4  n number;
  5  l_trace  number;
  6
  7      BEGIN
  8        if ora_dict_obj_name() = 'TEST' then
  9           raise_application_error(-20001,'You can not execute ddl  on    '||
 ora_dict_obj_name );
 10      end if;
 11
 12      END;
 13
 14  /

Trigger created.

SQL> conn kk/kk
Connected.
SQL> alter table test add (age  char(20));
alter table test add (age  char(20))
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You can not execute ddl  on    TEST
ORA-06512: at line 7

在觸發器裡ora_dict_obj_name 是ORACLE 自定義的系統定義事件屬性，代表Name of the dictionary object on which the DDL operation occurred.
還有很多函式，詳細的列表：
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg14evt.htm

如果要執行使用者執行DDL，怎麼辦？ 呵呵。涼拌。。
DISABLE 觸發器唄。
想要DISABLE這樣獨立的觸發器，必須有ALTER TRIGGER的許可權。
SQL> alter trigger db_ddl_trigger disable;

觸發器已更改
SQL> conn /as sysdba
Connected.
SQL> alter trigger db_ddl_trigger disable;

Trigger altered.

SQL> conn kk/kk
Connected.
SQL> alter table test add (age  char(20));

Table altered.

 -THE END-

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/40239/viewspace-708017/，如需轉載，請註明出處，否則將追究法律責任。

相關文章