snowflake-cloud-data-platform - 在 RBAC 中为 Snowflake 使用访问角色时，在架构 B 中的表上创建架构 A 中的视图

Question

我正在尝试在名为“A”的雪花模式中创建一个视图，该视图将用于分析师的消费。视图中的表将来自名为“B”的模式。这样做的目的是维护基础表和视图的命名约定，并直接分离出对表的访问。根据我们的安全模型，在架构级别上是全有或全无。因此，如果您读取了模式，则可以从表和视图中获取所有内容。

可以正常创建视图，但是当我从分析师角色（已被授予在“A”架构上读取的架构）查询它们时，我收到以下错误：“ SQL 编译错误：视图扩展失败”<table_name >'：SQL 编译错误：Schema '<database_name>.B' 不存在或未授权。”

将“B”的模式读取授予“A”的访问角色反过来也授予对我们不想要的基础表的访问权限。

此外，将“B”上的单一“USAGE”特权授予模式角色访问控制会导致错误消息类似于它现在无法找到基础表。

该文档指出，在创建这样的视图时，我不需要授予特定权限，但似乎只在相同的模式中存在。我不想将我的模式读取权限拆分为基于表和基于视图的权限，那么我该怎么做呢？

编辑

为了添加更多上下文，我对模式 A 和模式 A 有两个访问角色。两者都具有托管访问集。

我有第三个职能角色，可以访问 Schema A 的角色。基本上就像下面产生的：

use role accountadmin;

create or replace database test_db;

-- Create Managed Access Schema B with a Table and assign ownership of the database object to that schema
create or replace schema B with managed access;
create or replace table test (id number(3,2)) as select 3.14;
create or replace role access_role_b_sr ;
grant role access_role_b_sr to role accountadmin ;
grant ownership on table B.test to role access_role_b_sr revoke current grants;

-- Create Managed Access Schema A with a View and assign ownership of the database object to that schema
create or replace schema A with managed access;
create or replace view test_v as select * from B.test;
create or replace role access_role_a_sr;
grant role access_role_a_sr to role accountadmin;
grant ownership on view a.test_v to role access_role_a_sr revoke current grants;

-- Grant privileges to the Schema A access role
grant usage on database test_db to access_role_a_sr;
grant usage on schema test_db.A to access_role_a_sr;
grant select on view test_db.a.test_v to access_role_a_sr;

-- Grant privileges to the Schema B access role
grant usage on database test_db to access_role_b_sr;
grant usage on schema test_db.B to access_role_b_sr;
grant select on table test_db.b.test to access_role_b_sr;

-- Create the functional role and assign the Schema A access role
create or replace role functional_role_analyst;
grant role access_role_a_sr to role functional_role_analyst;
grant role all_u_group_bia_vwh_wrun to role functional_role_analyst;
grant role functional_role_analyst to user "admin-jaco.vanwyk";

use role functional_role_analyst;
use schema a;

select * from a.test_v; -- doesn't work 
-- SQL compilation error: Failure during expansion of view 'TEST_V': SQL compilation error: Schema 'TEST_DB.B' does not exist or not authorized.

show grants to role access_role_a_sr; -- no permission on schema B or table test

标签： snowflake-cloud-data-platformrbac

Answer 1

你能检查你的视图定义吗？我试图重现该问题，但正如我所见，它按预期工作：

use role accountadmin;

create schema B;
create  table test (id number(3,2)) as select 3.14;

create schema A;
create view test_v as select * from B.test;

create role test_role;
grant usage on database gokhan_db to test_role;
grant usage on schema gokhan_db.A to test_role;
grant select on view gokhan_db.A.test_v to test_role;
grant usage on warehouse gokhan_wh to role test_role;
grant role test_role to user gokhan;

use role test_role;

select * from test_v; -- works as expected

+------+
|  ID  |
+------+
| 3.14 |
+------+

show grants to role test_role; -- no permission on schema B or table test

+-----------+------------+--------------------+--------------+
| privilege | granted_on |        name        | grantee_name |
+-----------+------------+--------------------+--------------+
| USAGE     | DATABASE   | GOKHAN_DB          | TEST_ROLE    |
| USAGE     | SCHEMA     | GOKHAN_DB.A        | TEST_ROLE    |
| SELECT    | VIEW       | GOKHAN_DB.A.TEST_V | TEST_ROLE    |
| USAGE     | WAREHOUSE  | GOKHAN_WH          | TEST_ROLE    |
+-----------+------------+--------------------+--------------+

根据原始问题中的附加信息：

use role accountadmin;

create or replace database test_db;

-- Create Managed Access Schema B with a Table and assign ownership of the database object to that schema
create or replace schema B with managed access; -- managed access
create or replace table test (id number(3,2)) as select 3.14;
create or replace role access_role_b_sr ;
grant role access_role_b_sr to role accountadmin ;

-- Create Managed Access Schema A with a View and assign ownership of the database object to that schema
create or replace schema A with managed access;
create or replace view test_v as select * from B.test;
create or replace role access_role_a_sr;
grant role access_role_a_sr to role accountadmin;


grant usage on database test_db to access_role_a_sr;
grant usage on schema test_db.A to access_role_a_sr;


grant usage on database test_db to access_role_b_sr;
grant usage on schema test_db.B to access_role_b_sr;

grant ownership on table B.test to role access_role_b_sr revoke current grants;
grant ownership on view A.test_v to role access_role_b_sr revoke current grants;
grant select on view test_db.a.test_v to access_role_a_sr;

-- Create the functional role and assign the Schema A access role
create or replace role functional_role_analyst;
grant role access_role_a_sr to role functional_role_analyst;
grant usage on warehouse gokhan_wh to functional_role_analyst;
grant role functional_role_analyst to user gokhan;

use role functional_role_analyst;

select * from a.test_v; -- works