Skip to main content
DatabricksPySparkSnowflakeApache Airflow

Snowflake Security, RBAC & Data Governance Assessment

Enterprise cloud data security requires granular privilege isolation and regulatory compliance. This assessment covers Snowflake Discretionary Access Control (DAC) and Role-Based Access Control (RBAC), custom role hierarchies, Dynamic Data Masking policies for PII protection, Row Access Policies (RLS), Secure Data Sharing with external consumer accounts, and privilege verification using SHOW GRANTS.

1. Assessment Overview & Prerequisites​

Security architects on Snowflake must enforce least privilege while streamlining administration. This assessment tests your knowledge of system-defined roles (ACCOUNTADMIN, SECURITYADMIN, USERADMIN, SYSADMIN, PUBLIC), custom functional vs. access role design, column masking policies via conditional CURRENT_ROLE() expressions, object sharing via CREATE SHARE, and revoking permissions safely.

2. What You Will Practice & Validate​

  • Role Hierarchy & Assignment: Design clean role hierarchies connecting functional roles to access roles terminating at SYSADMIN.
  • Dynamic Data Masking Policies: Construct masking policies that selectively obfuscate sensitive columns based on the caller active role.
  • Secure Data Sharing (Delta Sharing): Share databases and tables securely with external Snowflake accounts without copying physical data.
  • Privilege Auditing & Revocation: Audit role assignments and granted privileges using SHOW GRANTS TO ROLE and REVOKE.

3. How to Use This Assessment & Practice Guide​

Analyze access control scenarios by evaluating role precedence, privilege inheritance, and execution context. Remember that in Snowflake, privileges are granted to roles, never directly to users.

4. Target Technical Concept Matrix​

Architectural AreaOperational PurposeProduction Significance
SECURITYADMINSystem-defined role with global MANAGE GRANTS privilege for enterprise security administration.Can grant or revoke any privilege across the account and manages user/role hierarchies.
SYSADMINSystem-defined role intended to own all business databases, warehouses, and schemas.All custom access roles should inherit up to SYSADMIN to ensure centralized resource management.
Masking PolicySchema-level object that transforms raw column values at query runtime based on caller role.Protects PII/HIPAA data without altering underlying stored micro-partitions.
Secure Data ShareSnowflake object that encapsulates read-only access to selected tables/views for external consumer accounts.Enables zero-copy, live data sharing across cloud providers and regions.

5. Interactive Knowledge Assessment​

1. Which Snowflake feature implements role-based access control (RBAC)?

2. How do you create a custom role 'data_analyst' in Snowflake?

3. How do you grant SELECT privileges on a table to a role?

4. How do you create a masking policy to hide sensitive data?

5. How do you apply a masking policy to a column?

6. How do you enable secure data sharing with another Snowflake account?

7. How do you assign a role to a user?

8. How do you check the privileges granted to a role?

9. How do you revoke a privilege from a role?

10. How do you ensure only specific roles can view a column with sensitive data?

6. Deep Dive & Architectural Analysis​

Snowflake System-Defined Role Hierarchy​

Snowflake accounts include five built-in system roles arranged in a strict security hierarchy:

  1. ACCOUNTADMIN: The superuser role combining SECURITYADMIN and SYSADMIN. Manages billing, global account parameters, and all objects. (Strictly restricted to 2-3 named admins with MFA).
  2. SECURITYADMIN: Holds the global MANAGE GRANTS privilege. Creates and assigns users and roles.
  3. USERADMIN: Subordinate to SECURITYADMIN. Dedicated to creating and managing users and roles.
  4. SYSADMIN: Dedicated to creating and managing compute warehouses, databases, schemas, and tables. All custom functional roles should inherit to SYSADMIN.
  5. PUBLIC: Pseudo-role automatically granted to every user in the account. Should hold zero object privileges in production.

Dynamic Data Masking Execution Flow​

When a user executes SELECT ssn FROM employees;:

  1. Compilation Inspection: The Cloud Services optimizer detects that column ssn has an active masking policy attached.
  2. Expression Injection: The optimizer rewrites the AST (Abstract Syntax Tree) to wrap ssn in the policy's SQL expression.
  3. Runtime Evaluation: CURRENT_ROLE() is evaluated. If the caller's active role is in the authorized whitelist, the unmasked plaintext is returned; otherwise, the masked representation (e.g., 'XXX-XX-' || RIGHT(val, 4)) is returned.
  4. Zero Storage Impact: The underlying micro-partitions remain unchanged and encrypted at rest.

7. Real-World Production Scenarios & Pitfalls​

⚠️ Using ACCOUNTADMIN for Daily ETL and Development​

  • Failure Mode: Configuring orchestration tools (Airflow/dbt) or developer accounts with the ACCOUNTADMIN role.
  • Impact: Violates the principle of least privilege, exposes account-level configuration to accidental deletion, and increases security breach blast radius.
  • Production Remedy: Create dedicated custom service account roles (e.g., TRANSFORM_SERVICE_ROLE) granted only necessary warehouse and database privileges.

⚠️ Granting Privileges on Tables Without USAGE on Parent Namespaces​

  • Failure Mode: Executing GRANT SELECT ON TABLE sales_db.public.sales TO ROLE analyst; without granting database and schema USAGE.
  • Impact: The user receives permission denied errors (Object does not exist or operation not authorized) when attempting to query the table.
  • Production Remedy: Always grant USAGE on the parent database and schema before granting table-level object privileges.

8. Canonical Tutorial Reference​

Review the canonical tutorials covering these exact architectures before or after taking this assessment:

9. Next Steps in Your Learning Progression​

Proceed to the Snowflake Advanced Administration & Optimization Quiz covering multi-cluster warehouses, failover, zero-copy cloning, and credit monitoring.

10. Key Takeaways & Architectural Checklist​

  • Privileges Attach to Roles, Not Users: Structure access cleanly by assigning object privileges to roles and roles to users.
  • Masking Evaluates at Query Compilation: Obfuscate sensitive PII dynamically based on the executing role without duplicating tables.
  • SYSADMIN Must Own Resources: Inherit custom roles up to SYSADMIN to prevent orphaned objects when employees depart.