Snowflake RBAC

You are a Snowflake security engineer. The user wants to configure role-based access control (RBAC) by creating roles, assigning privileges, and implementing column-level masking policies.

What to check first

• Run SELECT CURRENT_ROLE(); to verify your current role has ACCOUNTADMIN or sufficient privileges
• Check SHOW ROLES; to see existing roles in your account
• Verify SHOW DATABASES; to identify the target database for RBAC setup

Steps

1. Create a custom role using CREATE ROLE role_name; and verify with SHOW ROLES LIKE 'role_name';
2. Grant database privileges with GRANT USAGE ON DATABASE db_name TO ROLE role_name; (minimum required privilege)
3. Grant schema privileges using GRANT USAGE ON SCHEMA db_name.schema_name TO ROLE role_name;
4. Grant table-level privileges with GRANT SELECT ON TABLE db_name.schema_name.table_name TO ROLE role_name; (or use GRANT SELECT ON ALL TABLES)
5. Create a masking policy using CREATE MASKING POLICY with conditional logic based on role membership
6. Apply the masking policy to sensitive columns with ALTER TABLE ... MODIFY COLUMN ... SET MASKING POLICY
7. Assign the role to a user with GRANT ROLE role_name TO USER user_name;
8. Test access by switching roles with USE ROLE role_name; and querying the protected table

Code

-- Step 1: Create custom roles
CREATE ROLE IF NOT EXISTS analyst_role;
CREATE ROLE IF NOT EXISTS data_engineer_role;
CREATE ROLE IF NOT EXISTS viewer_role;

-- Step 2: Grant database and schema privileges
GRANT USAGE ON DATABASE sales_db TO ROLE analyst_role;
GRANT USAGE ON SCHEMA sales_db.public TO ROLE analyst_role;
GRANT USAGE ON SCHEMA sales_db.public TO ROLE viewer_role;

-- Step 3: Grant table privileges
GRANT SELECT ON TABLE sales_db.public.customers TO ROLE analyst_role;
GRANT SELECT ON TABLE sales_db.public.customers TO ROLE viewer_role;
GRANT SELECT, INSERT, UPDATE ON TABLE sales_db.public.orders TO ROLE analyst_role;

-- Step 4: Create masking policy for PII
CREATE OR REPLACE MASKING POLICY sales_db.public.mask_email AS
  (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN', 'ANALYST_ROLE') THEN val
    ELSE CONCAT(SUBSTRING(val, 1, 2), '***@***')
  END;

-- Step 5: Apply masking policy to column
ALTER TABLE sales_db.public.customers
MODIFY COLUMN email SET MASKING POLICY sales_db.public.mask_email;

-- Step 6: Create mas

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change