User Based Access Controls

Snowflake has introduced User-Based Access Controls (UBAC), allowing administrators to grant permissions directly to individual users. This is a shift from the traditional Role-Based Access Control (RBAC) model, where privileges are assigned to roles, which are then granted to users.

Snowflake’s internal mechanism to determine whether or not a user has access to a specific privilege through UBAC is only honored if the session has USE SECONDARY ROLE set to ALL. Fortunately, this is the default setting for all users as of the 2024_08 release bundle.

Wait… Secondary Roles?

The introduction of UBAC has brought to light a feature that is not at all new, but one that many users are unaware of. Secondary roles allow a session to inherit privileges from all roles assigned to a user—not just the active one. In other words, secondary roles enable aggregating all of the grants assigned to all of the roles assigned to the user. That’s a mouthful - right? Let’s look at a quick example:

Suppose my user ATOBIN has two roles:

• ATOBIN_OWNER: Has ownership over SAMPLE_DB and its objects.
  
  
• ATOBIN_USER: Has NO access to SAMPLE_DB.

Next, suppose I set ATOBIN_USER as my active role and run a select statement on a table inside of SAMPLE_DB:

• With USE SECONDARY ROLES ALL - the query succeeds. 
  • This is because Snowflake identifies that ATOBIN_OWNER is granted to my user, which DOES have sufficient privileges 
• With USE SECONDARY ROLES NONE -  the query fails.
  •  This is because Snowflake correctly identifies that my active role, ATOBIN_USER, does NOT have sufficient privileges.

Back to UBAC

There’s not much to implementing UBAC. Users simply grant permissions directly to a user instead of to a role. Below is the difference between granting access to a procedure using both RBAC and UBAC.

RBAC:

CREATE ROLE <ROLE_NAME>;

GRANT USAGE ON PROCEDURE <PROC_NAME> TO ROLE <ROLE_NAME>;

GRANT ROLE <ROLE_NAME> TO USER <USER_NAME>;

UBAC:

GRANT USAGE ON PROCEDURE <PROC_NAME> TO USER <USER_NAME>;

Sure, UBAC saves a couple of lines of code… But does it save you time in the long run?

Why I think RBAC is the right choice

UBAC adds flexibility and an additional option for control over security within an organization—and that’s great. But just because you can grant privileges directly to users doesn’t necessarily mean that you should.

In theory, skipping the creation of a role to give one person access to one object sounds efficient. In practice, it complicates governance. When privileges are assigned through roles, it’s easy for admins to audit access: just look at which users have which roles. With UBAC, you now have to check each user individually to see what direct grants they’ve been given.

Final Thoughts

I fully support Snowflake adding this feature, as I believe it’s important to allow customers to secure their data in whichever way they deem best for their company. But in my view, RBAC remains the cleaner, more scalable approach, especially for organizations concerned with visibility, security, and maintainability.

UBAC may have its use cases, but for most teams, this feels like one of those situations that fits the motto, ‘Just because you can, doesn’t mean you should.’

I’d love to hear how others plan to use UBAC, or if anyone’s found a compelling use case I haven’t considered!