Custom Roles

1. Custom Roles and Secondary Roles

In this video we're going deeper into how custom roles are designed in practice - the difference between functional and access roles, how to create them, when to use database roles versus account roles, and how secondary roles work.

2. The Problem with Flat Role Scenarios

Imagine Claro has three analysts: Ana, Ben, and Carlos. Each needs SELECT on credit scores, USAGE on the core schema, and a handful of other privileges. Managed user by user, you're granting the same privileges three times. When a new table gets added, you update three users. This doesn't scale. The solution is a deliberate role hierarchy.

3. Access Roles vs Functional Roles

Both layers use the same Snowflake object, created with CREATE ROLE -- this is a design pattern, not a different feature. There is no technical difference between an access role and a functional role in Snowflake; the distinction is in how they're used logically. Access roles sit at the bottom: each holds specific privileges on specific objects. CREDIT_READ holds SELECT on Claro's credit scores table. Functional roles sit above and inherit from access roles. ANALYST might inherit CREDIT_READ while DATA_ENGINEER might inherit both CREDIT_READ and PAYMENTS_WRITE.

4. How It Works

Access roles hold specific privileges. Functional roles inherit from one or more access roles. Users are assigned functional roles only. They never touch access roles directly. When Claro adds a new table, you update CREDIT_READ once. Every functional role that inherits it, and every user assigned to those roles, gets the access automatically.

5. Building the Hierarchy in SQL

First, create the access role and grant it the full privilege chain: USAGE on the database, USAGE on the schema, and SELECT on the credit scores table. All three are required -- missing either USAGE grant means the role can't reach the table. Then create the functional role ANALYST and grant it the CREDIT_READ role using GRANT ROLE. One role granted to another builds the hierarchy. Finally, grant ANALYST to Ana.

6. Account-roles vs Database-roles

Account roles exist at the account level and can span any database or schema. Database roles are scoped to a single database -- they can only hold privileges on objects within that database and can't be assigned directly to users. Instead they get granted to an account role. This lets database owners independently manage access to their own database objects without needing account-level role changes.

7. Secondary Roles

Every session has a primary role -- set with USE ROLE -- which controls object ownership and creation context. By default, secondary roles are set to ALL: every other role assigned to you is automatically active alongside the primary. Administrators can change this default at the account level. If you need a specific subset, USE SECONDARY ROLES followed by the role name activates just those. USE SECONDARY ROLES NONE restricts the session to the primary role only. One important limit regardless of secondary role setting: any object you create is always owned by the primary role.

8. Future Grants

When a new table is added to Claro's core schema, Future Grants mean you don't have to remember to grant access manually. GRANT SELECT ON FUTURE TABLES in a schema means any new table created there automatically inherits the grant, closing the gap where new objects are visible to admins but invisible to everyone else.

9. Let's practice!

Great work, we’ve dived deeper into custom and secondary roles. Let’s practice.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.