Friday, October 28, 2011

Restrict A User From Being Dropped


Sometimes  we have to  put  the  restriction  on  those  user which  is  having DBA role even though they can't drop a particular schemas . In such scenario's we have to create the triggers to restrict the user   before  the drop command . Below script  will restrict the DBA's  to drop the particular schemas . Here is the triggers.


SQL> Create Or Replace Trigger  TrgDropUserRestrict
Before Drop On Database
Declare
        Begin If Ora_Dict_Obj_Name In ('SCOTT','OUTLN')    Then 
 Raise_Application_Error(-20001,'Cannot Drop User '||ora_dict_obj_name||' Contact Your Database Administrator For Dropping This User !');  
        End If;
End;
/

Enjoy        :-)



3 comments:

AFZAL KHAN said...

if i want to drop the then how can

AFZAL KHAN said...

if want drop user.then how

NEERAJ VISHEN said...

As far my understanding of your doubt, you want to drop the schemas after creating the triggers. If it is so , then either disable or drop the trigger and then drop the schemas .
First of all you have find the name of the trigger by using the dba_triggers view .Once you have trigger name then you can either disable or drop the trigger as

ALTER TRIGGER DISABLE;
or
DROP TRIGGER ;