Access Control: RBAC, DAC, Masking Policies & Row Access

Architecture Diagram 1: Access Control Hierarchy

Architecture Diagram 2: Dynamic Data Masking Flow

Architecture Diagram 3: RBAC Role Hierarchy Tree

Architecture Diagram 4: Dynamic Data Masking Flow

Architecture Diagram 5: Row-Level Security Implementation

Detailed Explanation

What is RBAC?

Role-Based Access Control is Snowflake's primary access model: permissions → roles → users.

RBAC Structure

• Predefined system hierarchy: ACCOUNTADMIN → SECURITYADMIN → USERADMIN → SYSADMIN
• ACCOUNTADMIN — break-glass only (limit to 2–3 named users)
• Custom roles inherit from system roles or other custom roles
• Supports organizational hierarchies (department → team → individual)

Discretionary Access Control (DAC)

• Object-level permissions for fine-grained scenarios
• Privileges: SELECT, INSERT, UPDATE, DELETE, USAGE
• GRANT OPTION controls privilege redistribution
• Useful for temporary access or when RBAC is too coarse

Dynamic Data Masking

• Column-level security — transforms data at query time per role
• Zero storage overhead — original data preserved
• Masking strategies:

Row-Level Security (RLS)

• Filters entire rows from query results based on role/context
• Policies return TRUE/FALSE per row
• Essential for multi-tenant applications

Best Practices

1. Least privilege — grant minimum necessary permissions; audit quarterly
2. Role hierarchies — functional roles (analyst, developer) + organizational roles (marketing, finance)
3. Separation of duties — no single user has both admin and operational privileges
4. Document roles — maintain registry with purpose, users, and permissions

Key Takeaway: Limit ACCOUNTADMIN to 2–3 users. Use SECURITYADMIN for daily security operations. Audit access patterns quarterly.

Key Concepts Table

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
    WHEN CURRENT_ROLE() = 'executive_role' THEN val
    WHEN CURRENT_ROLE() = 'hr_role' AND data_type = 'ssn' THEN val
    WHEN CURRENT_ROLE() = 'marketing_role' AND data_type = 'email'
      THEN CONCAT(LEFT(val, 1), '***@***.com')
    WHEN CURRENT_ROLE() = 'support_role' AND data_type = 'ssn'
      THEN CONCAT('***-**-', RIGHT(val, 4))
    WHEN CURRENT_ROLE() = 'analytics_role' THEN '***'
    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
    WHEN CURRENT_ROLE() = 'executive_role' THEN TRUE
    WHEN CURRENT_ROLE() = 'dept_head_role'
      AND col_department = CURRENT_ACCOUNT_DEPARTMENT() THEN TRUE
    WHEN CURRENT_ROLE() = 'analyst_role'
      AND col_amount < 10000 THEN TRUE
    WHEN CURRENT_ROLE() = 'team_lead_role'
      AND col_department = CURRENT_ACCOUNT_DEPARTMENT()
      AND col_amount < 50000 THEN TRUE
    ELSE FALSE
END;

Performance Metrics

Best Practices

1. Implement least privilege: Grant users only the minimum permissions necessary for their job functions. Regularly review and adjust permissions.

2. Use role hierarchies: Create functional roles for specific job functions and organizational roles for business units. Grant permissions to functional roles.

3. Limit ACCOUNTADMIN: Restrict ACCOUNTADMIN access to 2-3 named users for break-glass scenarios only. Use SECURITYADMIN for daily security operations.

4. Document all roles: Maintain a role registry describing each role's purpose, typical users, and granted permissions. Update documentation regularly.

5. Audit access patterns: Regularly review query history, role assignments, and permission grants to detect anomalies and ensure compliance.

6. Implement dynamic masking: Apply masking policies to sensitive columns (PII, financial data) to protect data while enabling broad access.

7. Use row-level security: Implement RLS for multi-tenant applications or scenarios where different users should see different data subsets.

8. Separate duties: Ensure no single user has both administrative and operational privileges. Implement separation of duties for critical functions.

9. Monitor security events: Set up alerts for failed login attempts, permission changes, and unusual query patterns.

10. Regular security reviews: Conduct quarterly access reviews to ensure permissions remain appropriate and compliant with policies.

See Also

• Warehouse Management — Warehouse access control
• Time Travel & Data Cloning — Data recovery with access control
• Stored Procedures — Security in stored procedures
• Snowflake Architecture — Security architecture overview
• PySpark Iceberg Tables — Data lake security patterns
• Data Warehouse Concepts — Data warehouse design principles