如何禁止Oracle Schema内CREATE/ALTER/DROP等DDL操作？附尝试代码

嘿，我注意到你写的Schema级DDL触发器有个关键问题——你声明了oper变量但没给它赋值，所以触发器根本没法识别当前执行的是哪种DDL操作，自然起不到阻止作用。我来帮你修正代码，顺便给你一些实用的补充说明：

首先，我们需要用Oracle内置的ORA_SYSEVENT函数来获取当前执行的DDL操作类型，这是你原来代码缺失的核心部分。修正后的触发器代码如下：

CREATE OR REPLACE TRIGGER t_block_ddl
BEFORE CREATE OR ALTER OR DROP OR TRUNCATE OR RENAME ON SCHEMA
DECLARE
    v_oper VARCHAR2(20) := ORA_SYSEVENT; -- 获取当前DDL操作类型
BEGIN
    CASE v_oper
        WHEN 'CREATE' THEN
            RAISE_APPLICATION_ERROR(-20998, '禁止执行CREATE操作：当前Schema内不允许进行此类DDL操作');
        WHEN 'ALTER' THEN
            RAISE_APPLICATION_ERROR(-20999, '禁止执行ALTER操作：当前Schema内不允许进行此类DDL操作');
        WHEN 'DROP' THEN
            RAISE_APPLICATION_ERROR(-20999, '禁止执行DROP操作：当前Schema内不允许进行此类DDL操作');
        WHEN 'TRUNCATE' THEN
            RAISE_APPLICATION_ERROR(-20999, '禁止执行TRUNCATE操作：当前Schema内不允许进行此类DDL操作');
        WHEN 'RENAME' THEN
            RAISE_APPLICATION_ERROR(-20999, '禁止执行RENAME操作：当前Schema内不允许进行此类DDL操作');
    END CASE;
END;
/

• 为什么原来的代码无效？ 你声明的oper变量没有被赋值，所以触发器无法判断当前执行的是哪种DDL操作，所有分支都不会被触发。ORA_SYSEVENT是Oracle提供的专门用于DDL触发器的函数，它会返回当前执行的DDL事件名称（比如'CREATE'、'ALTER'等）。
• 权限要求：创建Schema级触发器需要用户拥有ADMINISTER DATABASE TRIGGER权限，通常需要由DBA来执行创建操作。
• 例外场景处理：如果需要允许特定用户执行DDL（比如管理员），可以在触发器开头添加判断逻辑：

  -- 在DECLARE之后，BEGIN之前添加
  IF ORA_LOGIN_USER = 'ADMIN_USER' THEN
      RETURN; -- 直接退出触发器，允许该用户执行DDL
  END IF;
  

• 触发器管理：
  • 临时禁用触发器：ALTER TRIGGER t_block_ddl DISABLE;
  • 重新启用触发器：ALTER TRIGGER t_block_ddl ENABLE;
  • 删除触发器：DROP TRIGGER t_block_ddl;

创建触发器后，你可以尝试执行任意被阻止的DDL操作，比如：

CREATE TABLE test_table(id NUMBER);

此时应该会抛出你定义的错误提示，说明触发器已经生效。

内容的提问来源于stack exchange，提问作者satya