🔒 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);
| id | name | salary |
|---|---|---|
| 1 | Aarav | 150000 |
| 2 | Priya | 175000 |
| 3 | Rahul | 130000 |
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
idandname—hiding the sensitivesalarycolumn.
📝 Expected Outputs
🔎 Querying the Secure View
SELECT * FROM employees_public;
| id | name |
|---|---|
| 1 | Aarav |
| 2 | Priya |
| 3 | Rahul |
- ✅
salaryis completely hidden!
🔎 Querying the Base Table as HR_ADMIN
-- Suppose CURRENT_ROLE() = 'HR_ADMIN'
SELECT * FROM employees;
| id | name | salary |
|---|---|---|
| 1 | Aarav | 150000 |
| 2 | Priya | 175000 |
| 3 | Rahul | 130000 |
- ✅ Full data access for HR.
🔎 Querying the Base Table as ANALYST
-- Suppose CURRENT_ROLE() = 'ANALYST'
SELECT * FROM employees;
| id | name | salary |
|---|---|---|
| 1 | Aarav | NULL |
| 2 | Priya | NULL |
| 3 | Rahul | NULL |
- ✅
salaryis 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!