[SOLVED] Snowflake Role Creation - Permissions granted by default

Snowflake Role Creation - Permissions granted by default

I've executed the following queries in Snowflake using the ACCOUNTADMIN role:

CREATE DATABASE RESTRICTED_DB_TEST;
CREATE SCHEMA RESTRICTED_DB_TEST.TEST_SCHEMA;
CREATE TABLE RESTRICTED_DB_TEST.TEST_SCHEMA.TEST_TBL (
    col_a INT,
    col_b STRING,
    col_c STRING
);
INSERT INTO RESTRICTED_DB_TEST.TEST_SCHEMA.TEST_TBL (col_a, col_b, col_c)
VALUES
    (1, 'Joe', 'New York'),
    (2, 'Mark', 'Boston'),
    (3, 'Emma', 'Chicago');

I was then able to query the data using ACCOUNTADMIN and using SECURTITYADMIN.

I then did the following (again, using ACCOUNTADMIN):

CREATE ROLE TEST_ROLE;
GRANT ROLE TEST_ROLE TO USER "MY-USER-DETAILS";

And I was somewhat surprised to be able to query the table when using TEST_ROLE. I've ran a SHOW GRANTS TO ROLE TEST_ROLE; and this returned zero rows.

Can someone explain how this new role is able to query the table? I've also had the exact same behaviour when creating a second test role with USERADMIN.

I've also tried creating the DB/schema/table using SYSADMIN but still the table is viewable to a new role.

I can query the table using PUBLIC, but the only grants on PUBLIC seem to be:

VIEW LINEAGE    ACCOUNT
USAGE           COMPUTE_POOL
USAGE           COMPUTE_POOL
USAGE           WAREHOUSE

Solution

Interestingly, it looks like running USE SECONDARY ROLES NONE; may have solved the issue.

It looks like by default, secondary roles is set to ALL, which means that despite running things as TEST_ROLE, snowflake was really accessing the data via ACCOUNTADMIN which was a secondary role.