CODEWITHSUNDEEP
sqloracle-databaseview
Don W.
Don W.

Reputation: 560

Prevent Drop View in Oracle

How to prevent a user from not dropping a view even he/she has previlige to do so? I have a view being used by an application. However, it was dropped by a user a couple times. Is there something like REVOKE DROP ANY VIEW? Do I have to use a trigger for that?

Upvotes: 0

Views: 245

Answers (1)

Littlefoot
Littlefoot

Reputation: 142778

A trigger might help. Here's an example.

First, a few objects to be dropped:

SQL> create table test as select * From emp where 1 = 2;

Table created.

SQL> create or replace view v_dept as select * From dept;

View created.

SQL>

I don't want to allow V_DEPT to be dropped, so:

SQL> create or replace trigger trg_drop
  2    before drop on schema
  3  declare
  4    l_name varchar2(30);
  5  begin
  6    select ora_dict_obj_name
  7      into l_name
  8      from dual;
  9    if l_name = 'V_DEPT' then
 10       raise_application_error(-20001, 'Forbidden');
 11    end if;
 12  end;
 13  /

Trigger created.

Testing:

SQL> drop view v_dept;
drop view v_dept
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Forbidden
ORA-06512: at line 8


SQL> drop table test;

Table dropped.

SQL>

Upvotes: 2

Related Questions