Why access control protects sensitive data in Snowflake - Why It Works

Introduction

Sensitive data needs protection to prevent unauthorized access. Access control helps by allowing only the right people to see or change this data, keeping it safe from misuse or leaks.

When you want to keep customer information private in your data warehouse

When you need to restrict who can see financial reports in your company

When you want to control which team members can modify sensitive tables

When you want to audit who accessed confidential data

When you want to comply with data privacy laws by limiting data exposure

Commands

This command creates a new role named 'analyst' to group permissions for users who need to access data.

Terminal

CREATE ROLE analyst;

Expected Output

SQL executed successfully.

This grants the 'analyst' role permission to read data from all tables in the 'sales_db' database, allowing controlled access.

Terminal

GRANT SELECT ON ALL TABLES IN DATABASE sales_db TO ROLE analyst;

Expected Output

Grant succeeded.

Creates a user named 'alice' with the 'analyst' role as default, so she can access data as allowed by that role.

Terminal

CREATE USER alice PASSWORD='StrongPass123' DEFAULT_ROLE=analyst MUST_CHANGE_PASSWORD=TRUE;

Expected Output

SQL executed successfully.

Assigns the 'analyst' role to user 'alice', enabling her to use the permissions granted to that role.

Terminal

GRANT ROLE analyst TO USER alice;

Expected Output

Grant succeeded.

Shows all permissions and roles assigned to user 'alice' to verify her access rights.

Terminal

SHOW GRANTS TO USER alice;

Expected Output

grantee_name | granted_role | privilege | granted_on | name

alice        | analyst      | SELECT    | TABLE      | <table_name>

Key Concept

If you remember nothing else from this pattern, remember: Access control limits who can see or change sensitive data, keeping it safe.

Common Mistakes

Granting permissions directly to users instead of roles

It makes managing access harder and less secure as you must update each user individually.

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

Not assigning any role to a user after creating them

The user will have no permissions and cannot access any data.

Assign at least one role to every user to give them proper access.

Using weak passwords or not requiring password changes

Weak passwords increase risk of unauthorized access.

Use strong passwords and require users to change them on first login.

Summary

Create roles to group permissions for easier access management.

Grant permissions to roles, not directly to users.

Assign roles to users to control their access to sensitive data.

Verify user permissions with SHOW GRANTS commands.