oracle禁止用户做DDL操作

逃离我推掉我的手 2024-02-17 20:57 47阅读 0赞

CREATE OR REPLACE TRIGGER trg_dropdeny
       BEFORE DROP ON DATABASE
    BEGIN
       IF LOWER (ora_dict_obj_name ()) = 'test'
       THEN
          raise_application_error (num      => -20000,
                                   msg      =>    '你疯了,想删除表 '
                                               || ora_dict_obj_name ()
                                               || ' ?!!!!!'
                                               || '你完了，警察已在途中.....'
                                  );
       END IF;
    END;
    /       
                          测试效果: 
    SQL> connect scott/tigerConnected.SQL> create table test as select * from dba_users;Table created.SQL> connect / as sysdbaConnected.SQL> create or replace trigger trg_dropdeny  2     before drop on database     3  begin  4        if lower(ora_dict_obj_name()) = 'test'          5        then  6        raise_application_error(  7           num => -20000,  8           msg => '你疯了,想删除表 ' || ora_dict_obj_name() || ' ?!!!!!' ||'你完了，警察已在途中.....');  9        end if; 10     end; 11  /Trigger created.SQL> connect scott/tigerConnected.SQL> drop table test;drop table test*ERROR at line 1:ORA-00604: error occurred at recursive SQL level 1ORA-20000: 你疯了,想删除表 TEST ?!!!!!你完了，警察已在途中.....ORA-06512: at line 4

Oracle从Oracle8i开始,允许实施DDL事件trigger，可是实现对于DDL的监视及控制,以下是一个进一步的例子:

create or replace trigger ddl_deny
    before create or alter or drop or truncate on database
    declare
      l_errmsg varchar2(100):= 'You have no permission to this operation';
    begin
      if ora_sysevent = 'CREATE' then
         raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
      elsif ora_sysevent = 'ALTER' then
        raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
      elsif ora_sysevent = 'DROP' then
        raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
      elsif ora_sysevent = 'TRUNCATE' then
        raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
      end if;
    
    exception
      when no_data_found then
        null;
    end;
    /

我们看一下效果:

[oracle@jumper tools]$ sqlplus "/ as sysdba"
     SQL*Plus: Release 9.2.0.4.0 - Production on Sun Oct 31 11:38:25 2004 
     Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
     
     Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
    With the Partitioning option
    JServer Release 9.2.0.4.0 - Production 
     SQL> set echo on
    SQL> @ddlt
    SQL> create or replace trigger ddl_deny
    2 before create or alter or drop or truncate on database
    3 declare
    4 l_errmsg varchar2(100):= 'You have no permission to this operation';
    5 begin
    6 if ora_sysevent = 'CREATE' then
    7 raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
    8 elsif ora_sysevent = 'ALTER' then
    9 raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
    10 elsif ora_sysevent = 'DROP' then
    11 raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
    12 elsif ora_sysevent = 'TRUNCATE' then
    13 raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
    14 end if;
    15 
    16 exception
    17 when no_data_found then
    18 null;
    19 end;
    20 / 
     Trigger created. 
     SQL> 
    SQL> 
    SQL> connect scott/tiger
    Connected.
    SQL> create table t as select * from test;
    create table t as select * from test
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-20001: SCOTT.T You have no permission to this operation
    ORA-06512: at line 5
     
     SQL> alter table test add (id number);
    alter table test add (id number)
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-20001: SCOTT.TEST You have no permission to this operation
    ORA-06512: at line 7
     
     SQL> drop table test;
    drop table test
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-20001: SCOTT.TEST You have no permission to this operation
    ORA-06512: at line 9
     
     SQL> truncate table test;
    truncate table test
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-20001: SCOTT.TEST You have no permission to this operation
    ORA-06512: at line 11