RBAC Permission Matrix - xo-data Snowflake Implementation¶

Date: 2025-11-26 Status: Design Phase Purpose: Detailed grant specifications for each role

Permission Matrix - By Role and Database¶

Legend¶

✅ Full Access: CREATE, SELECT, INSERT, UPDATE, DELETE
📖 Read-Only: SELECT only
📝 Write-Only: INSERT only (no SELECT, UPDATE, DELETE)
⚠️ Conditional: Access depends on business justification
❌ No Access: No permissions granted

XO_DATA_ENGINEER_DEV¶

On CND_DB_DEV, WBP_DB_DEV, CORE_DB_DEV¶

Object Type	Schema	Permission	Notes
DATABASE	-	USAGE	Can connect to database
SCHEMA	BRONZE	USAGE, CREATE TABLE, CREATE STAGE, CREATE FILE FORMAT	Full pipeline development
SCHEMA	SILVER	USAGE, CREATE TABLE, CREATE VIEW	Test dbt transformations locally
SCHEMA	GOLD	USAGE, CREATE TABLE, CREATE VIEW	Test dbt transformations locally
TABLES	BRONZE.*	SELECT, INSERT, UPDATE, DELETE	Full access for pipeline dev
TABLES	SILVER.*	SELECT, INSERT, UPDATE, DELETE	Test transformations
TABLES	GOLD.*	SELECT, INSERT, UPDATE, DELETE	Test transformations
WAREHOUSE	XO_DEV_WH	USAGE	Run queries and pipelines

On CND_DB_PROD, WBP_DB_PROD, CORE_DB_PROD¶

Object Type	Schema	Permission	Notes
DATABASE	-	USAGE	Can connect to database
SCHEMA	BRONZE	USAGE	Read-only access
SCHEMA	SILVER	USAGE	Read-only access
SCHEMA	GOLD	USAGE	Read-only access
TABLES	BRONZE.*	SELECT	Debug prod issues
TABLES	SILVER.*	SELECT	Debug prod issues
TABLES	GOLD.*	SELECT	Debug prod issues
WAREHOUSE	XO_PROD_WH	USAGE	Small queries only

Summary: Full access in DEV, read-only in PROD

XO_DATA_ENGINEER_PROD¶

On CND_DB_PROD, WBP_DB_PROD, CORE_DB_PROD¶

Object Type	Schema	Permission	Notes
DATABASE	-	USAGE	Can connect to database
SCHEMA	BRONZE	USAGE	Read-only
SCHEMA	SILVER	USAGE	Read-only
SCHEMA	GOLD	USAGE	Read-only
TABLES	BRONZE.*	SELECT	Debug production issues
TABLES	SILVER.*	SELECT	Debug production issues
TABLES	GOLD.*	SELECT	Debug production issues
WAREHOUSE	XO_PROD_WH	USAGE	Investigation queries

Summary: Read-only production access for debugging

Note: This role is optional - you may decide to only use XO_DATA_ENGINEER_DEV with read-only prod access

XO_AIRFLOW_LOADER_DEV¶

On CND_DB_DEV, WBP_DB_DEV, CORE_DB_DEV¶

Object Type	Schema	Permission	Notes
DATABASE	-	USAGE	Can connect to database
SCHEMA	BRONZE	USAGE, CREATE TABLE, CREATE STAGE, CREATE FILE FORMAT	Pipeline infrastructure
TABLES	BRONZE.*	SELECT, INSERT	Append-only loads, no UPDATE/DELETE
WAREHOUSE	XO_DEV_WH	USAGE	Run COPY INTO operations

On CND_DB_PROD, WBP_DB_PROD, CORE_DB_PROD¶

Object Type	Schema	Permission	Notes
-	-	❌ No Access	Separate prod service account

Summary: Write to DEV BRONZE only, append-only pattern

XO_AIRFLOW_LOADER_PROD¶

On CND_DB_PROD, WBP_DB_PROD, CORE_DB_PROD¶

Object Type	Schema	Permission	Notes
DATABASE	-	USAGE	Can connect to database
SCHEMA	BRONZE	USAGE, CREATE TABLE, CREATE STAGE, CREATE FILE FORMAT	Pipeline infrastructure
TABLES	BRONZE.*	SELECT, INSERT	Append-only loads, no UPDATE/DELETE
WAREHOUSE	XO_PROD_WH	USAGE	Run COPY INTO operations

On CND_DB_DEV, WBP_DB_DEV, CORE_DB_DEV¶

Object Type	Schema	Permission	Notes
-	-	❌ No Access	Separate dev service account

Summary: Write to PROD BRONZE only, append-only pattern

XO_DBT_TRANSFORMER_DEV¶

On CND_DB_DEV, WBP_DB_DEV, CORE_DB_DEV¶

Object Type	Schema	Permission	Notes
DATABASE	-	USAGE	Can connect to database
SCHEMA	BRONZE	USAGE	Read source data
SCHEMA	SILVER	USAGE, CREATE TABLE, CREATE VIEW	Build models
SCHEMA	GOLD	USAGE, CREATE TABLE, CREATE VIEW	Build models
TABLES	BRONZE.*	SELECT	Read-only (source)
TABLES	SILVER.*	SELECT, INSERT, UPDATE, DELETE	Full transformation access
TABLES	GOLD.*	SELECT, INSERT, UPDATE, DELETE	Full transformation access
WAREHOUSE	XO_DEV_WH	USAGE	Run transformations

On CND_DB_PROD, WBP_DB_PROD, CORE_DB_PROD¶

Object Type	Schema	Permission	Notes
-	-	❌ No Access	Separate prod service account

Summary: Transform DEV data, read-only on BRONZE

XO_DBT_TRANSFORMER_PROD¶

On CND_DB_PROD, WBP_DB_PROD, CORE_DB_PROD¶

Object Type	Schema	Permission	Notes
DATABASE	-	USAGE	Can connect to database
SCHEMA	BRONZE	USAGE	Read source data
SCHEMA	SILVER	USAGE, CREATE TABLE, CREATE VIEW	Build models
SCHEMA	GOLD	USAGE, CREATE TABLE, CREATE VIEW	Build models
TABLES	BRONZE.*	SELECT	Read-only (source)
TABLES	SILVER.*	SELECT, INSERT, UPDATE, DELETE	Full transformation access
TABLES	GOLD.*	SELECT, INSERT, UPDATE, DELETE	Full transformation access
WAREHOUSE	XO_PROD_WH	USAGE	Run transformations

On CND_DB_DEV, WBP_DB_DEV, CORE_DB_DEV¶

Object Type	Schema	Permission	Notes
-	-	❌ No Access	Separate dev service account

Summary: Transform PROD data, read-only on BRONZE

XO_ANALYST_CONDENAST¶

On CND_DB_PROD¶

Object Type	Schema	Permission	Notes
DATABASE	-	USAGE	Can connect to database
SCHEMA	GOLD	USAGE	Access analytics layer
TABLES	GOLD.*	SELECT	Read-only metrics
SCHEMA	SILVER	⚠️ USAGE (optional)	For senior analysts only
TABLES	SILVER.*	⚠️ SELECT (optional)	Transaction-level data
WAREHOUSE	XO_ANALYST_WH	USAGE	Run queries

On WBP_DB_PROD¶

Object Type	Schema	Permission	Notes
-	-	❌ No Access	Client isolation

On CORE_DB_PROD¶

Object Type	Schema	Permission	Notes
DATABASE	-	USAGE	Shared dimensions only
SCHEMA	GOLD	USAGE	Access shared data
TABLES	GOLD.DIM_CLIENTS	SELECT	Shared dimension table

On CND_DB_DEV, WBP_DB_DEV, CORE_DB_DEV¶

Object Type	Schema	Permission	Notes
-	-	❌ No Access	Analysts don't need dev access

Summary: CondeNast data only (GOLD layer), no access to other clients

XO_ANALYST_WARBYPARKER¶

On WBP_DB_PROD¶

Object Type	Schema	Permission	Notes
DATABASE	-	USAGE	Can connect to database
SCHEMA	GOLD	USAGE	Access analytics layer
TABLES	GOLD.*	SELECT	Read-only metrics
SCHEMA	SILVER	⚠️ USAGE (optional)	For senior analysts only
TABLES	SILVER.*	⚠️ SELECT (optional)	Transaction-level data
WAREHOUSE	XO_ANALYST_WH	USAGE	Run queries

On CND_DB_PROD¶

Object Type	Schema	Permission	Notes
-	-	❌ No Access	Client isolation

On CORE_DB_PROD¶

Object Type	Schema	Permission	Notes
DATABASE	-	USAGE	Shared dimensions only
SCHEMA	GOLD	USAGE	Access shared data
TABLES	GOLD.DIM_CLIENTS	SELECT	Shared dimension table

Summary: Warby Parker data only (GOLD layer), no access to other clients

XO_ANALYST_EXECUTIVE¶

On CORE_DB_PROD¶

Object Type	Schema	Permission	Notes
DATABASE	-	USAGE	Can connect to database
SCHEMA	GOLD	USAGE	Cross-client aggregates
TABLES	GOLD.DM_HEADCOUNT	SELECT	Company-wide headcount
TABLES	GOLD.DM_REVENUE	SELECT	Aggregated revenue
TABLES	GOLD.DM_CLIENT_PERFORMANCE	SELECT	Client comparison metrics
WAREHOUSE	XO_ANALYST_WH	USAGE	Run queries

On CND_DB_PROD, WBP_DB_PROD¶

Object Type	Schema	Permission	Notes
DATABASE	-	USAGE	High-level metrics only
SCHEMA	GOLD	USAGE	Summary tables only
TABLES	GOLD.DM_SUMMARY_*	SELECT	Monthly/quarterly aggregates, no details

Summary: Cross-client aggregates, no client-specific transaction data

XO_SECURITY_ADMIN¶

Account-Level Permissions¶

Object Type	Permission	Notes
USERS	CREATE, ALTER, DROP	Manage user accounts
ROLES	CREATE, ALTER, DROP, GRANT, REVOKE	Manage custom roles
-	MANAGE GRANTS	Grant/revoke permissions

Summary: Security management only, cannot access data or create databases

Warehouse Permissions Summary¶

XO_DEV_WH (Development Warehouse)¶

Role	Permission	Notes
XO_DATA_ENGINEER_DEV	USAGE, OPERATE	Can start/stop warehouse
XO_AIRFLOW_LOADER_DEV	USAGE	Can use for COPY INTO
XO_DBT_TRANSFORMER_DEV	USAGE	Can use for transformations

XO_PROD_WH (Production Warehouse)¶

Role	Permission	Notes
XO_DATA_ENGINEER_PROD	USAGE	Read-only queries
XO_AIRFLOW_LOADER_PROD	USAGE	COPY INTO operations
XO_DBT_TRANSFORMER_PROD	USAGE	Production transformations

XO_ANALYST_WH (Analyst Warehouse)¶

Role	Permission	Notes
XO_ANALYST_CONDENAST	USAGE	Run analytics queries
XO_ANALYST_WARBYPARKER	USAGE	Run analytics queries
XO_ANALYST_EXECUTIVE	USAGE	Run executive dashboards

Grant Sequences - Implementation Order¶

When creating these permissions, follow this order:

1. Create Warehouses First¶

-- Must exist before granting USAGE
CREATE WAREHOUSE XO_DEV_WH WITH WAREHOUSE_SIZE='SMALL' AUTO_SUSPEND=300;
CREATE WAREHOUSE XO_PROD_WH WITH WAREHOUSE_SIZE='MEDIUM' AUTO_SUSPEND=600;
CREATE WAREHOUSE XO_ANALYST_WH WITH WAREHOUSE_SIZE='SMALL' AUTO_SUSPEND=300;

2. Create Role Hierarchy (Top-Down)¶

-- Parent roles first
CREATE ROLE XO_SECURITY_ADMIN;
CREATE ROLE XO_DATA_ENGINEER;
CREATE ROLE XO_DATA_ANALYST;
CREATE ROLE XO_SERVICE_ACCOUNT;

-- Child roles second
CREATE ROLE XO_DATA_ENGINEER_DEV;
CREATE ROLE XO_DATA_ENGINEER_PROD;
CREATE ROLE XO_ANALYST_CONDENAST;
CREATE ROLE XO_ANALYST_WARBYPARKER;
CREATE ROLE XO_ANALYST_EXECUTIVE;
CREATE ROLE XO_AIRFLOW_LOADER_DEV;
CREATE ROLE XO_AIRFLOW_LOADER_PROD;
CREATE ROLE XO_DBT_TRANSFORMER_DEV;
CREATE ROLE XO_DBT_TRANSFORMER_PROD;

3. Grant Role Hierarchy (Parent → Child)¶

-- SYSADMIN owns all custom roles
GRANT ROLE XO_DATA_ENGINEER TO ROLE SYSADMIN;
GRANT ROLE XO_SERVICE_ACCOUNT TO ROLE SYSADMIN;

-- SECURITYADMIN owns security role
GRANT ROLE XO_SECURITY_ADMIN TO ROLE SECURITYADMIN;

-- Parent-child relationships
GRANT ROLE XO_DATA_ENGINEER_DEV TO ROLE XO_DATA_ENGINEER;
GRANT ROLE XO_DATA_ENGINEER_PROD TO ROLE XO_DATA_ENGINEER;
GRANT ROLE XO_ANALYST_CONDENAST TO ROLE XO_DATA_ANALYST;
GRANT ROLE XO_ANALYST_WARBYPARKER TO ROLE XO_DATA_ANALYST;
GRANT ROLE XO_ANALYST_EXECUTIVE TO ROLE XO_DATA_ANALYST;
GRANT ROLE XO_AIRFLOW_LOADER_DEV TO ROLE XO_SERVICE_ACCOUNT;
GRANT ROLE XO_AIRFLOW_LOADER_PROD TO ROLE XO_SERVICE_ACCOUNT;
GRANT ROLE XO_DBT_TRANSFORMER_DEV TO ROLE XO_SERVICE_ACCOUNT;
GRANT ROLE XO_DBT_TRANSFORMER_PROD TO ROLE XO_SERVICE_ACCOUNT;

4. Grant Warehouse Permissions¶

-- Dev warehouse
GRANT USAGE ON WAREHOUSE XO_DEV_WH TO ROLE XO_DATA_ENGINEER_DEV;
GRANT USAGE ON WAREHOUSE XO_DEV_WH TO ROLE XO_AIRFLOW_LOADER_DEV;
GRANT USAGE ON WAREHOUSE XO_DEV_WH TO ROLE XO_DBT_TRANSFORMER_DEV;

-- Prod warehouse
GRANT USAGE ON WAREHOUSE XO_PROD_WH TO ROLE XO_DATA_ENGINEER_PROD;
GRANT USAGE ON WAREHOUSE XO_PROD_WH TO ROLE XO_AIRFLOW_LOADER_PROD;
GRANT USAGE ON WAREHOUSE XO_PROD_WH TO ROLE XO_DBT_TRANSFORMER_PROD;

-- Analyst warehouse
GRANT USAGE ON WAREHOUSE XO_ANALYST_WH TO ROLE XO_DATA_ANALYST;

5. Grant Database Permissions (After Databases Exist)¶

-- Must run AFTER V1.0.X migrations create databases
-- See detailed grants in permission migration files

Validation Queries¶

Check Role Hierarchy¶

SHOW GRANTS TO ROLE XO_DATA_ENGINEER_DEV;

Check Database Access¶

SHOW GRANTS ON DATABASE CND_DB_DEV;

Check User's Roles¶

SHOW GRANTS TO USER oscar_colunga;

Test Access (as user)¶

USE ROLE XO_ANALYST_CONDENAST;
USE WAREHOUSE XO_ANALYST_WH;

-- Should work
SELECT * FROM CND_DB_PROD.GOLD.DM_SCORECARD_GPP LIMIT 10;

-- Should fail (no access to BRONZE)
SELECT * FROM CND_DB_PROD.BRONZE.GLADLY_CONVERSATIONS LIMIT 10;
-- Error: SQL access control error: Insufficient privileges

-- Should fail (no access to WBP)
SELECT * FROM WBP_DB_PROD.GOLD.DM_METRICS_DAILY LIMIT 10;
-- Error: SQL access control error: Insufficient privileges

Future Roles (As Clients Are Added)¶

When adding new clients (IONOS, NBA, etc.):

-- Create client-specific analyst role
CREATE ROLE XO_ANALYST_IONOS;
GRANT ROLE XO_ANALYST_IONOS TO ROLE XO_DATA_ANALYST;

-- Grant access to their database only
GRANT USAGE ON DATABASE ION_DB_PROD TO ROLE XO_ANALYST_IONOS;
GRANT USAGE ON SCHEMA ION_DB_PROD.GOLD TO ROLE XO_ANALYST_IONOS;
GRANT SELECT ON ALL TABLES IN SCHEMA ION_DB_PROD.GOLD TO ROLE XO_ANALYST_IONOS;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ION_DB_PROD.GOLD TO ROLE XO_ANALYST_IONOS;

-- Grant warehouse access
GRANT USAGE ON WAREHOUSE XO_ANALYST_WH TO ROLE XO_ANALYST_IONOS;

Security Best Practices Applied¶

✅ 1. Least Privilege¶

Analysts: SELECT only on GOLD
Airflow: INSERT only on BRONZE (no UPDATE/DELETE)
dbt: Read-only on BRONZE, write on SILVER/GOLD only

✅ 2. Separation of Duties¶

Airflow loads raw data
dbt transforms data
Analysts consume analytics
Security admin manages access (can't read data)

✅ 3. Environment Isolation¶

DEV roles: Separate from PROD roles
No cross-environment access

✅ 4. Client Isolation¶

Database-level separation
Impossible to grant wrong client access

✅ 5. Default Deny¶

Start with no access
Explicitly grant only what's needed
Use REVOKE for sensitive data

✅ 6. Future Grants¶

Use GRANT ... ON FUTURE TABLES to auto-grant on new tables
Reduces manual grant management

Next Steps¶

✅ Review this permission matrix
⏳ Write Snowflake SQL migration files (V1.3.X)
⏳ Test in DEV environment first
⏳ Validate access patterns with test users
⏳ Deploy to PROD after validation