Creating views in schema A on tables in schema B when using Access Roles in RBAC for Snowflake

Question

I'm trying to create a view in a Snowflake schema called "A" that will be used for analyst consumption. The tables in the view will come from a schema called "B". The purpose of this is to maintain naming conventions of the underlying tables and views as well as separate out access directly to the tables. Based on our security model, it is all or nothing at a schema level. Thus if you have schema read, you can get all from tables as well as views.

The views can be created ok, but when I go to query them from the analyst role (which has been granted schema read on "A" schema) I receive the following error: "SQL compilation error: Failure during expansion of view '<table_name>': SQL compilation error: Schema '<database_name>.B' does not exist or not authorized."

Granting the schema read of "B" to the access role of "A" in turn grants access to the underlying tables as well which we don't want.

Furthermore, granting a singular "USAGE" privilege on "B" to the schema role access control results in an error message along the lines of that it cannot now find the underlying table.

The documentation states that I don't need to grant specific rights when creating views like this but it seems that only holds WITHIN the SAME schema. I don't want to split my schema read privileges into table- and view-based privileges so how can I do this?

EDIT

To add a bit more context, I have two access roles for schema A and schema A. Both with managed access set.

I have a third functional role that will have access on Schema A's role. Basically like what is yielded by below:

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

Answer 1

Can you check your view definitions? I tried to reproduce the issue but as I see, it works as expected:

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    |
+-----------+------------+--------------------+--------------+

Based on the additional info in the original question:

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