DanBrother的部落格

/**********************************************************************************************/
-- Oracle Trigger for Allow None-DBA user to only truncate one's table    
-- Note: Create the Trigger Under SYS schema                                                        
/**********************************************************************************************/
CREATE OR REPLACE TRIGGER t_truncate_only_check
BEFORE DROP OR CREATE
ON database
DECLARE
v_check number(1);
v_login_user varchar2(30);
BEGIN
    v_login_user:=ora_login_user;
    select count(1) into v_check
    from dba_role_privs
    where grantee = v_login_user and granted_role = 'DBA';

   if v_check = 0 then
      RAISE_APPLICATION_ERROR(-20001, 'Only DBA Can Create Or Drop
      Production Table');
   end if;
END save_our_db;
/