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.