How to Revoke Privilege in Snowflake: Syntax and Examples
To revoke a privilege in Snowflake, use the
REVOKE command followed by the privilege name, the object type, and the role or user from which you want to remove the privilege. For example, REVOKE SELECT ON TABLE my_table FROM ROLE analyst; removes the SELECT privilege on my_table from the analyst role.Syntax
The REVOKE command removes privileges previously granted to roles or users. It follows this pattern:
- REVOKE: The command to remove privileges.
- privilege: The specific permission like SELECT, INSERT, USAGE.
- ON object_type object_name: Specifies the object type (TABLE, VIEW, SCHEMA, etc.) and its name.
- FROM role_or_user: The role or user losing the privilege.
sql
REVOKE privilege ON object_type object_name FROM role_or_user;
Example
This example shows how to revoke the SELECT privilege on a table named sales_data from a role called data_analyst. After running this, the data_analyst role will no longer be able to query the sales_data table.
sql
REVOKE SELECT ON TABLE sales_data FROM ROLE data_analyst;
Output
Statement executed successfully.
Common Pitfalls
Common mistakes when revoking privileges include:
- Trying to revoke a privilege that was never granted, which has no effect but may confuse users.
- Using incorrect object types or names, causing errors.
- Revoking privileges from the wrong role or user.
- Not having the necessary admin rights to revoke privileges.
Always verify the current grants with SHOW GRANTS ON object_name; before revoking.
sql
/* Wrong: Revoking privilege from a user instead of a role if the privilege was granted to a role */ REVOKE SELECT ON TABLE sales_data FROM USER data_analyst; /* Correct: Revoke from the role */ REVOKE SELECT ON TABLE sales_data FROM ROLE data_analyst;
Quick Reference
| Command Part | Description | Example |
|---|---|---|
| REVOKE | Command to remove privileges | REVOKE SELECT ... |
| privilege | Permission to remove (e.g., SELECT, INSERT) | SELECT |
| ON object_type object_name | Object type and name (TABLE, VIEW, etc.) | ON TABLE sales_data |
| FROM role_or_user | Role or user losing the privilege | FROM ROLE data_analyst |
Key Takeaways
Use the REVOKE command to remove privileges from roles or users in Snowflake.
Specify the exact privilege, object type, object name, and role or user in the command.
Check existing grants before revoking to avoid mistakes.
Revoking a privilege that was never granted does not cause errors but has no effect.
Ensure you have the necessary admin rights to revoke privileges.
