Exercise 7.1 — RBAC Model for Utility PII

Design and implement a 3-persona RBAC model on your trial account. Hands-on, end with a working grant tree you could walk an interviewer through.

The scenario

You're on a utility's data platform team. Three personas query the warehouse:

  1. Meter engineer: needs raw meter reads, asset IDs, consumption. Does not need customer names/addresses/billing.
  2. Customer analyst: needs billing addresses, customer names, consumption rolled up per account. Does not need SSN, DOB, credit-check data.
  3. Compliance officer: needs everything, for audit. Read-only. Never grants.

Step 1 — Create sample tables

USE ROLE ACCOUNTADMIN;
USE WAREHOUSE LEARN_WH;
CREATE SCHEMA IF NOT EXISTS LEARN_DB.RBAC_DEMO;
USE SCHEMA LEARN_DB.RBAC_DEMO;

CREATE OR REPLACE TABLE meter_reads (
  meter_id STRING, premise_zip STRING, read_ts TIMESTAMP_NTZ, consumption_kwh NUMBER(10,3)
);
CREATE OR REPLACE TABLE customers (
  customer_id STRING, full_name STRING, billing_address STRING,
  ssn STRING, dob DATE, credit_score NUMBER
);
CREATE OR REPLACE TABLE accounts (
  account_id STRING, customer_id STRING, meter_id STRING, started_at DATE
);
INSERT INTO customers VALUES
  ('C1','Customer One','100 Main St','xxx-xx-1111','1982-01-01',780),
  ('C2','Customer Two','200 Oak Ave','xxx-xx-2222','1977-05-05',810);
INSERT INTO accounts VALUES ('A1','C1','MTR-00001','2020-01-01'),('A2','C2','MTR-00002','2020-01-01');
INSERT INTO meter_reads VALUES
  ('MTR-00001','97124','2026-04-20 10:00:00',3.2),
  ('MTR-00002','97201','2026-04-20 10:00:00',4.1);

Step 2 — Create access roles

USE ROLE SECURITYADMIN;

CREATE ROLE IF NOT EXISTS AR_METERS_READ;
CREATE ROLE IF NOT EXISTS AR_CUSTOMERS_NOPII_READ;   -- customers w/o ssn/dob/credit
CREATE ROLE IF NOT EXISTS AR_CUSTOMERS_FULL_READ;    -- full customer record
CREATE ROLE IF NOT EXISTS AR_ACCOUNTS_READ;

-- Grant DB/schema USAGE first (easy to forget!)
GRANT USAGE ON DATABASE LEARN_DB TO ROLE AR_METERS_READ;
GRANT USAGE ON SCHEMA LEARN_DB.RBAC_DEMO TO ROLE AR_METERS_READ;
GRANT SELECT ON TABLE LEARN_DB.RBAC_DEMO.meter_reads TO ROLE AR_METERS_READ;

GRANT USAGE ON DATABASE LEARN_DB TO ROLE AR_ACCOUNTS_READ;
GRANT USAGE ON SCHEMA LEARN_DB.RBAC_DEMO TO ROLE AR_ACCOUNTS_READ;
GRANT SELECT ON TABLE LEARN_DB.RBAC_DEMO.accounts TO ROLE AR_ACCOUNTS_READ;

-- NO-PII access: grant on a *view* that strips sensitive cols
USE ROLE SYSADMIN;
CREATE OR REPLACE VIEW LEARN_DB.RBAC_DEMO.customers_nopii AS
  SELECT customer_id, full_name, billing_address FROM LEARN_DB.RBAC_DEMO.customers;

USE ROLE SECURITYADMIN;
GRANT USAGE ON DATABASE LEARN_DB TO ROLE AR_CUSTOMERS_NOPII_READ;
GRANT USAGE ON SCHEMA LEARN_DB.RBAC_DEMO TO ROLE AR_CUSTOMERS_NOPII_READ;
GRANT SELECT ON VIEW LEARN_DB.RBAC_DEMO.customers_nopii TO ROLE AR_CUSTOMERS_NOPII_READ;

-- Full customer read (compliance)
GRANT USAGE ON DATABASE LEARN_DB TO ROLE AR_CUSTOMERS_FULL_READ;
GRANT USAGE ON SCHEMA LEARN_DB.RBAC_DEMO TO ROLE AR_CUSTOMERS_FULL_READ;
GRANT SELECT ON TABLE LEARN_DB.RBAC_DEMO.customers TO ROLE AR_CUSTOMERS_FULL_READ;

Step 3 — Functional roles

CREATE ROLE IF NOT EXISTS FR_METER_ENGINEER;
CREATE ROLE IF NOT EXISTS FR_CUSTOMER_ANALYST;
CREATE ROLE IF NOT EXISTS FR_COMPLIANCE;

GRANT ROLE AR_METERS_READ           TO ROLE FR_METER_ENGINEER;
GRANT ROLE AR_ACCOUNTS_READ         TO ROLE FR_METER_ENGINEER;

GRANT ROLE AR_CUSTOMERS_NOPII_READ  TO ROLE FR_CUSTOMER_ANALYST;
GRANT ROLE AR_ACCOUNTS_READ         TO ROLE FR_CUSTOMER_ANALYST;
GRANT ROLE AR_METERS_READ           TO ROLE FR_CUSTOMER_ANALYST;

GRANT ROLE AR_METERS_READ           TO ROLE FR_COMPLIANCE;
GRANT ROLE AR_ACCOUNTS_READ         TO ROLE FR_COMPLIANCE;
GRANT ROLE AR_CUSTOMERS_FULL_READ   TO ROLE FR_COMPLIANCE;

-- Roll up to SYSADMIN for visibility
GRANT ROLE FR_METER_ENGINEER   TO ROLE SYSADMIN;
GRANT ROLE FR_CUSTOMER_ANALYST TO ROLE SYSADMIN;
GRANT ROLE FR_COMPLIANCE       TO ROLE SYSADMIN;

-- Grant yourself (the ACCOUNTADMIN user) all three to test
GRANT ROLE FR_METER_ENGINEER   TO USER CURRENT_USER();
GRANT ROLE FR_CUSTOMER_ANALYST TO USER CURRENT_USER();
GRANT ROLE FR_COMPLIANCE       TO USER CURRENT_USER();

Step 4 — Test as each persona

-- As meter engineer
USE ROLE FR_METER_ENGINEER;
USE WAREHOUSE LEARN_WH;
SELECT * FROM LEARN_DB.RBAC_DEMO.meter_reads;       -- ✓ works
SELECT * FROM LEARN_DB.RBAC_DEMO.customers;         -- ✗ should fail (no privilege)

-- As customer analyst
USE ROLE FR_CUSTOMER_ANALYST;
SELECT * FROM LEARN_DB.RBAC_DEMO.customers_nopii;   -- ✓ works (no PII)
SELECT * FROM LEARN_DB.RBAC_DEMO.customers;         -- ✗ should fail

-- As compliance
USE ROLE FR_COMPLIANCE;
SELECT ssn, credit_score FROM LEARN_DB.RBAC_DEMO.customers; -- ✓ works

Step 5 — Audit your work

USE ROLE SECURITYADMIN;
SHOW GRANTS TO ROLE FR_CUSTOMER_ANALYST;
SHOW GRANTS OF ROLE AR_CUSTOMERS_NOPII_READ;

The first tells you "what can Alice (as an analyst) do?" The second tells you "who all has access to this?"

Step 6 — Cleanup

USE ROLE ACCOUNTADMIN;
DROP SCHEMA IF EXISTS LEARN_DB.RBAC_DEMO CASCADE;
DROP ROLE IF EXISTS FR_METER_ENGINEER;
DROP ROLE IF EXISTS FR_CUSTOMER_ANALYST;
DROP ROLE IF EXISTS FR_COMPLIANCE;
DROP ROLE IF EXISTS AR_METERS_READ;
DROP ROLE IF EXISTS AR_CUSTOMERS_NOPII_READ;
DROP ROLE IF EXISTS AR_CUSTOMERS_FULL_READ;
DROP ROLE IF EXISTS AR_ACCOUNTS_READ;

Reflection — save to notepad

← Module 7 index Module 8 →