使用含DDL权限账号时，如何限制执行存储过程的会话的DDL权限？

可以通过会话级权限切换、动态SQL语法校验等方式实现目标，以下是针对主流数据库的可行方案：

1. 会话级权限切换（核心方案） #

利用数据库的上下文切换或临时权限回收机制，让动态SQL在低权限上下文执行，避免DDL风险：

Oracle #

在存储过程中临时回收当前会话的DDL权限，执行完动态SQL后再恢复。注意存储过程需用AUTHID DEFINER定义（以高权限定义者身份执行权限变更）：

CREATE OR REPLACE PROCEDURE execute_dynamic_sql(p_sql IN VARCHAR2)
AUTHID DEFINER
IS
BEGIN
    -- 临时回收DDL相关权限
    REVOKE ALTER ANY TABLE, DROP ANY TABLE, CREATE ANY TABLE FROM CURRENT_USER;
    
    -- 执行读取到的动态SQL
    EXECUTE IMMEDIATE p_sql;
    
    -- 恢复原权限
    GRANT ALTER ANY TABLE, DROP ANY TABLE, CREATE ANY TABLE TO CURRENT_USER;
EXCEPTION
    WHEN OTHERS THEN
        -- 异常时也要恢复权限，避免会话权限异常
        GRANT ALTER ANY TABLE, DROP ANY TABLE, CREATE ANY TABLE TO CURRENT_USER;
        RAISE;
END;
/

SQL Server #

通过EXECUTE AS切换到预先创建的低权限用户执行动态SQL，完成后切回原用户：

-- 先创建仅具备DML权限的用户
CREATE USER LowPrivUser WITHOUT LOGIN;
GRANT UPDATE, DELETE, INSERT ON TargetTable TO LowPrivUser;

-- 存储过程实现
CREATE PROCEDURE execute_dynamic_sql @dynamic_sql NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    -- 切换到低权限用户
    EXECUTE AS USER = 'LowPrivUser';
    
    -- 执行动态SQL
    EXEC sp_executesql @dynamic_sql;
    
    -- 切回原用户
    REVERT;
END;

PostgreSQL #

使用SET ROLE切换到低权限角色执行动态SQL：

-- 创建低权限角色并授权
CREATE ROLE low_priv_role;
GRANT UPDATE, DELETE, INSERT ON target_table TO low_priv_role;

-- 存储过程内实现
CREATE OR REPLACE FUNCTION execute_dynamic_sql(p_sql TEXT)
RETURNS VOID AS $$
BEGIN
    SET ROLE low_priv_role;
    EXECUTE p_sql;
    RESET ROLE;
END;
$$ LANGUAGE plpgsql;

2. 动态SQL语法校验（辅助防护） #

在执行前对读取到的SQL语句做关键字过滤，拦截包含DDL操作的语句，作为权限控制的补充：

-- 示例：SQL Server中拦截DDL关键字
IF @dynamic_sql LIKE '%ALTER%' 
    OR @dynamic_sql LIKE '%DROP%' 
    OR @dynamic_sql LIKE '%CREATE%'
    OR @dynamic_sql LIKE '%TRUNCATE%'
BEGIN
    RAISERROR('禁止执行DDL语句，请检查输入内容', 16, 1);
    RETURN;
END

注意：要考虑关键字的大小写、空格、注释绕过等情况，比如可以统一转成大写后再匹配，同时过滤注释内容。

3. 关键注意事项 #

• 权限切换操作必须包含异常处理，确保即使动态SQL执行失败，会话权限也能恢复，避免影响后续操作。
• 低权限用户/角色仅授予必要的DML权限，遵循最小权限原则。
• 保留原有的审批和状态校验流程，和上述方案形成多层安全防护，降低风险。

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