How to use triggers to implement database-level daemons to prevent DDL operations

Triggers | data | database

How to use triggers to implement database-level daemons to prevent DDL operations

--for important objects, the implementation of DDL rejection, prevent create,drop,truncate,alter and other important operations

Last Updated:sunday, 2004-10-31 12:06 eygle




Whether intentionally or unintentionally, you may experience the drop of objects such as important data tables in your database, which can be a huge loss.

With triggers, we can implement database-level protection for objects such as tables, and prohibit user drop operations.

The following is a simple example for reference:

REM This script can is used to monitor a objectrem deny any drop operation on it. CREATE OR REPLACE TRIGGER trg_dropdeny before DROP on Databasebegin IF LOWER (ora_dict_obj_name ()) = ' Test ' THEN raise_ap Plication_error (num => -20000, msg => ' You're crazy, want to delete table ' | | | ora_dict_obj_name () | | ' ?!!!!!' || ' You're done, the police are on their way ... ' End IF; end;/

Test effect:



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 => ' You're crazy, want to delete table ' | | | ora_ Dict_obj_name () | | ' ?!!!!!' ||' You're done, the police are on their way ... ' 9 End If; Ten 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: You're crazy, want to delete the table TEST?!!!!! You're done, the police are on their way ... Ora-06512:at Line 4



Oracle begins with oracle8i, allowing the implementation of DDL event trigger, but for the monitoring and control of DDL, here's a further example:

Create or Replace trigger Ddl_denybefore create or alter or drop or truncate on Databasedeclare l_errmsg varchar2 (100): = ' You are have no permission to this operation '; begin if ora_sysevent = ' CREATE ' then Raise_application_error ( -20001, Ora_dict_o Bj_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;/



Let's look at the effect:

[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 (MB): = ' 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);
elsif ora_sysevent = ' DROP ' Then
One 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;
15
exception
When No_data_found Then
null;
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 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 your 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 your 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 your have no permission to this operation
Ora-06512:at Line 11





As we can see, DDL statements are prohibited, and if you are not prohibited, you can choose to record the users and the time that you perform these actions to another temporary table. For enquiries.







The author of this article:
Eygle,oracle technology concern, from China's largest Oracle technology forum Itpub.
Www.eygle.com is the author's personal site. You can contact the author by Guoqiang.Gai@gmail.com. Welcome technical discussions and exchange of links.

Original source:

Http://www.eygle.com/faq/Use.Trigger.To.implement.ddl.deny.htm



If you want to reprint, please specify the author and the source. And please keep the connection to this article.


Back Page