Snowflake中基于Schema实现Future Grants权限管控（EXECUTE AS OWNER场景）

核心思路 #

Snowflake 的 EXECUTE AS OWNER 存储过程确实不支持 SHOW 类命令，但可以通过查询系统视图替代 SHOW FUTURE GRANTS 获取权限信息，全程在 OWNER（ACCOUNTADMIN）上下文执行，无需依赖调用者权限。

方案一：直接用系统视图重构存储过程 #

用 INFORMATION_SCHEMA.FUTURE_GRANTS 替代 SHOW 命令，直接在 EXECUTE AS OWNER 存储过程里完成权限检查、撤销、授予的全逻辑。

重构后的完整代码 #

-- 参数说明
--    DATABASE_NAME: 目标数据库名
--    SCHEMA_NAME: 目标Schema名
--    OBJECT_TYPE: 对象类型（如TABLE, EXTERNAL TABLE, VIEW等）
--    ROLE_NAME: 期望拥有权限的角色名

CREATE OR REPLACE PROCEDURE MANAGE_SCHEMA_FUTURE_GRANTS(
    "DATABASE_NAME" VARCHAR(16777216),
    "SCHEMA_NAME" VARCHAR(16777216),
    "OBJECT_TYPE" VARCHAR(16777216),
    "ROLE_NAME" VARCHAR(16777216)
)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS OWNER
$$
DECLARE
    v_target_obj_type VARCHAR := UPPER(TRIM(:OBJECT_TYPE));
    v_current_grantee VARCHAR;
    v_exec_sql VARCHAR;
BEGIN
    -- 查询当前对象类型的FUTURE OWNERSHIP权限归属
    SELECT UPPER(GRANTEE_NAME) INTO v_current_grantee
    FROM INFORMATION_SCHEMA.FUTURE_GRANTS
    WHERE 
        TABLE_CATALOG = UPPER(:DATABASE_NAME)
        AND TABLE_SCHEMA = UPPER(:SCHEMA_NAME)
        AND PRIVILEGE_TYPE = 'OWNERSHIP'
        AND GRANTEE_TYPE = 'ROLE'
        -- 注意：系统视图中GRANT_ON是单数（如TABLE），SHOW命令返回的是复数（如TABLES）
        AND GRANT_ON = v_target_obj_type;

    -- 根据权限状态执行对应操作
    IF v_current_grantee IS NULL THEN
        -- 无现有权限，直接授予目标角色
        v_exec_sql := 'GRANT OWNERSHIP ON FUTURE ' || v_target_obj_type || 'S IN SCHEMA ' || :DATABASE_NAME || '.' || :SCHEMA_NAME || ' TO ROLE ' || :ROLE_NAME || ' WITH GRANT OPTION;';
        EXECUTE IMMEDIATE v_exec_sql;
        RETURN '已为对象类型 ' || v_target_obj_type || ' 授予FUTURE OWNERSHIP权限给角色 ' || :ROLE_NAME;
    ELSIF v_current_grantee != UPPER(TRIM(:ROLE_NAME)) THEN
        -- 权限归属不匹配，先撤销再授予
        v_exec_sql := 'REVOKE OWNERSHIP ON FUTURE ' || v_target_obj_type || 'S IN SCHEMA ' || :DATABASE_NAME || '.' || :SCHEMA_NAME || ' FROM ROLE ' || v_current_grantee || ';';
        EXECUTE IMMEDIATE v_exec_sql;
        
        v_exec_sql := 'GRANT OWNERSHIP ON FUTURE ' || v_target_obj_type || 'S IN SCHEMA ' || :DATABASE_NAME || '.' || :SCHEMA_NAME || ' TO ROLE ' || :ROLE_NAME || ' WITH GRANT OPTION;';
        EXECUTE IMMEDIATE v_exec_sql;
        RETURN '已撤销角色 ' || v_current_grantee || ' 的FUTURE权限，并授予给角色 ' || :ROLE_NAME;
    ELSE
        -- 权限已正确配置，无需操作
        RETURN '对象类型 ' || v_target_obj_type || ' 的FUTURE权限已属于角色 ' || :ROLE_NAME || '，无需操作';
    END IF;
END;
$$;

关键细节 #

1. 系统视图选择：INFORMATION_SCHEMA.FUTURE_GRANTS 仅对当前Schema可见，若需跨Schema查询，可改用 ACCOUNT_USAGE.FUTURE_GRANTS（需ACCOUNTADMIN权限）。
2. 对象类型格式：系统视图返回的是单数类型（如TABLE），而权限操作语法要求用复数（如TABLES），代码中已做对应转换。
3. 权限安全性：全程以ACCOUNTADMIN身份执行，无需给自定义角色额外授权，符合你的权限管控需求。

方案二：修正嵌套存储过程的权限传递 #

如果坚持用嵌套结构，需确保子存储过程能继承父存储过程的OWNER权限，调整后的代码如下：

-- 父存储过程（EXECUTE AS OWNER）
CREATE OR REPLACE PROCEDURE PARENT_SECURE_PROCEDURE(
    "DATABASE_NAME" VARCHAR(16777216),
    "SCHEMA_NAME" VARCHAR(16777216),
    "OBJECT_TYPE" VARCHAR(16777216),
    "ROLE_NAME" VARCHAR(16777216)
)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS OWNER
$$
DECLARE
    v_result VARCHAR;
BEGIN
    -- 确保子存储过程的执行权限给ACCOUNTADMIN
    GRANT EXECUTE ON PROCEDURE GET_OR_REVOKE_FUTURE_GRANTS(VARCHAR, VARCHAR, VARCHAR, VARCHAR) TO ROLE ACCOUNTADMIN;
    
    CALL GET_OR_REVOKE_FUTURE_GRANTS(:DATABASE_NAME, :SCHEMA_NAME, :OBJECT_TYPE, :ROLE_NAME) INTO v_result;
    IF v_result NOT LIKE '--%' THEN
        EXECUTE IMMEDIATE v_result;
    END IF;
    RETURN v_result;
END;
$$;

-- 子存储过程（EXECUTE AS CALLER，调用者为ACCOUNTADMIN）
CREATE OR REPLACE PROCEDURE GET_OR_REVOKE_FUTURE_GRANTS(
    "DATABASE_NAME" VARCHAR(16777216),
    "SCHEMA_NAME" VARCHAR(16777216),
    obj_type VARCHAR,
    role_name VARCHAR
)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
$$
DECLARE
    future_obj OBJECT;
    sq VARCHAR;
    cc VARCHAR;
BEGIN
    cc := '--';
    sq := 'SHOW FUTURE GRANTS IN SCHEMA ' || :DATABASE_NAME || '.' || :SCHEMA_NAME;
    EXECUTE IMMEDIATE sq;

    future_obj := (
        SELECT OBJECT_AGG(REPLACE("grant_on", 'S', ''), TO_VARIANT(UPPER("grantee_name")))  
        FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) 
        WHERE "privilege" = 'OWNERSHIP' AND "grant_to" = 'ROLE'
    );

    IF (future_obj[TRIM(UPPER(obj_type))] IS NULL) THEN                 
        cc := 'GRANT OWNERSHIP ON FUTURE ' || UPPER(obj_type) || 'S IN SCHEMA ' || :DATABASE_NAME || '.' || :SCHEMA_NAME || ' TO ROLE ' || :role_name || ' WITH GRANT OPTION;';
    ELSIF (future_obj[TRIM(UPPER(obj_type))] != UPPER(role_name)) THEN
        cc := 'REVOKE OWNERSHIP ON FUTURE ' || UPPER(obj_type) || 'S IN SCHEMA ' || :DATABASE_NAME || '.' || :SCHEMA_NAME || ' FROM ROLE ' || future_obj[TRIM(UPPER(obj_type))] || '; GRANT OWNERSHIP ON FUTURE ' || UPPER(obj_type) || 'S IN SCHEMA ' || :DATABASE_NAME || '.' || :SCHEMA_NAME || ' TO ROLE ' || :role_name || ' WITH GRANT OPTION;';
    ELSE
        cc := '-- SKIPPING, 对象类型 ' || obj_type || ' 的FUTURE权限已分配给角色 ' || role_name;
    END IF;
    RETURN cc;
END;
$$;

关键说明 #

子存储过程的EXECUTE AS CALLER会使用父存储过程的OWNER身份（ACCOUNTADMIN）执行，只要提前给子存储过程授予EXECUTE权限即可完成权限传递。

总结 #

优先推荐方案一，用系统视图替代SHOW命令的逻辑更简洁稳定，完全规避了调用者权限的问题，符合你的安全管控需求。

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