Creating custom roles in Snowflake - Try It Yourself

Creating Custom Roles in Snowflake

📖 Scenario: You are a Snowflake administrator for a company. You need to create a custom role to manage access for the marketing team. This role will have specific privileges to query certain databases but not full admin rights.

🎯 Goal: Build a Snowflake role named MARKETING_ROLE with select privileges on the MARKETING_DB database schema.

📋 What You'll Learn

Create a role named MARKETING_ROLE

Grant usage on the MARKETING_DB database to MARKETING_ROLE

Grant usage privilege on the MARKETING_SCHEMA schema inside MARKETING_DB to MARKETING_ROLE

Grant select privilege on all tables inside MARKETING_SCHEMA to MARKETING_ROLE

💡 Why This Matters

🌍 Real World

Companies use custom roles in Snowflake to control who can access what data securely and efficiently.

💼 Career

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

1

Create the custom role

Write a SQL statement to create a role named MARKETING_ROLE.

Snowflake

-- Your code here to create the role MARKETING_ROLE

Need a hint?

Use the CREATE ROLE command followed by the role name.

2

Grant usage on the database

Write a SQL statement to grant usage on the database MARKETING_DB to the role MARKETING_ROLE.

Snowflake

CREATE ROLE MARKETING_ROLE;
-- Your code here to grant usage on MARKETING_DB to MARKETING_ROLE

Need a hint?

Use GRANT USAGE ON DATABASE followed by the database name and TO ROLE with the role name.

3

Grant usage on the schema and select on tables

Write SQL statements to grant usage privilege on the schema MARKETING_SCHEMA inside MARKETING_DB and select privilege on all tables in that schema to the role MARKETING_ROLE.

Snowflake

CREATE ROLE MARKETING_ROLE;
GRANT USAGE ON DATABASE MARKETING_DB TO ROLE MARKETING_ROLE;
-- Your code here to grant usage on MARKETING_DB.MARKETING_SCHEMA and SELECT on all tables in MARKETING_DB.MARKETING_SCHEMA to MARKETING_ROLE

Need a hint?

First grant usage on the schema, then grant select on all tables in that schema.

4

Complete role setup with future grants

Write a SQL statement to grant select privilege on all future tables in MARKETING_SCHEMA inside MARKETING_DB to the role MARKETING_ROLE.

Snowflake

CREATE ROLE MARKETING_ROLE;
GRANT USAGE ON DATABASE MARKETING_DB TO ROLE MARKETING_ROLE;
GRANT USAGE ON SCHEMA MARKETING_DB.MARKETING_SCHEMA TO ROLE MARKETING_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA MARKETING_DB.MARKETING_SCHEMA TO ROLE MARKETING_ROLE;
-- Your code here to grant select on future tables in MARKETING_SCHEMA to MARKETING_ROLE

Need a hint?

Use GRANT SELECT ON FUTURE TABLES IN SCHEMA to cover tables created later.