Mir Sayeed Hassan – Oracle Blog

Restrict a user to drop from sys user

Find the below tested & verified method

SQL> Create user mir identified by mir;

SQL> Grant connect, resource to mir;

Now user have the privilege to connect & perform DDL,DML Etc Operation

SQL> SHO USER
USER is "SYS"

Create the trigger to restrict the user to drop from the database

SQL>  create or replace trigger drop_user_trigger
before drop on database
when ( user = 'MIR' )
declare
l_cnt number;
l_name varchar2(30);
begin
if ( ora_dict_obj_type = 'USER' )
then
l_name := ORA_DICT_OBJ_NAME;
select count(*) into l_cnt
from dual
where exists ( select null
from app_users
where username = l_name );
if ( l_cnt <> 1 )
then
raise_application_error( -20001, 'You cannot drop that user' );
end if;
end if;
end;
/
Warning: Trigger created with compilation errors.

SQL> sho user

USER is “SYS”

SQL> drop user mir;

drop user mir
*
ERROR at line 1:
ORA-04098: trigger 'SYS.DROP_USER_TRIGGER' is invalid and failed re-validation

SQL> ALTER TRIGGER drop_user_trigger disable;
Trigger altered.

SQL> drop user mir cascade;
User dropped.

Hence we can restrict the user with trigger as shown above, if we want to restrict again then enable the trigger for the user.