Access Control: RBAC, DAC, Masking Policies & Row Access
Architecture Diagram 1: Access Control Hierarchy
Architecture Diagram
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β SNOWFLAKE ACCESS CONTROL HIERARCHY β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ€
β β
β ACCOUNT LEVEL β
β ββββββββββββββ β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β β β
β β ACCOUNTADMIN (Break-glass role) β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β β β β’ Complete account control β β β
β β β β’ Can create/drop warehouses, databases β β β
β β β β’ Can manage billing and support cases β β β
β β β β’ Should be limited to 2-3 named users β β β
β β β β’ Never use for daily operations β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β β β SECURITYADMIN β β β
β β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β β
β β β β β’ Manage grants and roles β β β β
β β β β β’ Create and drop users β β β β
β β β β β’ Grant privileges to roles β β β β
β β β β β’ Cannot create warehouses or databases β β β β
β β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β β β USERADMIN β β β
β β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β β
β β β β β’ Create and manage users β β β β
β β β β β’ Create roles β β β β
β β β β β’ Cannot grant privileges β β β β
β β β β β’ Cannot manage warehouses β β β β
β β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β β
β βΌ β
β DATABASE LEVEL β
β βββββββββββββββ β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β β β
β β SYSADMIN β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β β β β’ Create and manage databases and schemas β β β
β β β β’ Create and manage warehouses β β β
β β β β’ Create and manage stages β β β
β β β β’ Cannot grant privileges to other roles β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β β β CUSTOM ROLES (Database-specific) β β β
β β β β β β
β β β βββββββββββββββ βββββββββββββββ βββββββββββββββββββββββ β β β
β β β β ANALYTICS β β ETL β β REPORTING β β β β
β β β β ROLE β β ROLE β β ROLE β β β β
β β β βββββββββββββββ€ βββββββββββββββ€ βββββββββββββββββββββββ€ β β β
β β β β SELECT on β β INSERT/ β β SELECT on β β β β
β β β β all tables β β UPDATE on β β views only β β β β
β β β β β β staging β β β β β β
β β β βββββββββββββββ βββββββββββββββ βββββββββββββββββββββββ β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β β
β βΌ β
β SCHEMA LEVEL β
β βββββββββββββ β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β β β Schema: PROD β β β
β β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β β
β β β β Tables: β β β β
β β β β β’ sales_data (Full access for ANALYTICS role) β β β β
β β β β β’ customer_pii (Masked for ANALYTICS, Full for HR) β β β β
β β β β β’ financial_data (Row-level security) β β β β
β β β β β β β β
β β β β Views: β β β β
β β β β β’ sales_summary (Derived from sales_data) β β β β
β β β β β’ customer_analytics (Masked PII) β β β β
β β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β β
β βΌ β
β OBJECT LEVEL β
β βββββββββββββ β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β β β Table: customer_pii β β β
β β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β β
β β β β Column: ssn β β β β
β β β β Masking Policy: ssn_mask β β β β
β β β β β’ HR role: Full access (XXX-XX-1234) β β β β
β β β β β’ Analytics role: Masked (***) β β β β
β β β β β’ Support role: Partial (XXX-XX-5678) β β β β
β β β β β β β β
β β β β Column: email β β β β
β β β β Masking Policy: email_mask β β β β
β β β β β’ Marketing role: Full access β β β β
β β β β β’ Analytics role: j***@***.com β β β β
β β β β β’ Support role: Full access β β β β
β β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
Architecture Diagram 2: Dynamic Data Masking Flow
Architecture Diagram
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β DYNAMIC DATA MASKING FLOW β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ€
β β
β QUERY: SELECT * FROM customer_pii WHERE region = 'US' β
β β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β STEP 1: QUERY PARSING β β
β β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β β β SQL Parser identifies: β β β
β β β β’ Table: customer_pii β β β
β β β β’ Columns: ALL (*) β β β
β β β β’ Current Role: ANALYTICS_ROLE β β β
β β β β’ User: john.doe β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β β
β βΌ β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β STEP 2: MASKING POLICY EVALUATION β β
β β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β β β For each column with masking policy: β β β
β β β β β β
β β β Column: ssn β β β
β β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β β
β β β β MASKING POLICY: ssn_mask β β β β
β β β β β β β β
β β β β CASE β β β β
β β β β WHEN CURRENT_ROLE() IN ('HR_ROLE', 'SECURITY_ADMIN') β β β β
β β β β THEN val β β β β
β β β β WHEN CURRENT_ROLE() = 'SUPPORT_ROLE' β β β β
β β β β THEN 'XXX-XX-' || RIGHT(val, 4) β β β β
β β β β ELSE '***-**-****' β β β β
β β β β END β β β β
β β β β β β β β
β β β β Current Role: ANALYTICS_ROLE β Mask: ***-**-**** β β β β
β β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β β
β β β β β β
β β β Column: email β β β
β β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β β
β β β β MASKING POLICY: email_mask β β β β
β β β β β β β β
β β β β CASE β β β β
β β β β WHEN CURRENT_ROLE() IN ('HR_ROLE', 'MARKETING_ROLE') β β β β
β β β β THEN val β β β β
β β β β WHEN CURRENT_ROLE() = 'SUPPORT_ROLE' β β β β
β β β β THEN val β β β β
β β β β ELSE CONCAT(LEFT(val, 1), '***@***.com') β β β β
β β β β END β β β β
β β β β β β β β
β β β β Current Role: ANALYTICS_ROLE β Mask: j***@***.com β β β β
β β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β β
β βΌ β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β STEP 3: RESULT SET GENERATION β β
β β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β β β Original Data: β β β
β β β ββββββββ¬ββββββββββββ¬ββββββββββββββββββ¬βββββββββββββ β β β
β β β β id β name β ssn β email β β β β
β β β ββββββββΌββββββββββββΌββββββββββββββββββΌβββββββββββββ€ β β β
β β β β 1 β John β 123-45-6789 β j@co.com β β β β
β β β β 2 β Jane β 987-65-4321 β jane@co.comβ β β β
β β β ββββββββ΄ββββββββββββ΄ββββββββββββββββββ΄βββββββββββββ β β β
β β β β β β
β β β Masked Result (ANALYTICS_ROLE): β β β
β β β ββββββββ¬ββββββββββββ¬ββββββββββββββββββ¬βββββββββββββ β β β
β β β β id β name β ssn β email β β β β
β β β ββββββββΌββββββββββββΌββββββββββββββββββΌβββββββββββββ€ β β β
β β β β 1 β John β ***-**-**** β j***@***.coβ β β β
β β β β 2 β Jane β ***-**-**** β j***@***.coβ β β β
β β β ββββββββ΄ββββββββββββ΄ββββββββββββββββββ΄βββββββββββββ β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β β
β βΌ β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β MASKING POLICY TYPES β β
β β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β β β β β β
β β β 1. FULL MASKING β β β
β β β β’ Completely hides original value β β β
β β β β’ Returns static string (e.g., '***') β β β
β β β β’ Use case: Highly sensitive data β β β
β β β β β β
β β β 2. PARTIAL MASKING β β β
β β β β’ Showsι¨ε of original value β β β
β β β β’ Examples: XXX-XX-1234, j***@***.com β β β
β β β β’ Use case: Data that needs partial visibility β β β
β β β β β β
β β β 3. HASH MASKING β β β
β β β β’ Returns hash of original value β β β
β β β β’ Consistent across queries β β β
β β β β’ Use case: Joining on sensitive data β β β
β β β β β β
β β β 4. NULL MASKING β β β
β β β β’ Returns NULL for unauthorized users β β β
β β β β’ Use case: Data that should be invisible β β β
β β ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
Architecture Diagram 3: Row-Level Security Implementation
Architecture Diagram
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β ROW-LEVEL SECURITY IMPLEMENTATION β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ€
β β
β TABLE: financial_data β
β βββββββββββββββββββββ β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β ββββββββββββββ¬βββββββββββ¬βββββββββ¬βββββββββββββ¬βββββββββββββ β β
β β β transactionβ amount β region β department β rep_id β β β
β β ββββββββββββββΌβββββββββββΌβββββββββΌβββββββββββββΌβββββββββββββ€ β β
β β β TXN-001 β $50,000 β US β Sales β REP-101 β β β
β β β TXN-002 β $75,000 β EU β Finance β REP-102 β β β
β β β TXN-003 β $25,000 β APAC β Marketing β REP-103 β β β
β β β TXN-004 β $100,000 β US β Sales β REP-101 β β β
β β β TXN-005 β $60,000 β EU β Finance β REP-104 β β β
β β β TXN-006 β $45,000 β APAC β Marketing β REP-105 β β β
β β ββββββββββββββ΄βββββββββββ΄βββββββββ΄βββββββββββββ΄βββββββββββββ β β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β
β ROW ACCESS POLICY: financial_access β
β βββββββββββββββββββββββββββββββββββ β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β β β
β β CREATE OR REPLACE ROW ACCESS POLICY financial_access AS β β
β β (col_region VARCHAR, col_department VARCHAR, col_rep_id VARCHAR) β β
β β RETURNS BOOLEAN -> β β
β β CASE β β
β β -- Executives see everything β β
β β WHEN CURRENT_ROLE() = 'EXECUTIVE_ROLE' THEN TRUE β β
β β β β
β β -- Regional managers see their region β β
β β WHEN CURRENT_ROLE() = 'REGIONAL_MGR_ROLE' β β
β β AND col_region = CURRENT_ACCOUNT_REGION() THEN TRUE β β
β β β β
β β -- Department heads see their department β β
β β WHEN CURRENT_ROLE() = 'DEPT_HEAD_ROLE' β β
β β AND col_department = CURRENT_ACCOUNT_DEPARTMENT() THEN TRUE β β
β β β β
β β -- Sales reps see only their transactions β β
β β WHEN CURRENT_ROLE() = 'SALES_REP_ROLE' β β
β β AND col_rep_id = CURRENT_ACCOUNT_REP_ID() THEN TRUE β β
β β β β
β β -- Analysts see non-sensitive data only β β
β β WHEN CURRENT_ROLE() = 'ANALYST_ROLE' β β
β β AND col_amount < 50000 THEN TRUE β β
β β β β
β β -- Default: deny access β β
β β ELSE FALSE β β
β β END; β β
β βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ β
β β
β QUERY RESULTS BY ROLE: β
β βββββββββββββββββββββββ β
β β
β ROLE: EXECUTIVE_ROLE β
β ββββββββββββββ¬βββββββββββ¬βββββββββ¬βββββββββββββ¬βββββββββββββ β
β β transactionβ amount β region β department β rep_id β β
β ββββββββββββββΌβββββββββββΌβββββββββΌβββββββββββββΌβββββββββββββ€ β
β β TXN-001 β $50,000 β US β Sales β REP-101 β β
β β TXN-002 β $75,000 β EU β Finance β REP-102 β β
β β TXN-003 β $25,000 β APAC β Marketing β REP-103 β β
β β TXN-004 β $100,000 β US β Sales β REP-101 β β
β β TXN-005 β $60,000 β EU β Finance β REP-104 β β
β β TXN-006 β $45,000 β APAC β Marketing β REP-105 β β
β ββββββββββββββ΄βββββββββββ΄βββββββββ΄βββββββββββββ΄βββββββββββββ β
β β
β ROLE: REGIONAL_MGR_ROLE (US Region) β
β ββββββββββββββ¬βββββββββββ¬βββββββββ¬βββββββββββββ¬βββββββββββββ β
β β transactionβ amount β region β department β rep_id β β
β ββββββββββββββΌβββββββββββΌβββββββββΌβββββββββββββΌβββββββββββββ€ β
β β TXN-001 β $50,000 β US β Sales β REP-101 β β
β β TXN-004 β $100,000 β US β Sales β REP-101 β β
β ββββββββββββββ΄βββββββββββ΄βββββββββ΄βββββββββββββ΄βββββββββββββ β
β β
β ROLE: SALES_REP_ROLE (REP-101) β
β ββββββββββββββ¬βββββββββββ¬βββββββββ¬βββββββββββββ¬βββββββββββββ β
β β transactionβ amount β region β department β rep_id β β
β ββββββββββββββΌβββββββββββΌβββββββββΌβββββββββββββΌβββββββββββββ€ β
β β TXN-001 β $50,000 β US β Sales β REP-101 β β
β β TXN-004 β $100,000 β US β Sales β REP-101 β β
β ββββββββββββββ΄βββββββββββ΄βββββββββ΄βββββββββββββ΄βββββββββββββ β
β β
β ROLE: ANALYST_ROLE β
β ββββββββββββββ¬βββββββββββ¬βββββββββ¬βββββββββββββ¬βββββββββββββ β
β β transactionβ amount β region β department β rep_id β β
β ββββββββββββββΌβββββββββββΌβββββββββΌβββββββββββββΌβββββββββββββ€ β
β β TXN-003 β $25,000 β APAC β Marketing β REP-103 β β
β β TXN-006 β $45,000 β APAC β Marketing β REP-105 β β
β ββββββββββββββ΄βββββββββββ΄βββββββββ΄βββββββββββββ΄βββββββββββββ β
β β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
RBAC is Snowflake's primary access model where permissions are granted to roles, and roles are assigned to users. Users inherit all permissions from assigned roles. The hierarchy includes ACCOUNTADMIN β SECURITYADMIN β USERADMIN β SYSADMIN β custom roles, enabling centralized permission management.
Dynamic Data Masking provides column-level security by transforming data at query time based on the executing role. Unlike static masking, it preserves original data while presenting different views per role β full masking, partial masking, hashing, or nullification.
Row-Level Security (RLS) restricts which rows users can query based on role, user attributes, or session context. Row access policies return TRUE/FALSE per row, filtering entire rows from results β ensuring users only see authorized data subsets.
Limit ACCOUNTADMIN to 2β3 named users for break-glass scenarios. Use SECURITYADMIN for daily security operations. Create functional roles (analyst, developer) and organizational roles (marketing, finance) for clear separation of duties.
- RBAC: Permissions β Roles β Users (hierarchical inheritance)
- Dynamic Masking: Column-level security with role-based transformation
- Row-Level Security: Row filtering based on role/session context
- Least privilege: Grant minimum necessary permissions; audit quarterly
- Separation of duties: No single user has both administrative and operational privileges
Detailed Explanation
Role-Based Access Control (RBAC)
Snowflake implements Role-Based Access Control (RBAC) as its primary access control mechanism. In RBAC, permissions are granted to roles, and roles are assigned to users. Users inherit all permissions from their assigned roles, enabling centralized permission management without modifying individual user privileges. This model simplifies administration and ensures consistent permission enforcement across the organization.
Snowflake provides a hierarchy of predefined system roles, each with specific privileges. The ACCOUNTADMIN role has complete account control but should be limited to break-glass scenarios. SECURITYADMIN manages grants and user creation, USERADMIN handles user management, and SYSADMIN manages databases and warehouses. Custom roles can be created for specific use cases, inheriting from system roles or other custom roles.
Role hierarchy enables permission inheritance. When a role is granted to another role (parent-child relationship), the child role inherits all parent role privileges. This hierarchical model supports organizational structures where department roles inherit from parent business unit roles, and individual roles inherit from department roles.
Discretionary Access Control (DAC)
In addition to RBAC, Snowflake supports Discretionary Access Control (DAC) for object-level permissions. DAC allows object owners to grant specific privileges to individual users or roles. While RBAC provides broad permission frameworks, DAC enables fine-grained control for specific scenarios where RBAC alone is insufficient.
DAC privileges include SELECT, INSERT, UPDATE, DELETE, and USAGE on tables, views, and other database objects. These privileges can be granted with or without the GRANT OPTION, which determines whether the grantee can further distribute the privilege to others. DAC is particularly useful for temporary access grants or scenarios where RBAC roles are too coarse-grained.
Dynamic Data Masking
Dynamic data masking provides column-level security by transforming data at query time based on the executing role. Unlike static masking, which permanently alters stored data, dynamic masking preserves the original data while presenting different views to different roles. This approach enables multiple roles to query the same table while seeing different levels of detail.
Masking policies are defined using SQL expressions that return the masked value based on the current role, user, or other context information. Common masking strategies include full masking (replacing entire values), partial masking (showingι¨ε of values), hashing (returning consistent hash values), and nullification (returning NULL). Policies can be applied to multiple columns and combined with other security features.
Row-Level Security
Row-level security (RLS) restricts which rows users can query based on their role, user attributes, or other context information. Unlike column-level masking, RLS filters entire rows from query results, ensuring users only see data they're authorized to access. This feature is essential for multi-tenant applications or scenarios where different users should see different subsets of the same table.
Row access policies are defined as functions that return TRUE or FALSE for each row. The policy evaluates the current context (role, user, account) and determines whether the row should be visible. Policies can use table column values, session variables, or account metadata to make access decisions.
Best Practices for Access Control
Implementing effective access control requires careful planning and governance. Start with the principle of least privilege, granting users only the minimum permissions necessary for their job functions. Regularly audit role assignments and permissions to ensure compliance with security policies and regulatory requirements.
Use role hierarchies to simplify permission management. Create functional roles for specific job functions (analyst, developer, administrator) and organizational roles for business units (marketing, finance, operations). Grant permissions to functional roles and assign users to both functional and organizational roles as needed.
Document all custom roles and their intended use cases. Maintain a role registry that describes each role's purpose, typical users, and granted permissions. This documentation supports onboarding, auditing, and compliance activities.
Key Concepts Table
| Access Model | Granularity | Management | Use Case |
|---|---|---|---|
| RBAC | Role-level | Centralized | Organization-wide permissions |
| DAC | Object-level | Distributed | Specific access grants |
| Dynamic Masking | Column-level | Centralized | Data privacy compliance |
| Row-Level Security | Row-level | Centralized | Multi-tenant data isolation |
| System Role | Purpose | Key Privileges | Typical Users |
|---|---|---|---|
| ACCOUNTADMIN | Account management | Everything | CTO, Security Lead |
| SECURITYADMIN | Security management | Grants, Users | Security Team |
| USERADMIN | User management | Users, Roles | HR, IT Admin |
| SYSADMIN | Resource management | DB, Warehouse | Data Engineers |
| Masking Type | Data Preservation | Query Performance | Use Case |
|---|---|---|---|
| Full Masking | None | High | Highly sensitive data |
| Partial Masking | Partial | High | Semi-sensitive data |
| Hash Masking | Hash only | Medium | Joining on sensitive data |
| Null Masking | None | High | Invisible data |
Code Examples
-- Example 1: Create custom roles
CREATE ROLE analytics_role;
CREATE ROLE etl_role;
CREATE ROLE reporting_role;
-- Grant roles to parent role
GRANT ROLE analytics_role TO ROLE sysadmin;
GRANT ROLE etl_role TO ROLE sysadmin;
GRANT ROLE reporting_role TO ROLE sysadmin;
-- Example 2: Grant database privileges
GRANT USAGE ON DATABASE analytics_db TO ROLE analytics_role;
GRANT USAGE ON SCHEMA analytics_db.prod TO ROLE analytics_role;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics_db.prod TO ROLE analytics_role;
-- Example 3: Create masking policy
CREATE OR REPLACE MASKING POLICY ssn_mask AS (val STRING)
RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('hr_role', 'security_admin') THEN val
WHEN CURRENT_ROLE() = 'support_role' THEN 'XXX-XX-' || RIGHT(val, 4)
ELSE '***-**-****'
END;
-- Apply masking policy to column
ALTER TABLE customer_pii MODIFY COLUMN ssn SET MASKING POLICY ssn_mask;
-- Example 4: Create row access policy
CREATE OR REPLACE ROW ACCESS POLICY regional_access AS (col_region VARCHAR)
RETURNS BOOLEAN ->
CASE
WHEN CURRENT_ROLE() = 'executive_role' THEN TRUE
WHEN CURRENT_ROLE() = 'regional_mgr_role'
AND col_region = CURRENT_ACCOUNT_REGION() THEN TRUE
ELSE FALSE
END;
-- Apply row access policy to table
ALTER TABLE sales_data ADD ROW ACCESS POLICY regional_access ON (region);
-- Example 5: Grant masking policy usage
GRANT USAGE ON MASKING POLICY ssn_mask TO ROLE analytics_role;
-- Example 6: Create resource monitor with security
CREATE RESOURCE MONITOR security_monitor
WITH
CREDIT_QUOTA = 1000
FREQUENCY = MONTHLY
TRIGGERS
ON 100% DO NOTIFY
ON 100% DO SUSPEND_IMMEDIATELY;
-- Example 7: Monitor access patterns
SELECT
user_name,
role_name,
query_start_time,
query_text,
database_name,
schema_name
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
AND role_name IN ('analytics_role', 'etl_role', 'reporting_role')
ORDER BY query_start_time DESC;
-- Example 8: Audit role assignments
SELECT
grantee_name,
granted_to,
grant_option,
created_on
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE deleted_on IS NULL
ORDER BY grantee_name;
-- Example 9: Dynamic masking with multiple conditions
CREATE OR REPLACE MASKING POLICY pii_mask AS (val STRING, data_type STRING)
RETURNS STRING ->
CASE
-- Executives see everything
WHEN CURRENT_ROLE() = 'executive_role' THEN val
-- HR sees SSN fully
WHEN CURRENT_ROLE() = 'hr_role' AND data_type = 'ssn' THEN val
-- Marketing sees email partially
WHEN CURRENT_ROLE() = 'marketing_role' AND data_type = 'email'
THEN CONCAT(LEFT(val, 1), '***@***.com')
-- Support sees last 4 of SSN
WHEN CURRENT_ROLE() = 'support_role' AND data_type = 'ssn'
THEN CONCAT('***-**-', RIGHT(val, 4))
-- Analytics sees nothing
WHEN CURRENT_ROLE() = 'analytics_role' THEN '***'
-- Default: mask everything
ELSE '***'
END;
-- Example 10: Row-level security with complex logic
CREATE OR REPLACE ROW ACCESS POLICY department_access AS (
col_department VARCHAR,
col_amount NUMBER
)
RETURNS BOOLEAN ->
CASE
-- Executives see everything
WHEN CURRENT_ROLE() = 'executive_role' THEN TRUE
-- Department heads see their department
WHEN CURRENT_ROLE() = 'dept_head_role'
AND col_department = CURRENT_ACCOUNT_DEPARTMENT() THEN TRUE
-- Analysts see small transactions only
WHEN CURRENT_ROLE() = 'analyst_role'
AND col_amount < 10000 THEN TRUE
-- Team leads see their team's transactions
WHEN CURRENT_ROLE() = 'team_lead_role'
AND col_department = CURRENT_ACCOUNT_DEPARTMENT()
AND col_amount < 50000 THEN TRUE
-- Default: deny
ELSE FALSE
END;
Performance Metrics
| Metric | Target | Warning | Critical |
|---|---|---|---|
| Permission Check Latency | < 1ms | 1-5ms | > 5ms |
| Masking Policy Evaluation | < 0.5ms | 0.5-2ms | > 2ms |
| Row Access Policy Evaluation | < 1ms | 1-5ms | > 5ms |
| Role Assignment Count | < 5/user | 5-10/user | > 10/user |
| Policy Count per Table | < 10 | 10-20 | > 20 |
Best Practices
-
Implement least privilege: Grant users only the minimum permissions necessary for their job functions. Regularly review and adjust permissions.
-
Use role hierarchies: Create functional roles for specific job functions and organizational roles for business units. Grant permissions to functional roles.
-
Limit ACCOUNTADMIN: Restrict ACCOUNTADMIN access to 2-3 named users for break-glass scenarios only. Use SECURITYADMIN for daily security operations.
-
Document all roles: Maintain a role registry describing each role's purpose, typical users, and granted permissions. Update documentation regularly.
-
Audit access patterns: Regularly review query history, role assignments, and permission grants to detect anomalies and ensure compliance.
-
Implement dynamic masking: Apply masking policies to sensitive columns (PII, financial data) to protect data while enabling broad access.
-
Use row-level security: Implement RLS for multi-tenant applications or scenarios where different users should see different data subsets.
-
Separate duties: Ensure no single user has both administrative and operational privileges. Implement separation of duties for critical functions.
-
Monitor security events: Set up alerts for failed login attempts, permission changes, and unusual query patterns.
-
Regular security reviews: Conduct quarterly access reviews to ensure permissions remain appropriate and compliant with policies.
See Also
- PySpark Iceberg - Data lake security patterns
- Delta Lake on Databricks - Delta Lake security model
- Data Warehouse Concepts - Data warehouse design principles