I have a role "READ_ROLE" in snowflake.

I'm using below two queries to see all the items it has access to in snowflake.They both give same results

SELECT  *
FROM snowflake.account_usage.GRANTS_TO_ROLES 
WHERE GRANTEE_NAME  = 'READ_ROLE';

show grants to role READ_ROLE;

According to the output of this query the role does not have access to a database named "SAMPLE_DB", because it is not present in the output.

But somehow when I use the role "READ_ROLE", it is able to access the database "SAMPLE_DB" for which it should not have access to.

The "READ_ROLE" does not have any other role assigned to it.

What can be the reason that this role is able to access a db which it is not granted access to. If it is possible how can I check what all possible items a role can access

I have a role "READ_ROLE" in snowflake.

I'm using below two queries to see all the items it has access to in snowflake.They both give same results

SELECT  *
FROM snowflake.account_usage.GRANTS_TO_ROLES 
WHERE GRANTEE_NAME  = 'READ_ROLE';

show grants to role READ_ROLE;

According to the output of this query the role does not have access to a database named "SAMPLE_DB", because it is not present in the output.

But somehow when I use the role "READ_ROLE", it is able to access the database "SAMPLE_DB" for which it should not have access to.

The "READ_ROLE" does not have any other role assigned to it.

What can be the reason that this role is able to access a db which it is not granted access to. If it is possible how can I check what all possible items a role can access

• snowflake-cloud-data-platform

• Hi - what are the grants to the SAMPLE_DB? – NickW Commented Feb 14 at 7:59
• Hi- the grants on the SAMPLE_DB is also not containing any privileges to the READ_ROLE – curry_b Commented Feb 14 at 10:37
• Hi - please update your question with the output of "show grants on database sample_db;", as requested, so that people have at least some information to start the process of trying to help you – NickW Commented Feb 14 at 11:06

1 Answer 1

Can you run the following command?

show grants on database sample_db;

I expect that PUBLIC role is granted to access the SAMPLE_DB database. It's a pseudo-role that is automatically granted to every user and every role in your account. So if it has permission to access the database, your new role (READ_ROLE) will also access the database.

Addition: The issue was related to secondary roles and BCR. After trying with "USE SECONDARY ROLES NONE", the problem is solved.