Granting and revoking privileges in Snowflake - Commands & Configuration

Introduction

Sometimes you want to let someone use parts of your data or tools, and other times you want to take that permission away. Granting and revoking privileges in Snowflake lets you control who can do what with your data and resources.

When you want to allow a new team member to read data from a specific database.

When you need to let an analyst create tables in a shared schema.

When you want to stop a user from modifying data after their project ends.

When you want to give a role permission to run queries on a warehouse.

When you need to remove access from a user who no longer needs it.

Commands

This command gives the role named 'analyst_role' permission to read data from the database 'my_database'.

Terminal

GRANT SELECT ON DATABASE my_database TO ROLE analyst_role;

Expected Output

SQL executed successfully.

This command shows all the permissions currently given to the 'analyst_role' so you can verify the grant.

Terminal

SHOW GRANTS TO ROLE analyst_role;

Expected Output

created_on           | privilege | granted_on | name        | granted_to | grantee_name  

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

2024-06-01 12:00:00  | SELECT    | DATABASE   | MY_DATABASE | ROLE       | ANALYST_ROLE

This command removes the permission to read data from 'my_database' for the role 'analyst_role'.

Terminal

REVOKE SELECT ON DATABASE my_database FROM ROLE analyst_role;

Expected Output

SQL executed successfully.

This command checks again to confirm that the 'SELECT' permission was removed from 'analyst_role'.

Terminal

SHOW GRANTS TO ROLE analyst_role;

Expected Output

No grants found.

Key Concept

If you remember nothing else from this pattern, remember: granting gives permission to use resources, and revoking takes it away.

Common Mistakes

Trying to grant privileges to a user instead of a role.

Snowflake manages permissions through roles, not directly to users, so the grant will fail or not work as expected.

Always grant privileges to roles, then assign roles to users.

Revoking privileges without checking current grants.

You might revoke a privilege that was never granted or miss revoking inherited privileges, causing confusion.

Use SHOW GRANTS to verify current permissions before revoking.

Granting privileges on the wrong object level (e.g., table instead of database).

Permissions might not apply as intended, leading to unexpected access or denial.

Be clear about the object you want to grant or revoke privileges on and use the correct syntax.

Summary

Use GRANT to give roles permission to access or modify Snowflake resources.

Use SHOW GRANTS to check what permissions a role currently has.

Use REVOKE to remove permissions from roles when access is no longer needed.