Author Eygle ----------------------------------- | - Implement DDL refuse to prevent CREATE Important operations such as DROP, TRUNCATE, ALTER. | ---------------------------------- 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 => 'mad you want to delete the table' || ora_dict_obj_name () || '? !!!' || "Oral, the police came to catch you ... '); 9 end if; 10 end; 11 /
Trigger has been created
SQL> CREATE TABLE TEST (A Number);
The table has been created.
SQL> Drop Test; Drop Table Test * Error is located in Chapter 1: ORA-00604: Retringenized SQL layer 1 error ORA-20000: You are crazy, want to delete the table Test? !!! You finished, the police came to catch you. ... ORA-06512: In line 4
----------------------------------- | --DDL monitoring and control | ------- ---------------------------- 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 || || 8 ora_dict_obj_name || '' || l_errmsg) ; 9 elsif ora_sysevent = 'ALTER' then 10 raise_application_error (-20001, ora_dict_obj_owner || || 11 ora_dict_obj_name || ''. '' || l_errmsg); 12 elsif ora_sysevent = 'DROP' then 13 raise_application_error (-20001, ora_dict_obj_owner | '.'. | || 14 ora_dict_obj_name || '' || l_errmsg); 15 elsif ora_sysevent = 'TRUNCATE' then 16 raise_application_error (-20001, ora_dict_obj_owner || '' || 17 ora_dict_obj_name || '' || l_errmsg); 18 end if; 19 Exception 20 When no_data_found the 21 NULL; 22 End; 23 /
Trigger has been created
Time: 00:00:00.00sql> Create Table B (B Number); Create Table B (B Number) * Error is located in Chapter 1: ORA-00604: Retrieving SQL layer 1 error ORA-20001: Sys.b You have no permission to this Operationra-06512: In line 5sql> Drop Trigger DDL_DENY;
Trigger has been discarded
----------------------------------- | - For important tables, implement DDL rejection, prevent Delete, Update, INSERT and other important operations. | ---------------------------------- SQL> CONN Scott / Tiger @ Oemrep has connection. SQL> @c: /trigger_del.txtSQL> rem this script can be used to monitor a objectSQL> rem deny an delete operation on itSQL> rem this trigger can not run with sysSQL> rem this trigger can not create on databaseSQL> create Or Replace Trigger TRG_DEL 2 Before INSERT OR DELETE or UPDATE IN ZJ1 3 for Each Row 4 Begin 5 Raise_Application_ERROR (6 Num => - 20000, 7 msg => "You are crazy, want to delete record ?!!! '||' You After it, the police came to catch you ... '); 8 End; 9 /
Trigger has been created
Time: 00: 00: 00.00sql> delete zj1 where xh = 18; delete zj1 where xh = 18 * error is located in Chapter 1: ORA-20000: You are crazy, want to delete record ?!!! Oral, police Come to catch you ... ora-06512: In "Scott.trg_Del", line 2OR-04088: Trigger 'Scott.TRG_DEL' is wrong
Time: 00: 00:00.00.00sql> delete zj1; delete zj1 * Error is located in Chapter 1: ORA-20000: You are crazy, want to delete record ?!!! You are finished, the police will catch you ... ora -06512: In "scott.trg_del", line 2OR-04088: Trigger 'Scott.trg_del' Execution Time: 00: 00:00.00sql> Update ZJ1 Set Name = 'LL' WHERE XH = 18; Update zj1 set name = 'll' where xh = 18 * Error is located on the first line: ORA-20000: You are crazy, want to delete record ?!!! Oral, police to catch you ... ORA-06512: "Scott.trg_del", line 2OR-04088: Trigger 'scott.trg_del' is in the process of execution
Time: 00: 00: 00.00sql>
