Granting and revoking privileges in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

What happens after this privilege grant?

You run the command:

GRANT SELECT ON TABLE sales TO ROLE analyst;

What is the immediate effect of this command?

Snowflake

GRANT SELECT ON TABLE sales TO ROLE analyst;

A The role 'analyst' can now read data from the 'sales' table.

B The role 'analyst' can now modify data in the 'sales' table.

C The role 'analyst' can create new tables in the database.

D The role 'analyst' can delete the 'sales' table.

💡 Hint

Think about what the SELECT privilege allows.

The SELECT privilege allows reading data from a table. It does not allow modifying, creating, or deleting tables.

❓ security

intermediate

Which command revokes the privilege to insert data?

You want to remove the ability of the role 'data_entry' to add new rows to the 'customers' table. Which command does this?

A GRANT DELETE ON TABLE customers TO ROLE data_entry;

B REVOKE INSERT ON TABLE customers FROM ROLE data_entry;

C REVOKE SELECT ON TABLE customers FROM ROLE data_entry;

D GRANT UPDATE ON TABLE customers TO ROLE data_entry;

💡 Hint

Look for the command that removes the INSERT privilege.

REVOKE INSERT removes the ability to add new rows. Other options either grant privileges or revoke different ones.

❓ Architecture

advanced

What is the effect of granting a privilege on a schema?

You execute:

GRANT USAGE ON SCHEMA sales_data TO ROLE analyst;

What does this allow the 'analyst' role to do?

A Delete the 'sales_data' schema.

B Create new databases inside the 'sales_data' schema.

C Access objects inside the 'sales_data' schema but not read data from tables.

D Read data from all tables inside the 'sales_data' schema.

💡 Hint

USAGE on a schema allows access but not data reading.

USAGE privilege on a schema allows the role to access objects inside it but does not grant data reading rights. SELECT must be granted separately on tables.

✅ Best Practice

advanced

Which approach follows least privilege principle?

You want to give a user read access to only one table in a large database. Which is the best way?

A Grant USAGE on the database and schema only.

B Grant SELECT on the entire database to the user's role.

C Grant OWNERSHIP of the table to the user's role.

D Grant SELECT on the specific table to the user's role.

💡 Hint

Least privilege means giving only what is needed.

Granting SELECT on just the needed table limits access and follows security best practices. Granting more privileges exposes unnecessary data or control.

🧠 Conceptual

expert

What error occurs if you revoke a privilege not granted?

You run:

REVOKE DELETE ON TABLE orders FROM ROLE sales_team;

But the 'sales_team' role never had DELETE privilege on 'orders'. What happens?

A The command succeeds silently with no error.

B An error is raised: privilege does not exist for role.

C The command revokes all privileges from the role.

D The command grants DELETE privilege instead.

💡 Hint

Think about idempotency of revoke commands.

In Snowflake, revoking a privilege that was never granted does not cause an error. The command succeeds silently.