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        :-)



at

4 comments:

  1. AFZAL KHANMay 25, 2012 at 6:38 PM

    if i want to drop the then how can

    ReplyDelete
  2. AFZAL KHANMay 25, 2012 at 6:39 PM

    if want drop user.then how

    ReplyDelete
  3. NEERAJ VISHENMay 26, 2012 at 9:54 AM

    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 ;

    ReplyDelete
  4. AnonymousJuly 5, 2014 at 3:10 AM

    Have you ever considered writing an e-book or guest authoring on other blogs?
    I have a blog based on the same ideas you discuss and would really like to
    have you share some stories/information. I know my visitors would value your work.
    If you are even remotely interested, feel free to send me
    an e-mail.

    My web site ... boom beach cheats jailbreak

    ReplyDelete