Skip to main content

🔒 Column-Level Security in Snowflake: A 360° Overview

Column-Level Security (CLS) in Snowflake allows you to restrict access to specific columns within a table or view, enabling you to protect sensitive data while still sharing non-sensitive data with users.


🎯 What is Column-Level Security?​

Column-Level Security lets you control access to sensitive columns by ensuring that only authorized users or roles can see or query those columns.

👉 Unlike Row-Level Security (which filters rows), CLS hides entire columns from users who shouldn’t see them.

  • ✅ Use Cases:
    • Hide PII/PHI data like salaries or Aadhaar numbers.
    • Mask financial details for most users while showing them to finance teams.
    • Let different roles see only the data they need.

🌟 How Column-Level Security Works in Snowflake​

Snowflake doesn’t offer a built-in “GRANT SELECT(column)” feature. However, you can implement column-level security by using:

1️⃣ Secure Views – Show only the safe columns in a view, hiding sensitive ones.
2️⃣ Dynamic Data Masking (DDM) – Dynamically mask sensitive column data based on the user’s role.


🔍 1️⃣ Secure Views​

A secure view is a logical layer that exposes only the approved columns to specific roles.

  • ✅ Benefits:
    • Easy to control what columns are visible.
    • Users query the view as if it’s a table.
    • Data in underlying tables remains secure.

Example:

-- Original table
CREATE TABLE employees (
id INT,
name STRING,
salary NUMBER
);

-- Secure view for general users (no salary access)
CREATE SECURE VIEW employees_public AS
SELECT id, name FROM employees;

🔍 2️⃣ Dynamic Data Masking (DDM)​

DDM masks column data dynamically at query time, based on the current role.

  • ✅ Advantages:

    • One table, different data visibility based on roles.
    • No need to maintain separate views or copies.

Example:

-- Create a masking policy for salary
CREATE MASKING POLICY salary_mask AS (val NUMBER)
RETURNS NUMBER ->
CASE
WHEN CURRENT_ROLE() = 'HR_ADMIN' THEN val
ELSE NULL
END;

-- Apply the masking policy
ALTER TABLE employees MODIFY COLUMN salary
SET MASKING POLICY salary_mask;

🏗️ Implementation Steps​

1️⃣ Create the Base Table​

CREATE TABLE employees (
id INT,
name STRING,
salary NUMBER
);

2️⃣ Insert Sample Data​

INSERT INTO employees (id, name, salary) VALUES
(1, 'Aarav', 150000),
(2, 'Priya', 175000),
(3, 'Rahul', 130000);
idnamesalary
1Aarav150000
2Priya175000
3Rahul130000

3️⃣ Create a Masking Policy​

CREATE MASKING POLICY salary_mask AS (val NUMBER) 
RETURNS NUMBER ->
CASE
WHEN CURRENT_ROLE() = 'HR_ADMIN' THEN val
ELSE NULL
END;

4️⃣ Apply the Masking Policy​

ALTER TABLE employees MODIFY COLUMN salary 
SET MASKING POLICY salary_mask;

5️⃣ Create a Secure View (Optional)​

CREATE SECURE VIEW employees_public AS
SELECT id, name FROM employees;
  • ✅ This secure view exposes only id and name—hiding the sensitive salary column.

📝 Expected Outputs​

🔎 Querying the Secure View​

SELECT * FROM employees_public;
idname
1Aarav
2Priya
3Rahul
  • ✅ salary is completely hidden!

🔎 Querying the Base Table as HR_ADMIN​

-- Suppose CURRENT_ROLE() = 'HR_ADMIN'
SELECT * FROM employees;
idnamesalary
1Aarav150000
2Priya175000
3Rahul130000
  • ✅ Full data access for HR.

🔎 Querying the Base Table as ANALYST​

-- Suppose CURRENT_ROLE() = 'ANALYST'
SELECT * FROM employees;
idnamesalary
1AaravNULL
2PriyaNULL
3RahulNULL
  • ✅ salary is masked (NULL) for non-HR roles.

💡 Best Practices​

  • ✅ Use secure views for general, less sensitive access.
  • ✅ Apply masking policies for dynamic, role-based column masking.
  • ✅ Use descriptive naming (e.g., employees_public, salary_mask) for clarity.
  • ✅ Regularly audit column-level policies to ensure compliance.
  • ✅ Apply the least privilege principle: only share what’s truly needed.

⚠️ Limitations​

❌ No direct GRANT SELECT(column)—you must use views or masking. ❌ Complex masking logic can be hard to manage—test thoroughly! ❌ Masking policies don’t apply to SELECT * FROM <secure view> if the column is excluded in the view.


🌐 Real-World Use Cases​

✔️ HR Data – Show salaries only to HR roles. ✔️ Financial Data – Hide revenue figures from sales teams. ✔️ PII/PHI – Protect Aadhaar, PAN, or phone numbers. ✔️ Analytics – Let data analysts work with partial datasets safely.


⚡ Key Takeaways​

✔️ Snowflake’s Secure Views and Dynamic Data Masking are your go-to for column-level security. ✔️ Use views to hide columns completely and masking to dynamically show/hide sensitive data. ✔️ Build a security model that matches your business needs, balancing security and usability!