Granting and revoking privileges in Snowflake - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the GRANT command in Snowflake?

The GRANT command is used to give specific privileges to roles or users, allowing them to perform certain actions on database objects.

beginner

How do you revoke privileges in Snowflake?

You use the REVOKE command to remove previously granted privileges from roles or users, restricting their access or actions on database objects.

intermediate

What is a role in Snowflake and how does it relate to privileges?

A role is a collection of privileges. Assigning a role to a user grants them all the privileges that role has. This simplifies managing access rights.

intermediate

Explain the difference between granting privileges to a user directly versus granting them to a role.

Granting privileges to a role allows multiple users to share the same access rights by assigning the role to them. Granting directly to a user is less flexible and harder to manage.

intermediate

What happens if you revoke a privilege from a role that is assigned to multiple users?

All users assigned to that role lose the revoked privilege immediately, since the privilege is tied to the role, not individual users.

Which command is used to give a user permission to query a table in Snowflake?

A GRANT SELECT ON TABLE my_table TO USER my_user

B REVOKE SELECT ON TABLE my_table FROM USER my_user

C CREATE ROLE my_user

D DROP TABLE my_table

The GRANT command with SELECT privilege allows the user to query the table.

What does the REVOKE command do in Snowflake?

A Deletes a user

B Gives privileges to users or roles

C Creates a new role

D Removes privileges from users or roles

REVOKE removes privileges previously granted.

If you want to manage access for many users easily, what should you use?

A Use the REVOKE command on all users

B Grant privileges directly to each user

C Create roles and grant privileges to roles

D Delete all users and recreate them

Roles group privileges and can be assigned to many users for easier management.

Which privilege allows a user to create tables in a database?

A SELECT

B CREATE TABLE

C USAGE

D INSERT

CREATE TABLE privilege allows creating tables.

What happens if you revoke a privilege from a role assigned to multiple users?

A All users with that role lose the privilege

B The privilege remains for all users

C Only one user loses the privilege

D The role is deleted

Revoking from a role affects all users assigned to that role.

Describe how you would grant a user permission to read data from a table in Snowflake.

Think about the command syntax to allow reading data.

Explain why using roles to manage privileges is better than granting privileges directly to users.

Consider how managing many users is simplified.