How To Limit System Privileges

BACKGROUND & OVERVIEW

This document provides a guidelines and options for restricting system priviliges. The options are demonstrated through worked examples.

TRIGGERS

  • This example shows how to create a database trigger - alter_user_trigger - that will prevent any user from modifying the SYS or SYSTEM users via the alter user command.
  • Note, as the example below demonstrates, this includes preventing the SYS user too. If the SYS or SYSTEM users need to be modified, then either drop or disable the trigger, or add extra logic to the trigger code.
  • Note, before implementing triggers of this nature, make sure the consequences are fully understood, for example, it would be recommended to disable this type of trigger whilst performing a patch upgrade.
SYS > show user
USER is "SYS"
 
SYS> create or replace trigger alter_user_trigger before ddl on database
  2  begin
  3    if ora_sysevent='ALTER' and ora_dict_obj_type = 'USER'
  4       and ora_dict_obj_name in ('SYS','SYSTEM')
  5    then 
  6      raise_application_error(-20000, 'You are not allowed to modify this user');
  7    end if;
  8  end;
  9  /
 
Trigger created.
 
SYS > alter user ap1 default tablespace SYSAUX;
 
User altered.
 
SYS > alter user sys default tablespace SYSAUX;
alter user sys default tablespace SYSAUX
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: You are not allowed to modify this user
ORA-06512: at line 5
© copyright 2001-2014 ABCdba.com | all rights reserved