Shulou(Shulou.com)06/02 Report--

Sometimes, to prevent accidental deletion of tables or other database objects in the database, it can be achieved by artificially adding a trigger. As follows:

Create or replace trigger ddl_deny

Before create or alter or drop or truncate on database

Declare

L_errmsg varchar2: = '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

/

It is best to execute with sysdba login, and then test drop with a business user login

SQL > drop table test

Drop table a

ORA-00604: an error occurred in recursive SQL level 1

ORA-20001: TEST.TEST You have no permission to this operation

ORA-06512: in line 9

Here is a reference to the solution of a master, similar requirements can be modified with reference to this.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.