Creating custom roles in Snowflake - Step-by-Step CLI Walkthrough

Introduction

Sometimes, the default roles in Snowflake do not fit your exact needs. Creating custom roles lets you control who can do what in your data system, making it safer and easier to manage.

When you want to give a team access to only specific databases or tables.

When you need to separate duties so no one has too much control.

When you want to create a role for a new project with unique permissions.

When you want to limit access to sensitive data for certain users.

When you want to organize permissions clearly for easier management.

Commands

This command creates a new role named 'data_analyst' which you can later assign permissions to.

Terminal

CREATE ROLE data_analyst;

Expected Output

SQL executed successfully.

This grants the 'data_analyst' role permission to read data from all tables in the 'public' schema of the 'sales_db' database.

Terminal

GRANT SELECT ON ALL TABLES IN SCHEMA sales_db.public TO ROLE data_analyst;

Expected Output

SQL executed successfully.

This assigns the 'data_analyst' role to the user named 'alice', giving her the permissions of that role.

Terminal

GRANT ROLE data_analyst TO USER alice;

Expected Output

SQL executed successfully.

This shows all the permissions that have been given to the 'data_analyst' role, so you can verify them.

Terminal

SHOW GRANTS TO ROLE data_analyst;

Expected Output

created_on           | privilege | granted_on | name      | granted_to | grantee_name

---------------------|-----------|------------|-----------|------------|-------------

2024-06-01 12:00:00  | SELECT    | TABLE      | SALES_DB.PUBLIC.*  | ROLE       | DATA_ANALYST

Key Concept

If you remember nothing else from this pattern, remember: custom roles let you control exactly who can access what in your Snowflake account.

Common Mistakes

Trying to grant permissions to a user directly without creating or using a role.

Snowflake manages permissions through roles, so users must have roles assigned to get permissions.

Always create a role, grant permissions to that role, then assign the role to users.

Not verifying the granted permissions after creating a role.

You might think permissions are set correctly but they may be missing or incorrect.

Use SHOW GRANTS TO ROLE to check the permissions assigned to your custom role.

Summary

Create a custom role with CREATE ROLE to group permissions.

Grant specific permissions to the role using GRANT statements.

Assign the role to users to give them those permissions.

Verify permissions with SHOW GRANTS TO ROLE.