Why access control protects sensitive data in Snowflake - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to grant read access to a user on a table.

Snowflake

GRANT SELECT ON TABLE sensitive_data TO [1];

Drag options to blanks, or click blank then click option'

AUSER_ANALYST

BDATABASE_ADMIN

CROLE_ANALYST

DWAREHOUSE_OPERATOR

Attempts:

3 left

💡 Hint

Common Mistakes

Using a role name when a user name is required

Granting privileges to the wrong entity

You grant privileges to a user or role. Here, USER_ANALYST is the user receiving SELECT rights.

2fill in blank

medium

Complete the code to revoke write access from a role.

Snowflake

REVOKE INSERT ON TABLE customer_info FROM [1];

Drag options to blanks, or click blank then click option'

AUSER_SALES

BUSER_ADMIN

CROLE_DATA_ENGINEER

DROLE_SALES_TEAM

Attempts:

3 left

💡 Hint

Common Mistakes

Revoking from a user instead of a role

Using a role unrelated to the data

Write permissions are usually granted to roles. Here, ROLE_SALES_TEAM is having INSERT rights revoked.

3fill in blank

hard

Fix the error in the code to correctly grant access to a schema.

Snowflake

GRANT USAGE ON SCHEMA [1] TO ROLE data_analyst;

Drag options to blanks, or click blank then click option'

Asales-data

Bsales_data

Csales.data

DsalesData

Attempts:

3 left

💡 Hint

Common Mistakes

Using invalid characters in schema names

Using camelCase which is allowed but less common

Schema names must be valid identifiers without special characters like dashes or dots. sales_data is valid.

4fill in blank

hard

Fill both blanks to create a role and grant it access to a database.

Snowflake

CREATE ROLE [1];
GRANT USAGE ON DATABASE [2] TO ROLE [1];

Drag options to blanks, or click blank then click option'

Adata_scientist

Banalytics_db

Csales_db

Ddata_engineer

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing role and database names

Using invalid names

The role data_scientist is created and granted usage on the analytics_db database.

5fill in blank

hard

Fill all three blanks to grant a role access to a table with select permission.

Snowflake

GRANT SELECT ON TABLE [1] TO ROLE [2];
GRANT [3] ON SCHEMA public TO ROLE [2];

Drag options to blanks, or click blank then click option'

Acustomer_data

Bdata_analyst

CUSAGE

Dsales_data

Attempts:

3 left

💡 Hint

Common Mistakes

Confusing table and schema names

Using wrong privilege keywords

The data_analyst role is granted SELECT on customer_data table and USAGE on the public schema.

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