Why access control protects sensitive data in Snowflake - Visual Breakdown

Process Flow - Why access control protects sensitive data

User tries to access data

↓

Check user identity

↓

Verify user permissions

↓

Allow access

↓

User sees data

This flow shows how access control checks who the user is and what they are allowed to see before giving access to sensitive data.

Execution Sample

Snowflake

GRANT SELECT ON TABLE customers TO ROLE analyst;
-- Analyst role can read customer data

SELECT * FROM customers;
-- Analyst runs this query

This code grants read access on the customers table to the analyst role, then the analyst queries the data.

Process Table

Step	Action	User Role	Permission Check	Access Result	Data Visible
1	User 'analyst' runs SELECT query	analyst	Check if 'analyst' has SELECT on customers	Permission found	Data rows returned
2	User 'intern' runs SELECT query	intern	Check if 'intern' has SELECT on customers	Permission not found	Access denied, no data
3	User 'admin' runs SELECT query	admin	Check if 'admin' has SELECT on customers	Permission found	Data rows returned
4	User 'analyst' tries to UPDATE customers	analyst	Check if 'analyst' has UPDATE on customers	Permission not found	Access denied, no update
5	User 'admin' tries to UPDATE customers	admin	Check if 'admin' has UPDATE on customers	Permission found	Update allowed

💡 Access control stops users without proper permissions from seeing or changing sensitive data.

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5
User Role	none	analyst	intern	admin	analyst	admin
Permission (SELECT)	none	granted	not granted	granted	granted	granted
Permission (UPDATE)	none	not granted	not granted	granted	not granted	granted
Access Result	none	allowed	denied	allowed	denied	allowed
Data Visible	none	yes	no	yes	no	yes

Key Moments - 3 Insights

Why can't the 'intern' user see the customer data?

Because the 'intern' role does not have SELECT permission on the customers table, as shown in step 2 of the execution table where permission check fails and access is denied.

Why is the 'analyst' allowed to read but not update the data?

The 'analyst' role has SELECT permission but not UPDATE permission on the customers table, so reading is allowed (step 1) but updating is denied (step 4).

How does access control protect sensitive data?

It checks user roles and permissions before allowing data access or changes, blocking unauthorized users as seen in multiple steps where permission is missing and access is denied.

Visual Quiz - 3 Questions

Look at the execution table, what is the access result when 'intern' tries to SELECT from customers?

A Permission found, data returned

B Permission not found, access denied

C Permission found, but no data returned

D Access allowed without permission

💡 Hint

Check step 2 in the execution table where 'intern' role permission is checked.

At which step does the 'analyst' role try to update the customers table and what happens?

A Step 4, update denied

B Step 1, update allowed

C Step 5, update allowed

D Step 2, update denied

💡 Hint

Look at step 4 in the execution table for 'analyst' update attempt.

If the 'intern' role was granted SELECT permission, how would step 2 change?

A User role would change to 'analyst'

B Access result would remain 'Permission not found' and data visible 'no'

C Access result would be 'Permission found' and data visible 'yes'

D Data visible would be 'no' regardless

💡 Hint

Refer to variable_tracker for permission and access result changes.

Concept Snapshot

Access control checks who you are and what you can do.
Permissions like SELECT or UPDATE control data access.
Users without permission are blocked from sensitive data.
Roles group permissions for easier management.
Always grant least privilege needed to protect data.

Full Transcript

Access control protects sensitive data by checking user identity and permissions before allowing access. When a user tries to read or change data, the system verifies if their role has the needed permission. If yes, access is granted and data is visible. If no, access is denied and data remains protected. For example, an analyst role with SELECT permission can read customer data but cannot update it. An intern without SELECT permission cannot see the data at all. This ensures only authorized users can view or modify sensitive information, keeping data safe.

Practice

1. Why is access control important in Snowflake when handling sensitive data?

easy

A. It automatically backs up data to prevent loss.

B. It speeds up data queries by caching results.

C. It limits who can view or change the data, protecting privacy.

D. It converts data into a different format for storage.

Solution

1. Step 1: Understand the purpose of access control

   Access control restricts data access to authorized users only, ensuring privacy and security.

2. Step 2: Identify the role of access control in Snowflake

   Snowflake uses access control to protect sensitive data by limiting who can view or modify it.

3. Final Answer:

   It limits who can view or change the data, protecting privacy. -> Option C

4. Quick Check:

   Access control = Limits data access [OK]

Hint: Access control means who can see or change data [OK]

Common Mistakes:

• Confusing access control with data backup
• Thinking access control changes data format
• Believing access control improves query speed

2. Which Snowflake command is used to give a role permission to access a table?

easy

A. DROP PERMISSION ON TABLE

B. REVOKE ROLE ON TABLE

C. CREATE ROLE ON TABLE

D. GRANT SELECT ON TABLE

Solution

1. Step 1: Identify the command to assign permissions

   In Snowflake, the GRANT command is used to give permissions like SELECT on objects.

2. Step 2: Confirm the correct syntax for granting table access

   GRANT SELECT ON TABLE allows a role to read data from the table.

3. Final Answer:

   GRANT SELECT ON TABLE -> Option D

4. Quick Check:

   Grant = give permission [OK]

Hint: Use GRANT to give access, REVOKE to remove it [OK]

Common Mistakes:

• Using REVOKE to give permissions
• Confusing CREATE ROLE with granting permissions
• Using DROP PERMISSION which is invalid

3. Given this Snowflake SQL snippet:

GRANT SELECT ON TABLE sales TO ROLE analyst;
SHOW GRANTS TO ROLE analyst;

What will the SHOW GRANTS command display?

medium

A. It shows that the analyst role has SELECT permission on the sales table.

B. It shows all users assigned to the analyst role.

C. It shows the data inside the sales table.

D. It shows the creation date of the sales table.

Solution

1. Step 1: Understand the GRANT command effect

   The GRANT command gives the analyst role SELECT permission on the sales table.

2. Step 2: Understand what SHOW GRANTS TO ROLE does

   This command lists all permissions granted to the specified role, here analyst.

3. Final Answer:

   It shows that the analyst role has SELECT permission on the sales table. -> Option A

4. Quick Check:

   SHOW GRANTS = list permissions [OK]

Hint: SHOW GRANTS lists permissions for roles [OK]

Common Mistakes:

• Thinking SHOW GRANTS lists users
• Expecting SHOW GRANTS to show table data
• Confusing SHOW GRANTS with SHOW TABLES

4. A user complains they cannot query a sensitive table even though they belong to a role with SELECT permission. What is the likely cause?

medium

A. The table does not exist in the database.

B. The user was not assigned the role with SELECT permission.

C. The user has too many roles assigned.

D. The SELECT permission was granted to a different table.

Solution

1. Step 1: Check role assignment to user

   Even if a role has permission, the user must be assigned that role to use it.

2. Step 2: Verify the user's roles include the one with SELECT permission

   If the user lacks the role, they cannot query the table despite the role's permissions.

3. Final Answer:

   The user was not assigned the role with SELECT permission. -> Option B

4. Quick Check:

   User needs role assigned to access data [OK]

Hint: User must have the role assigned to use its permissions [OK]

Common Mistakes:

• Assuming permission applies without role assignment
• Blaming table existence without checking roles
• Thinking too many roles cause access denial

5. You want to protect sensitive customer data in Snowflake by allowing only the marketing team to view it. Which approach best follows access control best practices?

hard

A. Create a role named marketing_team, grant SELECT on the customer table to this role, then assign the role to marketing users.

B. Grant SELECT on the customer table directly to each marketing user individually.

C. Create a separate copy of the customer table with limited data and share it with marketing users.

D. Encrypt the customer table and share the encryption key with marketing users.

Solution

1. Step 1: Use roles to manage permissions efficiently

   Assigning permissions to a role and then assigning that role to users simplifies management.

2. Step 2: Grant SELECT permission on the sensitive table to the marketing_team role

   This limits access to only users with that role, protecting data.

3. Final Answer:

   Create a role named marketing_team, grant SELECT on the customer table to this role, then assign the role to marketing users. -> Option A

4. Quick Check:

   Use roles for permission management [OK]

Hint: Grant permissions to roles, assign roles to users [OK]

Common Mistakes:

• Granting permissions directly to users instead of roles
• Sharing copies instead of controlling access
• Relying only on encryption without access control