控制開發人用使用DDL語句-优快云博客

本文介绍了一个Oracle数据库中的DDL触发器示例，该触发器在特定条件下阻止某些用户执行DDL操作，除非他们拥有相应的授权。通过检查用户的来源IP地址、用户名、对象类型及操作类型来实现这一目标。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE OR REPLACE TRIGGER C_DDL BEFORE
SHUTDOWN
OR DDL ON DATABASE
declare
vip varchar2(100);
obj_type varchar2(30);
obj_name varchar2(60);
vuser varchar2(30);
vsysevent varchar2(30);

BEGIN
SELECT ltrim(rtrim(SYS_CONTEXT('USERENV','IP_ADDRESS'))) into vip FROM DUAL;
select sys.dictionary_obj_type into obj_type from dual;
select sys.dictionary_obj_name into obj_name from dual;
select sys.sysevent into vsysevent from dual;

select sys_context('userenv','session_user') into vuser from dual;
if vuser in ('BACK','SODICEX','SYSTEM') then
     null;
elsif vuser='MEA' then
    if vip in('192.168.7.48','192.168.130.192') or vip is null then
      null;
    elsif vip in('192.168.126.222') and obj_type in ('PROCEDURE','FUNCTION','VIEW') and vsysevent in ('CREATE','DROP','ALTER') then
      null;
    else
      sys.dbms_system.ksdwrt(2,to_char(sysdate,'yyyymmdd hh24:mi:ss')||'ip:'||vip||'want ddl('||vsysevent||')'||vuser||'.'||obj_name||'.');
      RAISE_APPLICATION_ERROR (
         num => -20002,
         msg => '需要授權');
    end if;
elsif vuser IN('SFC','SFCC','SFCR','PTS') then
    null;
elsif vuser='PTS' then
    if vip in('192.168.7.48','192.168.126.222','192.168.130.192','192.168.126.227') or vip is null then
      null;
    elsif vip in('10.108.254.215') and obj_type in ('PROCEDURE','FUNCTION','VIEW','COLUMN','COMMENT') and vsysevent in ('CREATE','DROP','ALTER','COMMENT') then
      null;
    else
      sys.dbms_system.ksdwrt(2,to_char(sysdate,'yyyymmdd hh24:mi:ss')||'ip:'||vip||'want ddl('||vsysevent||')'||vuser||'.'||obj_name||'.');
      RAISE_APPLICATION_ERROR (
         num => -20002,
         msg => '需要授權');
    end if;
end if;
END;