Search code examples
permissionssnowflake-cloud-data-platformfuture

future grants on a snowflake database

Any insight on why the below grant statements are not providing future grants on tables in database db_prod for role analyst_legacy_test? Below grants do give select to views and tables, but not to future views or tables...(when a new table is created by another role, analyst_legacy_test role cannot see it or view it)

use role securityadmin;

grant usage on database db_prod to role analyst_legacy_test;
grant usage on all schemas in database db_prod to role analyst_legacy_test;
grant select on all tables in database db_prod to role analyst_legacy_test;
grant select on all views in database db_prod to role analyst_legacy_test;

grant usage on future schemas in database db_prod to role ANALYST_LEGACY_TEST;
grant select on future tables in database db_prod to role analyst_legacy_test;
grant select on future views in database db_prod to role ANALYST_LEGACY_TEST;

I've read multiple pieces of documentation and pulled these statements from there.

https://community.snowflake.com/s/article/How-to-grant-select-on-all-future-tables-in-a-schema-and-database-level

I've also read about the potential issue with future grant precedence (that if you designate schema level future grants the datbase level grants will be ignored). I don't believe my statements apply to this scenario

https://community.snowflake.com/s/article/Precedence-rule-for-future-grants

UPDATE/SOLUTION: The issue was that another role had schema level future grants. From the documentation it was not clear to me the future grants precedence issue spanned across roles. To resolve issue, I had to check every role in database for schema level grants (and remove them) or grant schema level grants on this role.

Solution

  • One plausible scenario is existence of another future grants that are assigned on schema level to different role. In such situation future grants assigned on the database level are ignored.

    Considerations

    When future grants are defined on the same object type for a database and a schema in the same database, the schema-level grants take precedence over the database level grants, and the database level grants are ignored. This behavior applies to privileges on future objects granted to one role or different roles.

    Reproducible example:

    USE ROLE SYSADMIN;
CREATE OR REPLACE DATABASE DB_PROD;

USE ROLE SECURITYADMIN;
CREATE OR REPLACE ROLE analyst_legacy_test;

grant usage on database db_prod to role analyst_legacy_test;
grant usage on all schemas in database db_prod to role analyst_legacy_test;
grant select on all tables in database db_prod to role analyst_legacy_test;
grant select on all views in database db_prod to role analyst_legacy_test;

grant usage on future schemas in database db_prod to role ANALYST_LEGACY_TEST;
grant select on future tables in database db_prod to role analyst_legacy_test;
grant select on future views in database db_prod to role ANALYST_LEGACY_TEST;

grant role ANALYST_LEGACY_TEST TO USER <user_name_here>;

USE ROLE SYSADMIN;
CREATE OR REPLACE SCHEMA TEST;
CREATE OR REPLACE VIEW TEST_VW AS SELECT 1 AS c;

-- no schema level future grants, the view is accessible
USE ROLE analyst_legacy_test;
SELECT * FROM TEST.TEST_VW;
-- 1

-- adding future grants to different role on schema level
USE ROLE SECURITYADMIN;
grant select on future views in schema DB_PROD.TEST to role sysadmin;

-- TEST_VW still works
USE ROLE analyst_legacy_test;
SELECT * FROM TEST.TEST_VW;
-- 1

-- new view
USE ROLE SYSADMIN;
CREATE OR REPLACE VIEW TEST.TEST_VW_NEW AS SELECT 2 AS c;

USE ROLE analyst_legacy_test;
SELECT * FROM TEST.TEST_VW_NEW;
-- Object 'DB_PROD.TEST.TEST_VW_NEW' does not exist or not authorized.