Granting and revoking privileges in Snowflake - Time & Space Complexity

Time Complexity: Granting and revoking privileges

O(n)

Understanding Time Complexity

When managing access in Snowflake, granting and revoking privileges controls who can do what.

We want to understand how the time to apply these changes grows as we handle more users or roles.

Scenario Under Consideration

Analyze the time complexity of the following operation sequence.

GRANT SELECT ON TABLE sales TO ROLE analyst;
GRANT INSERT ON TABLE sales TO ROLE analyst;
REVOKE SELECT ON TABLE sales FROM ROLE analyst;
GRANT UPDATE ON TABLE sales TO ROLE manager;
REVOKE INSERT ON TABLE sales FROM ROLE analyst;

This sequence grants and revokes specific privileges on a table to different roles.

Identify Repeating Operations

• Primary operation: Executing a grant or revoke command on a privilege.
• How many times: Once per grant or revoke statement in the sequence.

How Execution Grows With Input

Each grant or revoke command runs independently, so the total time grows as we add more commands.

Input Size (n)	Approx. API Calls/Operations
10	10 grant/revoke commands
100	100 grant/revoke commands
1000	1000 grant/revoke commands

Pattern observation: The time grows directly with the number of grant or revoke commands.

Final Time Complexity

Time Complexity: O(n)

This means the time to apply privileges grows in a straight line as you add more grant or revoke commands.

Common Mistake

[X] Wrong: "Granting multiple privileges at once takes the same time as granting one privilege."

[OK] Correct: Each privilege requires a separate operation, so more privileges mean more time.

Interview Connect

Understanding how privilege changes scale helps you design efficient access control in real projects.

Self-Check

"What if we batch multiple privileges in a single grant command? How would the time complexity change?"