Lab 08 · Domain 2 · You are Priya
Read every Teach first section before you copy SQL.
RBAC decides if you can query a table. Governance decides which cells and rows you see.
Needs Lab 00 customers + Lab 06 FR_ANALYST / AR_LAB_READ.
SECURE.CUSTOMERS_PII from LAB.CUSTOMERS, masking policy on EMAIL,
row access policy on COUNTRY, object tags for cost/class.
Proof: lab admin sees all · FR_ANALYST + SECONDARY ROLES NONE sees masked US rows.
Episode 08 · Start here
Masking = curtain over a column. Row access = velvet rope at the door — some rows never enter Kai’s result set.
SameSELECT *, different movie depending on my role. Policies useCURRENT_ROLE()— primary role only.
Column problem → masking · Row problem → RAP · Don’t swap them on the exam.
You get the row, but sensitive cells rewrite (e.g. ****@example.com).
Entire rows disappear from the result. Analyst never sees CA/UK customers.
Metadata stickers for cost chargeback and classification — not a substitute for masking.
TAG_DATA_CLASS=PIITAG_COST_CENTER=…Classification suggests PII tags. Lineage shows upstream/downstream impact. Exam awareness.
Before Step 1
Can’t we just mask LAB.CUSTOMERS? Why invent SECURE?
Practice rooms stay messy. Locked room = where policies live. Exam story: put sensitive governed objects in a dedicated schema.
What this does: Copies Lab 00 customers into the locked schema so we can attach policies without surprising other labs.
USE ROLE ROLE_CORE_LAB_ADMIN;
USE WAREHOUSE WH_CORE_XS;
USE DATABASE ALPINE_RETAIL;
USE SCHEMA SECURE;
CREATE OR REPLACE TABLE CUSTOMERS_PII AS
SELECT * FROM ALPINE_RETAIL.LAB.CUSTOMERS;
SELECT COUNT(*) AS N FROM CUSTOMERS_PII;What this does: Lab admin / ACCOUNTADMIN see clear email. Everyone else sees ****@…. Body uses CURRENT_ROLE() (primary only).
CREATE OR REPLACE MASKING POLICY MP_EMAIL_MASK AS (VAL STRING)
RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('ROLE_CORE_LAB_ADMIN', 'ACCOUNTADMIN')
THEN VAL
ELSE REGEXP_REPLACE(VAL, '.+@', '****@')
END;
ALTER TABLE CUSTOMERS_PII MODIFY COLUMN EMAIL
SET MASKING POLICY MP_EMAIL_MASK;Before Step 3
I’ll mask COUNTRY to hide Canada. Done, right?
Wrong tool. Masking rewrites a cell; the row still exists (and other columns leak). RAP removes the row. Canada needs RAP.
What this does: Lab admin sees all countries. FR_ANALYST only gets rows where COUNTRY = 'US'.
CREATE OR REPLACE ROW ACCESS POLICY RAP_COUNTRY_US
AS (COUNTRY STRING) RETURNS BOOLEAN ->
CASE
WHEN CURRENT_ROLE() IN ('ROLE_CORE_LAB_ADMIN', 'ACCOUNTADMIN')
THEN TRUE
WHEN CURRENT_ROLE() = 'FR_ANALYST' AND COUNTRY = 'US'
THEN TRUE
ELSE FALSE
END;
ALTER TABLE CUSTOMERS_PII
ADD ROW ACCESS POLICY RAP_COUNTRY_US ON (COUNTRY);What this does: Stickers auditors and FinOps can query — “this table is PII” and “charge RETAIL_OPS.”
CREATE TAG IF NOT EXISTS TAG_COST_CENTER
ALLOWED_VALUES 'RETAIL_OPS', 'DATA_PLATFORM', 'MARKETING';
CREATE TAG IF NOT EXISTS TAG_DATA_CLASS
ALLOWED_VALUES 'PUBLIC', 'INTERNAL', 'PII', 'SENSITIVE';
ALTER TABLE CUSTOMERS_PII SET TAG
TAG_COST_CENTER = 'RETAIL_OPS',
TAG_DATA_CLASS = 'PII';What this does: Lets AR_LAB_READ see the secure table, then shows clear emails and all countries as lab admin.
GRANT USAGE ON SCHEMA ALPINE_RETAIL.SECURE TO ROLE AR_LAB_READ;
GRANT SELECT ON TABLE ALPINE_RETAIL.SECURE.CUSTOMERS_PII
TO ROLE AR_LAB_READ;
SELECT CUSTOMER_ID, FULL_NAME, EMAIL, COUNTRY
FROM CUSTOMERS_PII
ORDER BY CUSTOMER_ID;Before Step 6
I switched to FR_ANALYST and still see clear email. Is the masking policy broken?
Policy body checksCURRENT_ROLE()= primary. Table access still unions secondaries — but if lab admin is secondary you may still pass other checks. AlwaysUSE SECONDARY ROLES NONEfor FR proofs.
What this does: Proves masked email + US-only rows. Run alone — not in a Run All with lab-admin setup.
USE ROLE FR_ANALYST;
USE SECONDARY ROLES NONE;
USE WAREHOUSE WH_CORE_XS;
USE DATABASE ALPINE_RETAIL;
SELECT CURRENT_ROLE(), CURRENT_SECONDARY_ROLES();
SELECT CUSTOMER_ID, FULL_NAME, EMAIL, COUNTRY
FROM SECURE.CUSTOMERS_PII
ORDER BY CUSTOMER_ID;Basics attached the policies. Cover each answer before you open it.
Say: Masking rewrites column values; rows still return. RAP returns a boolean per row — false rows never appear. Pick masking for PII cells, RAP for tenancy/region filters.
Say: Policy expressions evaluate CURRENT_ROLE() as the primary role only. That’s why FR proofs need the intended primary and why secondary-role unions confuse access tests.
Say: Metadata for cost chargeback, classification, and automation. Auditors query tags; FinOps groups spend. Tags don’t replace masking/RAP.
Say: Classification helps discover/label sensitive columns. Lineage shows upstream/downstream dependencies for impact analysis. Both are governance awareness topics on Core.
SELECT EMAIL || '' or a view?Say: Masking applies to the column through queries; you don’t get clear text by wrapping expressions as a non-privileged role. (Owners/exempt roles still see clear — least privilege on those roles matters.)