[原创]如何使用触发器实现数据库级守护,防止DDL操作

eygle

如何使用触发器实现数据库级守护,防止DDL操作

--对于重要对象,实施DDL拒绝,防止create,drop,truncate,alter等重要操作

Last Updated: Sunday, 2004-10-31 12:06 Eygle
   
原文出处:
http://www.eygle.com/faq/Use.Trigger.To.implement.ddl.deny.htm
  

不管是有意还是无意的,你可能会遇到数据库中重要的数据表等对象被drop掉的情况,这可能会给我们带来巨大的损失.

通过触发器，我们可以实现对于表等对象的数据库级守护，禁止用户drop操作.

以下是一个简单的范例，供参考:

1. 
   
2. REM this script can be used to monitor a object
   
3. REM deny any drop operation on it.
   
4. CREATE OR REPLACE TRIGGER trg_dropdeny
   
5.    BEFORE DROP ON DATABASE
   
6. BEGIN
   
7.    IF LOWER (ora_dict_obj_name ()) = 'test'
   
8.    THEN
   
9.       raise_application_error (num      =>; -20000,
   
10.                                msg      =>;    '你疯了,想删除表 '
    
11.                                            || ora_dict_obj_name ()
    
12.                                            || ' ?!!!!!'
    
13.                                            || '你完了，警察已在途中.....'
    
14.                               );
    
15.    END IF;
    
16. END;
    
17. /                                          
    
18.                      

复制代码

测试效果:

1. 
   
2. SQL>; connect scott/tiger
   
3. Connected.
   
4. SQL>; create table test as select * from dba_users;
   
5. 
   
6. Table created.
   
7. 
   
8. SQL>; connect / as sysdba
   
9. Connected.
   
10. SQL>; create or replace trigger trg_dropdeny
    
11.   2     before drop on database   
    
12.   3  begin
    
13.   4        if lower(ora_dict_obj_name()) = 'test'        
    
14.   5        then
    
15.   6        raise_application_error(
    
16.   7           num =>; -20000,
    
17.   8           msg =>; '你疯了,想删除表 ' || ora_dict_obj_name() || ' ?!!!!!' ||'你完了，警察已在途中.....');
    
18.   9        end if;
    
19. 10     end;
    
20. 11  /
    
21. 
    
22. Trigger created.
    
23. 
    
24. SQL>; connect scott/tiger
    
25. Connected.
    
26. SQL>; drop table test;
    
27. drop table test
    
28. *
    
29. ERROR at line 1:
    
30. ORA-00604: error occurred at recursive SQL level 1
    
31. ORA-20000: 你疯了,想删除表 TEST ?!!!!!你完了，警察已在途中.....
    
32. ORA-06512: at line 4
    
33.                                           
    
34. 
    

复制代码

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

1. 
   
2. 
   
3. create or replace trigger ddl_deny
   
4. before create or alter or drop or truncate on database
   
5. declare
   
6.   l_errmsg varchar2(100):= 'You have no permission to this operation';
   
7. begin
   
8.   if ora_sysevent = 'CREATE' then
   
9.      raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
   
10.   elsif ora_sysevent = 'ALTER' then
    
11.     raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
    
12.   elsif ora_sysevent = 'DROP' then
    
13.     raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
    
14.   elsif ora_sysevent = 'TRUNCATE' then
    
15.     raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
    
16.   end if;
    
17. 
    
18. exception
    
19.   when no_data_found then
    
20.     null;
    
21. end;
    
22. /
    
23. 
    
24.                      

复制代码

我们看一下效果:

1. 
   
2. [oracle@jumper tools]$ sqlplus "/ as sysdba"
   
3. SQL*Plus: Release 9.2.0.4.0 - Production on Sun Oct 31 11:38:25 2004
   
4. 
   
5. Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
   
6. 
   
7. 
   
8. Connected to:
   
9. Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
   
10. With the Partitioning option
    
11. JServer Release 9.2.0.4.0 - Production
    
12. 
    
13. SQL>; set echo on
    
14. SQL>; @ddlt
    
15. SQL>; create or replace trigger ddl_deny
    
16. 2 before create or alter or drop or truncate on database
    
17. 3 declare
    
18. 4 l_errmsg varchar2(100):= 'You have no permission to this operation';
    
19. 5 begin
    
20. 6 if ora_sysevent = 'CREATE' then
    
21. 7 raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
    
22. 8 elsif ora_sysevent = 'ALTER' then
    
23. 9 raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
    
24. 10 elsif ora_sysevent = 'DROP' then
    
25. 11 raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
    
26. 12 elsif ora_sysevent = 'TRUNCATE' then
    
27. 13 raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
    
28. 14 end if;
    
29. 15
    
30. 16 exception
    
31. 17 when no_data_found then
    
32. 18 null;
    
33. 19 end;
    
34. 20 /
    
35. 
    
36. Trigger created.
    
37. 
    
38. SQL>;
    
39. SQL>;
    
40. SQL>; connect scott/tiger
    
41. Connected.
    
42. SQL>; create table t as select * from test;
    
43. create table t as select * from test
    
44. *
    
45. ERROR at line 1:
    
46. ORA-00604: error occurred at recursive SQL level 1
    
47. ORA-20001: SCOTT.T You have no permission to this operation
    
48. ORA-06512: at line 5
    
49. 
    
50. 
    
51. SQL>; alter table test add (id number);
    
52. alter table test add (id number)
    
53. *
    
54. ERROR at line 1:
    
55. ORA-00604: error occurred at recursive SQL level 1
    
56. ORA-20001: SCOTT.TEST You have no permission to this operation
    
57. ORA-06512: at line 7
    
58. 
    
59. 
    
60. SQL>; drop table test;
    
61. drop table test
    
62. *
    
63. ERROR at line 1:
    
64. ORA-00604: error occurred at recursive SQL level 1
    
65. ORA-20001: SCOTT.TEST You have no permission to this operation
    
66. ORA-06512: at line 9
    
67. 
    
68. 
    
69. SQL>; truncate table test;
    
70. truncate table test
    
71. *
    
72. ERROR at line 1:
    
73. ORA-00604: error occurred at recursive SQL level 1
    
74. ORA-20001: SCOTT.TEST You have no permission to this operation
    
75. ORA-06512: at line 11
    
76. 
    
77. 
    
78. 
    

复制代码

                       


我们可以看到,ddl语句都被禁止了,如果你不是禁止,可以选择把执行这些操作的用户及时间记录到另外的临时表中.以备查询.