Can we grant select or insert to a user on table with out creating a role in snowflake?

Question

Can we grant direct select or insert access( with out creating a role ) to a user on a table ?

score 1 · Answer 1 · answered Jun 07 '20 at 11:05

1

No, you cannot. Snowflake uses Role-based Access Control (RBAC):

Therefore, all access on a table should be granted through a role.

Of course you can use "existing roles" instead of "creating a new one".

answered Jun 07 '20 at 11:05

Gokhan Atil

You could also create a role that just has SELECT or INSERT access and then grant whatever those users use for their default role to this new role, and then they'd get everything they had + SELECT or INSERT. Granting roles to roles is a nice way to customize for a subset of users. – Mike Walton Jun 07 '20 at 14:37

score 0 · Answer 2 · answered Apr 25 '22 at 10:10

The short answer is NO - you can only grant access to a ROLE - never directly to a USER.

In Snowflake, everything is accessed via a ROLE. See this diagram:

In summary:

USERS are granted one or more ROLES
A ROLE is granted PRIVILEGES (for example, insert, update, delete) on SECURABLE OBJECTS (for example a TABLE or VIEW)

Even the concept of OWNERSHIP is different in Snowflake. Every USER with the same ROLE shares access to the OBJECTS. This has some unusual results.

For example:

If a USER creates a TABLE - everyone with the same ROLE has OWNERSHIP on the table.

You can read more about Snowflake RBAC on this article - which also links to another two which explain best practices on how to deploy. https://www.analytics.today/blog/introducing-snowflake-rbac

2 Answers2