How to use triggers to realize database-level guardians, prevent DDL operations
- For important objects, implement DDL rejection, prevent important operations such as CREATE, DROP, TRUNCATE, ALTER and other
Last Updated:
Sunday, 2004-10-31 12:06
Eygle
Whether it is intentionally or unintentional, you may encounter the case where important data sheets in the database are dropped by DROP, which may bring us huge losses.
With triggers, we can implement database-level guardians for tables, and prohibit user DROP operations.
Here is a simple example for reference:
Rem this script can be used to monitor a object
Rem deny any Drop Operation on it.
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 => 'You are crazy, want to delete the table'
|| ORA_DICT_OBJ_NAME ()
|| '? !!!!!'
|| 'Oral, the police have been on the way .....'
);
END IF;
END;
/
Test effect:
SQL> Connect Scott / Tiger
Connected.
SQL> CREATE TABLE TEST AS SELECT * from DBA_USERS;
Table created.
SQL> Connect / as sysdba
Connected.
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 are crazy, want to delete the table '|| Ora_Dict_obj_name () ||'? !!!!!! '||' You are finished, the police have been on the way ..... ');
9 end if;
10 End;
11 /
Trigger created.
SQL> Connect Scott / Tiger
Connected.
SQL> Drop Table Test;
Drop Table Test
*
Error At Line 1:
ORA-00604: Error Occurred At Recursive Sql Level 1
ORA-20000: You are crazy, want to delete Test Test ?!!!!! Oral, the police have been on the way ...
ORA-06512: AT line 4
Oracle starts from Oracle8i, allowing implementation of DDL event TRIGGER, but implements monitoring and control for DDL, the following is a further example:
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';
Beginif 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_foundim
NULL;
END;
/
Let's take a 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 - Productionwith The Partitioning OptionjServer Release 9.2.0.4.0 - Production
SQL> set echo onSQL> @ddltSQL> create or replace trigger ddl_deny2 before create or alter or drop or truncate on database3 declare4 l_errmsg varchar2 (100): = 'You have no permission to this operation'; 5 begin6 if ora_sysevent = 'CREATE' then7 raise_application_error (-20001, ora_dict_obj_owner || || ora_dict_obj_name || '' || l_errmsg; 8 elsif ora_sysevent = '.') '. ALTER' then9 raise_application_error (-20001, ora_dict_obj_owner || '' || ora_dict_obj_name || '' || l_errmsg); '.' 10 elsif ora_sysevent = 'DROP' then11 raise_application_error (-20001, ora_dict_obj_owner || || ora_dict_obj_name || '' || l_errmsg); 12 elsif ora_sysevent = 'TRUNCATE' then13 raise_application_error (-20001, ora_dict_obj_owner || '.' || ORA_DICT_OBJ_NAME || '' || L_ERRMSG); 14 END IF; 15 16 Exception17 When No_Data_Found Then18 NULL; 19 END; 20 / TRIGGER CREATED.
SQL> SQL> SQL> connect scott / tigerConnected.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 1ORA-20001: Scott.t you have no permission to this Operationra-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 1ORA-20001: Scott.test You Have No Permissions To this Operationra- 06512: AT line 7
SQL> Drop table test; drop table test * error at line 1: ORA-00604: Error Occurred At Recursive Sql Level 1Or-20001: Scott.test You Have no permission to this Operationra-06512: At line 9
SQL> Truncate Table Test; Truncate Table Test * Error At line 1: ORA-00604: Error Occurred At Recursive Sql Level 1ORA-20001: Scott.test You Have No Permissions To this Operationra-06512: At line 11 We can see, DDL statements are banned, if you are not disabled, you can choose to record users and time to perform these operations into another temporary table. Inquiry.
Author: eygle, Oracle technology followers, Oracle technical forum itpub.www.eygle.com from China is the biggest author's personal site you may contact the author by Guoqiang.Gai@gmail.com welcome to explore technical exchanges and links. exchange.
Original source:
http://www.eygle.com/faq/use.trigger.to.implement.ddl.deny.htm
To reprint, please indicate the author and the source. And please keep this paper.
Back home
