Granting and revoking privileges in Snowflake - Step-by-Step Execution

Process Flow - Granting and revoking privileges

Start

↓

Identify Role/User

↓

Choose Privilege

↓

GRANT Privilege

↓

Privilege Active?

No→Error or Retry

Yes↓

REVOKE Privilege

↓

Privilege Removed?

No→Error or Retry

Yes↓

End

The flow shows granting a privilege to a role or user, checking it is active, then revoking it and confirming removal.

Execution Sample

Snowflake

GRANT SELECT ON TABLE sales TO ROLE analyst;
REVOKE SELECT ON TABLE sales FROM ROLE analyst;

This code grants SELECT permission on the sales table to the analyst role, then revokes it.

Process Table

Step	Command	Target	Privilege	Result
1	GRANT SELECT ON TABLE sales TO ROLE analyst	ROLE analyst	SELECT	Privilege granted
2	Check if analyst can SELECT from sales	ROLE analyst	SELECT	Access allowed
3	REVOKE SELECT ON TABLE sales FROM ROLE analyst	ROLE analyst	SELECT	Privilege revoked
4	Check if analyst can SELECT from sales	ROLE analyst	SELECT	Access denied

💡 Privilege revoked, analyst no longer has SELECT access on sales table

Status Tracker

Variable	Start	After Step 1	After Step 3	Final
analyst_role_privileges	none	SELECT on sales	none	none

Key Moments - 2 Insights

Why does the analyst role lose access after revoking?

Because the REVOKE command removes the SELECT privilege granted earlier, as shown in execution_table step 3 and 4.

Can privileges be granted to users directly instead of roles?

Yes, privileges can be granted to users or roles. This example uses a role for best practice, as seen in execution_table step 1.

Visual Quiz - 3 Questions

Look at the execution table, what is the analyst role's privilege after step 1?

A SELECT on sales

B No privileges

C INSERT on sales

D ALL privileges

💡 Hint

Check the 'Result' column in step 1 and variable_tracker after step 1

At which step does the analyst lose SELECT access?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Result' column for access checks in steps 2 and 4

If we grant INSERT instead of SELECT in step 1, what changes in the execution table?

A Privilege column in step 1 and 3 changes to INSERT

B No change, still SELECT

C Result changes to 'Privilege denied'

D Target changes to user instead of role

💡 Hint

Focus on the 'Privilege' column in steps 1 and 3

Concept Snapshot

GRANT privilege ON object TO role_or_user;
REVOKE privilege ON object FROM role_or_user;
Privileges control access rights.
Grant adds rights; revoke removes them.
Always check access after changes.

Full Transcript

This visual execution shows how to grant and revoke privileges in Snowflake. First, a SELECT privilege is granted on the sales table to the analyst role. This allows the analyst to access the table. Then, the privilege is revoked, removing access. The execution table tracks each step, showing commands, targets, privileges, and results. The variable tracker shows the analyst role's privileges changing from none to SELECT and back to none. Key moments clarify why access changes and the difference between granting to roles or users. The quiz tests understanding of privilege states and effects of commands. The snapshot summarizes the syntax and behavior of granting and revoking privileges.

Practice

1. What is the main purpose of granting privileges in Snowflake?

easy

A. To allow roles to perform specific actions on database objects

B. To create new users in the system

C. To delete roles from the account

D. To backup data automatically

Solution

1. Step 1: Understand the role of privileges

   Privileges control what actions roles can perform on database objects like tables or views.

2. Step 2: Identify the purpose of granting privileges

   Granting privileges gives roles permission to do specific tasks, such as SELECT or INSERT on tables.

3. Final Answer:

   To allow roles to perform specific actions on database objects -> Option A

4. Quick Check:

   Grant privileges = Allow role actions [OK]

Hint: Privileges let roles do tasks on objects, not users directly [OK]

Common Mistakes:

• Confusing privileges with user creation
• Thinking privileges delete roles
• Assuming privileges backup data

2. Which of the following is the correct syntax to grant SELECT privilege on a table named sales_data to a role named analyst_role?

easy

A. GRANT SELECT TO ROLE analyst_role ON sales_data;

B. GRANT ROLE analyst_role TO SELECT ON TABLE sales_data;

C. GRANT SELECT ON ROLE analyst_role TO TABLE sales_data;

D. GRANT SELECT ON TABLE sales_data TO ROLE analyst_role;

Solution

1. Step 1: Recall the correct GRANT syntax

   The syntax is: GRANT <privilege> ON <object_type> <object_name> TO ROLE <role_name>;

2. Step 2: Match the syntax to the options

   GRANT SELECT ON TABLE sales_data TO ROLE analyst_role; matches the correct order and keywords exactly.

3. Final Answer:

   GRANT SELECT ON TABLE sales_data TO ROLE analyst_role; -> Option D

4. Quick Check:

   GRANT privilege ON object TO ROLE [OK]

Hint: Remember: GRANT privilege ON object TO ROLE role_name [OK]

Common Mistakes:

• Mixing order of keywords
• Placing role before privilege
• Using TO TABLE instead of TO ROLE

3. Given the following commands executed in order:

GRANT SELECT ON TABLE customers TO ROLE sales_role;
REVOKE SELECT ON TABLE customers FROM ROLE sales_role;
GRANT INSERT ON TABLE customers TO ROLE sales_role;

What privileges does sales_role have on the customers table after these commands?

medium

A. Only SELECT

B. Only INSERT

C. SELECT and INSERT

D. No privileges

Solution

1. Step 1: Analyze the first GRANT

   The role sales_role is granted SELECT privilege on customers.

2. Step 2: Analyze the REVOKE

   The SELECT privilege is revoked from sales_role, removing that permission.

3. Step 3: Analyze the second GRANT

   The role is granted INSERT privilege on customers.

4. Final Answer:

   Only INSERT -> Option B

5. Quick Check:

   Revoked SELECT, granted INSERT = INSERT only [OK]

Hint: Revoke removes privilege; later grants add new ones [OK]

Common Mistakes:

• Assuming revoked privileges remain
• Thinking grants accumulate without revokes
• Confusing privilege types

4. You run the command:

REVOKE ALL PRIVILEGES ON DATABASE sales_db FROM ROLE marketing_role;

But the privileges are not removed. What is the most likely reason?

medium

A. The role marketing_role does not have any privileges on sales_db

B. The syntax REVOKE ALL PRIVILEGES is invalid in Snowflake

C. You must revoke privileges from users, not roles

D. The database sales_db does not exist

Solution

1. Step 1: Check if the role has privileges

   If the role has no privileges on the database, revoking does nothing.

2. Step 2: Verify syntax and object existence

   REVOKE ALL PRIVILEGES is valid syntax, and revoking is done on roles, not users. Assume sales_db exists.

3. Final Answer:

   The role marketing_role does not have any privileges on sales_db -> Option A

4. Quick Check:

   Revoking non-existent privileges has no effect [OK]

Hint: Check if role actually has privileges before revoking [OK]

Common Mistakes:

• Assuming syntax error with REVOKE ALL PRIVILEGES
• Trying to revoke from users directly
• Ignoring role privilege state

5. You want to allow a role data_scientist to both read and write data in tables of the analytics schema but prevent it from dropping any tables. Which set of commands correctly grants and restricts privileges?

hard

A. GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA analytics TO ROLE data_scientist;
GRANT DROP ON SCHEMA analytics TO ROLE data_scientist;

B. GRANT ALL PRIVILEGES ON SCHEMA analytics TO ROLE data_scientist;
REVOKE DROP ON SCHEMA analytics FROM ROLE data_scientist;

C. GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA analytics TO ROLE data_scientist;
REVOKE DROP ON SCHEMA analytics FROM ROLE data_scientist;

D. GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA analytics TO ROLE data_scientist;

Solution

1. Step 1: Grant read and write privileges

   Grant SELECT, INSERT, and UPDATE to allow reading and writing data.

2. Step 2: Revoke DROP privilege

   Explicitly revoke DROP to prevent deleting tables.

3. Step 3: Evaluate other options

   GRANT ALL PRIVILEGES ON SCHEMA analytics TO ROLE data_scientist; REVOKE DROP ON SCHEMA analytics FROM ROLE data_scientist; grants ALL privileges then revokes DROP, which is valid but less precise. GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA analytics TO ROLE data_scientist; does not revoke DROP, so drop is allowed. GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA analytics TO ROLE data_scientist; GRANT DROP ON SCHEMA analytics TO ROLE data_scientist; grants DROP, which is opposite of requirement.

4. Final Answer:

   GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA analytics TO ROLE data_scientist; REVOKE DROP ON SCHEMA analytics FROM ROLE data_scientist; -> Option C

5. Quick Check:

   Grant needed privileges, revoke unwanted ones [OK]

Hint: Grant needed privileges, then revoke unwanted ones explicitly [OK]

Common Mistakes:

• Granting ALL privileges without revoking DROP
• Not revoking DROP privilege
• Granting DROP privilege by mistake