Granting and revoking privileges in Snowflake - Mini Project: Build & Apply

Granting and Revoking Privileges in Snowflake

📖 Scenario: You are managing access control in a Snowflake data warehouse for a small team. You need to grant and revoke privileges on a database to control who can read and modify data.

🎯 Goal: Learn how to grant and revoke privileges on a Snowflake database to users and roles.

📋 What You'll Learn

Create a role named analyst_role

Create a user named analyst_user

Grant USAGE privilege on the database sales_db to analyst_role

Grant SELECT privilege on all tables in sales_db schema public to analyst_role

Grant the role analyst_role to the user analyst_user

Revoke the SELECT privilege on all tables in sales_db.public from analyst_role

💡 Why This Matters

🌍 Real World

Managing access control in Snowflake ensures data security and proper permissions for team members.

💼 Career

Cloud engineers and data administrators often manage roles and privileges to enforce security policies.

1

Create a role and a user

Write SQL commands to create a role called analyst_role and a user called analyst_user.

Snowflake

-- Your code here to create role and user

Need a hint?

Use CREATE ROLE and CREATE USER statements with exact names.

2

Grant USAGE privilege on the database

Write a SQL command to grant the USAGE privilege on the database sales_db to the role analyst_role.

Snowflake

CREATE ROLE analyst_role;
CREATE USER analyst_user PASSWORD = 'StrongPass123!';
-- Your code here to grant USAGE privilege

Need a hint?

Use GRANT USAGE ON DATABASE sales_db TO ROLE analyst_role;

3

Grant SELECT privilege on all tables in schema

Write a SQL command to grant the SELECT privilege on all tables in the schema public of database sales_db to the role analyst_role.

Snowflake

CREATE ROLE analyst_role;
CREATE USER analyst_user PASSWORD = 'StrongPass123!';
GRANT USAGE ON DATABASE sales_db TO ROLE analyst_role;
-- Your code here to grant SELECT privilege on all tables

Need a hint?

Use GRANT SELECT ON ALL TABLES IN SCHEMA sales_db.public TO ROLE analyst_role;

4

Grant role to user and revoke SELECT privilege

Write SQL commands to grant the role analyst_role to the user analyst_user and then revoke the SELECT privilege on all tables in sales_db.public from analyst_role.

Snowflake

CREATE ROLE analyst_role;
CREATE USER analyst_user PASSWORD = 'StrongPass123!';
GRANT USAGE ON DATABASE sales_db TO ROLE analyst_role;
GRANT SELECT ON ALL TABLES IN SCHEMA sales_db.public TO ROLE analyst_role;
-- Your code here to grant role to user and revoke SELECT privilege

Need a hint?

Use GRANT ROLE analyst_role TO USER analyst_user; and REVOKE SELECT ON ALL TABLES IN SCHEMA sales_db.public FROM ROLE analyst_role;