Skip to main content

RBAC Modeling Lab

Welcome to the RBAC (Role-Based Access Control) Modeling Lab. In this lab, we will design and implement a functional RBAC hierarchy for a mock organization.

A well-designed RBAC model is the foundation of data security in Snowflake. Rather than granting privileges directly to users, privileges are granted to roles, and roles are granted to users (and other roles) in a structured hierarchy.

The Scenario​

We need to implement security for the Marketing and Engineering teams.

  • Marketing Analysts need to SELECT from the Marketing data.
  • Data Engineers need full CRUD (SELECT, INSERT, UPDATE, DELETE) access to both Marketing and Engineering data.
  • SysAdmins need to oversee everything and inherit all custom roles to ensure no orphaned objects are created.

Hands-On Lab: Implementing the Model​

1. Database & Schema Setup​

Copy and paste the following code into your Snowflake worksheet.

USE ROLE SYSADMIN;

-- Create our training database
CREATE DATABASE IF NOT EXISTS BEEMS_TRAINING_DB;
USE DATABASE BEEMS_TRAINING_DB;

-- Create schemas for the different teams
CREATE SCHEMA IF NOT EXISTS MARKETING_SCHEMA;
CREATE SCHEMA IF NOT EXISTS ENGINEERING_SCHEMA;

-- Create some sample tables
CREATE OR REPLACE TABLE MARKETING_SCHEMA.CAMPAIGN_METRICS (
CAMPAIGN_ID INT,
CLICKS INT,
SPEND NUMBER(10,2)
);

CREATE OR REPLACE TABLE ENGINEERING_SCHEMA.SYSTEM_LOGS (
LOG_ID INT,
ERROR_MSG STRING,
SEVERITY STRING
);

-- Insert sample data
INSERT INTO MARKETING_SCHEMA.CAMPAIGN_METRICS VALUES (1, 1500, 500.00), (2, 3200, 1200.00);
INSERT INTO ENGINEERING_SCHEMA.SYSTEM_LOGS VALUES (101, 'Memory limit exceeded', 'HIGH');

2. Creating the Role Hierarchy​

To implement RBAC correctly, we use the SECURITYADMIN role (or USERADMIN) to create roles and the role hierarchy.

USE ROLE SECURITYADMIN;

-- 1. Create Functional Roles (representing job functions)
CREATE OR REPLACE ROLE MARKETING_ANALYST_ROLE;
CREATE OR REPLACE ROLE DATA_ENGINEER_ROLE;

-- 2. Build the Hierarchy
-- Data Engineers should inherit Marketing Analyst privileges (Engineers can do what Analysts do, and more)
GRANT ROLE MARKETING_ANALYST_ROLE TO ROLE DATA_ENGINEER_ROLE;

-- ALL custom functional roles should ultimately roll up to SYSADMIN
GRANT ROLE DATA_ENGINEER_ROLE TO ROLE SYSADMIN;

3. Applying Grants to the Roles​

Now we grant access to the database, schemas, and objects.

-- Grant USAGE on the database to both roles (since Engineer inherits Analyst, we just need to grant to Analyst where applicable)
GRANT USAGE ON DATABASE BEEMS_TRAINING_DB TO ROLE MARKETING_ANALYST_ROLE;
GRANT USAGE ON DATABASE BEEMS_TRAINING_DB TO ROLE DATA_ENGINEER_ROLE;

-- Grant Schema Access
GRANT USAGE ON SCHEMA BEEMS_TRAINING_DB.MARKETING_SCHEMA TO ROLE MARKETING_ANALYST_ROLE;
GRANT USAGE ON SCHEMA BEEMS_TRAINING_DB.ENGINEERING_SCHEMA TO ROLE DATA_ENGINEER_ROLE;

-- Grant Object-Level Privileges
-- Marketing Analyst gets read-only access to Marketing
GRANT SELECT ON TABLE BEEMS_TRAINING_DB.MARKETING_SCHEMA.CAMPAIGN_METRICS TO ROLE MARKETING_ANALYST_ROLE;

-- Data Engineer gets full DML access to Engineering
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE BEEMS_TRAINING_DB.ENGINEERING_SCHEMA.SYSTEM_LOGS TO ROLE DATA_ENGINEER_ROLE;

-- Data Engineer gets full DML access to Marketing (they need explicitly granted write access, even though they inherit read access)
GRANT INSERT, UPDATE, DELETE ON TABLE BEEMS_TRAINING_DB.MARKETING_SCHEMA.CAMPAIGN_METRICS TO ROLE DATA_ENGINEER_ROLE;

4. Testing Privilege Isolation​

Now, let's verify that our RBAC model works as expected. We will impersonate the roles to ensure they only have the access we intended.

-- Test as Marketing Analyst
USE ROLE MARKETING_ANALYST_ROLE;

-- This should SUCCEED
SELECT * FROM BEEMS_TRAINING_DB.MARKETING_SCHEMA.CAMPAIGN_METRICS;

-- This should FAIL (Analysts shouldn't delete data)
-- DELETE FROM BEEMS_TRAINING_DB.MARKETING_SCHEMA.CAMPAIGN_METRICS WHERE CAMPAIGN_ID = 1;

-- This should FAIL (Analysts can't see Engineering data)
-- SELECT * FROM BEEMS_TRAINING_DB.ENGINEERING_SCHEMA.SYSTEM_LOGS;


-- Test as Data Engineer
USE ROLE DATA_ENGINEER_ROLE;

-- This should SUCCEED (Inherited from Marketing Analyst)
SELECT * FROM BEEMS_TRAINING_DB.MARKETING_SCHEMA.CAMPAIGN_METRICS;

-- This should SUCCEED (Directly granted to Data Engineer)
SELECT * FROM BEEMS_TRAINING_DB.ENGINEERING_SCHEMA.SYSTEM_LOGS;

-- This should SUCCEED (Engineers have write access)
INSERT INTO BEEMS_TRAINING_DB.MARKETING_SCHEMA.CAMPAIGN_METRICS VALUES (3, 500, 100.00);

Common Mistakes in RBAC Models​

  1. Orphaned Roles: Forgetting to grant top-level custom roles to SYSADMIN. If you don't do this, SYSADMIN won't be able to manage the objects created by those roles.
  2. Granting Privileges to Users: You should almost never use GRANT SELECT ON TABLE... TO USER john_doe. Always grant to a role, then grant the role to the user.
  3. Flat Hierarchies: Not using inheritance. If Engineers need all Marketing privileges, use GRANT ROLE MARKETING_ANALYST_ROLE TO ROLE DATA_ENGINEER_ROLE rather than duplicating all the table grants manually.

Summary​

In this lab, you successfully built a functional role hierarchy. You separated the duties between SYSADMIN (who creates objects) and SECURITYADMIN (who manages roles and grants), and verified that the privilege isolation works correctly across your organization's teams.