Viswamitra's blog

Today one of my friend asked me for a trigger by which he want to stop a particular user from misusing his privileges to create/drop any objects. Below is the trigger for the same.

  create or replace trigger ddl_trigger
  before DDL ON DATABASE
  declare
      l_sysevent varchar2(25);
  begin
      select ora_sysevent into l_sysevent from dual;

      if (l_sysevent in (‘CREATE’,’DROP’) AND ora_login_user in (‘TEMP’))
      then
      RAISE_APPLICATION_ERROR(-20998, ‘You do not have the privilege to create tables or other database objects. Please contact your manager if you need the privilege.’);  
      end if;
  end;
  /