限制DDL操作（一）

出於安全性或避免影響效能的考慮，在產品資料庫中有時候會禁止或者在一定時間段內限制DDL語句的發生。Oracle也提高了很多方法來實現這個功能，這個簡單介紹一下。

這篇介紹利用觸發器來限制DDL語句。

 

 

利用觸發器來限制DDL，可能是最容易想到的功能。利用Oracle的DDL觸發器，在DDL語句執行之前RAISE_APPLICATION_ERROR，可以有效的限制DDL語句。

下面這個例子就可以禁止資料庫中的CREATE語句：

SQL> create or replace trigger tri_restrict_create
  2  before create on database
  3  begin
  4  raise_application_error(-20001, 'Restrict CREATE statement!');
  5  end;
  6  /

觸發器已建立

SQL> create synonym my_dual for dual;
create synonym my_dual for dual
*
第 1 行出現錯誤:
ORA-00604: 遞迴 SQL 級別 1 出現錯誤
ORA-20001: Restrict CREATE statement!
ORA-06512: 在 line 2

SQL> create table test (id number);
create table test (id number)
*
第 1 行出現錯誤:
ORA-00604: 遞迴 SQL 級別 1 出現錯誤
ORA-20001: Restrict CREATE statement!
ORA-06512: 在 line 2

SQL> drop trigger tri_restrict_create;

觸發器已刪除。

而下面的例子，則禁止了TEST使用者的任何DDL操作：

SQL> conn test/test                           
已連線。
SQL> create or replace trigger tri_restrict_test_ddl
  2  before ddl on schema
  3  begin
  4  raise_application_error(-20001, 'Restric DDL!');
  5  end;
  6  /

觸發器已建立

SQL> create table test (id number);
create table test (id number)
*
第 1 行出現錯誤:
ORA-00604: 遞迴 SQL 級別 1 出現錯誤
ORA-20001: Restric DDL!
ORA-06512: 在 line 2

SQL> alter table t add fid number;
alter table t add fid number
*
第 1 行出現錯誤:
ORA-00604: 遞迴 SQL 級別 1 出現錯誤
ORA-20001: Restric DDL!
ORA-06512: 在 line 2

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

會話已更改。

SQL> drop trigger tri_restrict_test_ddl;

觸發器已刪除。

使用BEFORE DDL會對所有的DDL語句都生效，像上面的這個觸發器，就禁止了TEST使用者的一切DDL語句。

注意alter session和alter system不屬於DDL語句，因此不會受這個觸發器影響。

需要注意，雖然這個觸發器可以禁止任何DDL語句，但是並不禁止刪除當前的觸發器，這是Oracle為了避免使用者建立了這個觸發器後，無法自己刪除觸發器，從而導致使用者不可用。

使用DDL觸發器來限制DDL語句的好處是思路簡單，編寫PL/SQL程式碼也不算很複雜，而且相對比較靈活，可以在觸發器內對時間、登陸IP、使用者等一系列安全條件進行判斷，而後確定使用者是否可以執行。

不過觸發器也有一定的缺點，比如需要使用者自己編寫、維護以及驗證觸發器的程式碼，而且對於DDL的粒度控制也存在問題，比如資料庫中只希望禁止ALTER TABLE，而其他物件的ALTER並不禁止，使用觸發器就很難實現。而且如果希望禁止具體某個物件的DDL，使用觸發器的方式也比較困難。