Snowflake DWH中Schema级CREATE TABLE权限撤销失效问题咨询

• 权限继承：Snowflake角色权限可继承，若DUMMY_ROLE被授予了其他拥有CREATE TABLE权限的父角色（如SYSADMIN、自定义高权限角色），会继承对应权限。
• PUBLIC角色默认权限：所有Snowflake角色默认继承PUBLIC角色的权限，若PUBLIC角色被授予了对应Schema的CREATE TABLE权限，所有角色都会拥有该权限，单独撤销单个角色的CREATE TABLE权限不会生效。

步骤1：排查角色继承关系 #

执行如下命令查看DUMMY_ROLE被授予的所有父角色：

SHOW GRANTS TO ROLE DUMMY_ROLE;

若返回结果中存在其他高权限角色，执行撤销命令：

REVOKE ROLE 高权限角色名 FROM ROLE DUMMY_ROLE;

步骤2：排查并撤销PUBLIC角色的多余权限 #

执行如下命令查看对应Schema的所有权限配置，确认PUBLIC角色是否有CREATE TABLE权限：

SHOW GRANTS ON SCHEMA PRD_DWH.ANALYTICS;

若存在PUBLIC角色的CREATE TABLE授权，执行撤销：

USE ROLE ACCOUNTADMIN;
REVOKE CREATE TABLE ON SCHEMA PRD_DWH.ANALYTICS FROM ROLE PUBLIC;

注意：如果其他业务角色依赖PUBLIC的该权限，可单独为对应业务角色授予CREATE TABLE权限，避免影响正常业务。

步骤3：补全只读角色所需的SELECT权限 #

原脚本仅授予了资源的USAGE权限，未授予表的SELECT权限，执行如下命令补全：

-- 授予当前所有表的SELECT权限，如需整库所有Schema生效，将IN SCHEMA改为IN DATABASE PRD_DWH即可
GRANT SELECT ON ALL TABLES IN SCHEMA PRD_DWH.ANALYTICS TO ROLE DUMMY_ROLE;
-- 授予未来新建表的SELECT权限，避免后续新建表需要重复授权
GRANT SELECT ON FUTURE TABLES IN SCHEMA PRD_DWH.ANALYTICS TO ROLE DUMMY_ROLE;
-- 如需整库所有Schema的USAGE权限，执行以下两句
GRANT USAGE ON ALL SCHEMAS IN DATABASE PRD_DWH TO ROLE DUMMY_ROLE;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE PRD_DWH TO ROLE DUMMY_ROLE;

步骤4：验证权限生效 #

切换到DUMMY_ROLE执行建表语句验证，此时会返回权限不足的报错，符合预期：

USE ROLE DUMMY_ROLE;
CREATE TABLE PRD_DWH.ANALYTICS.DELETEME (ID INTEGER);
-- 预期返回：SQL access control error: Insufficient privileges to operate on schema 'ANALYTICS'

内容的提问来源于stack exchange，提问作者rishm.msc