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 ?

Answer 1

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

https://docs.snowflake.com/en/user-guide/security-access-control-overview.html#access-control-framework

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

https://docs.snowflake.com/en/sql-reference/ddl-user-security.html#access-control-management

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

Answer 2

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:

RBAC: USERS, ROLES and SECURABLE OBJECTS

From this article: https://www.analytics.today/blog/introducing-snowflake-rbac

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