透過觸發器禁止模式物件的DDL操作

create or replace trigger rmes.tri_ddl_deny

  before create or alter or drop or truncate on database

-- 禁用對RMES模式的DDL操作

 

declare

  my_errnum number := -20001;

  my_errmsg varchar2(100) := 'You have no permission to this operation.';

begin

  if ora_dict_obj_owner = 'RMES' then

    if ora_sysevent in ('CREATE', 'ALTER', 'DROP', 'TRUNCATE') then

      raise_application_error(my_errnum,

                              ora_dict_obj_owner || '.' ||

                              ora_dict_obj_name || ' ' || my_errmsg);

    end if;

  end if;

exception

  when no_data_found then

    null;

end;

/