时间戳

首页 > 解决方案 > 授予数据库中的所有权限有什么作用？

snowflake-cloud-data-platform - 授予数据库中的所有权限有什么作用？

问题描述

我尝试了以下授权，期望它授予对数据库中所有未来模式和表的读取访问权限test。这授予了对当前表的访问权限，但未来的授予不起作用。

grant usage on all schemas in database test to role dev;
grant usage on future schemas in database test to role dev;
grant select on all tables in database test to role dev;
grant select on future tables in database test to role dev;

我必须运行以下命令才能让未来的赠款在模式 dw 中工作

grant select on all tables in schema test.dw to role dev;
grant select on future tables in schema test.dw to role dev;

第一批赠款做了什么？

标签： snowflake-cloud-data-platform

解决方案

首次授权应授予角色 DEV 对任何模式中所有新表的访问权限。我试图重现这个问题：

create database test;
create role dev;

grant usage on all schemas in database test to role dev;
grant usage on future schemas in database test to role dev;
grant select on all tables in database test to role dev;
grant select on future tables in database test to role dev;

create schema test_schema;
create table test_table (name varchar);

show grants on schema test_schema;

+-------------------------------+-----------+------------+------------------+------------+--------------+--------------+--------------+
|          created_on           | privilege | granted_on |       name       | granted_to | grantee_name | grant_option |  granted_by  |
+-------------------------------+-----------+------------+------------------+------------+--------------+--------------+--------------+
| 2021-09-16 13:25:01.099 -0700 | OWNERSHIP | SCHEMA     | TEST.TEST_SCHEMA | ROLE       | ACCOUNTADMIN | true         | ACCOUNTADMIN |
| 2021-09-16 13:25:01.101 -0700 | USAGE     | SCHEMA     | TEST.TEST_SCHEMA | ROLE       | DEV          | false        | ACCOUNTADMIN |
+-------------------------------+-----------+------------+------------------+------------+--------------+--------------+--------------+

show grants on table test_table;

+-------------------------------+-----------+------------+-----------------------------+------------+--------------+--------------+--------------+
|          created_on           | privilege | granted_on |            name             | granted_to | grantee_name | grant_option |  granted_by  |
+-------------------------------+-----------+------------+-----------------------------+------------+--------------+--------------+--------------+
| 2021-09-16 13:25:02.280 -0700 | OWNERSHIP | TABLE      | TEST.TEST_SCHEMA.TEST_TABLE | ROLE       | ACCOUNTADMIN | true         | ACCOUNTADMIN |
| 2021-09-16 13:25:02.337 -0700 | SELECT    | TABLE      | TEST.TEST_SCHEMA.TEST_TABLE | ROLE       | DEV          | false        | ACCOUNTADMIN |
+-------------------------------+-----------+------------+-----------------------------+------------+--------------+--------------+--------------+

正如我所看到的，它按预期工作。您是否在该架构上定义了任何其他“未来授权”（对于任何其他角色）？当在数据库和模式级别都定义了未来的授权时，模式级别的授权优先于数据库级别的授权，并且数据库级别的授权被忽略。

https://docs.snowflake.com/en/sql-reference/sql/grant-privilege.html#considerations

推荐阅读