๐ Secure Views in Snowflake
Secure Views in Snowflake offer enhanced data protection by hiding the underlying logic and metadata from unauthorized users. They are ideal for building privacy-preserving data access layers and are a recommended best practice for shared data environments, such as data marketplaces or multi-role BI reporting setups.
โ What is a Secure View?โ
A Secure View is a special type of view in Snowflake that:
- Prevents unauthorized users from viewing the definition or logic behind the view
- Ensures metadata security when the data is shared across roles, accounts, or organizations
- Is required when publishing to the Snowflake Marketplace or using Reader Accounts
๐ Secure View vs Normal Viewโ
| Feature | โ Secure View | โ ๏ธ Standard View |
|---|---|---|
| Queryable | โ Yes | โ Yes |
| Definition Visible to Non-Owners | โ Hidden | โ Visible |
| Suitable for Data Sharing | โ Yes | โ No |
| Prevent Metadata Leakage | โ Enforced | โ Exposed |
| Required for Reader Accounts/Marketplace | โ Mandatory | โ Not supported |
๐ก Real-World Use Casesโ
- ๐ฉโ๐ผ HR/IT/Finance team access to department-specific views
- ๐ BI dashboards with protected KPIs or salary logic
- ๐ Data shared externally via Data Marketplace
- ๐ก๏ธ Compliance with data governance & masking policies
๐งช Full Demo โ Department-Wise Secure Accessโ
๐งฑ Step 1: Create Source Tableโ
CREATE OR REPLACE TABLE emp_data (
emp_id INT,
emp_name STRING,
department STRING,
salary NUMBER(10,2),
joining_date DATE
);
๐งพ Step 2: Insert Sample Recordsโ
INSERT INTO emp_data VALUES
(101, 'Anil Kumar', 'HR', 55000.00, '2021-01-15'),
(102, 'Divya Rani', 'IT', 75000.00, '2020-08-22'),
(103, 'Rohit Das', 'Finance', 67000.00, '2019-11-01'),
(104, 'Meera Menon', 'IT', 80000.00, '2022-02-20'),
(105, 'Suresh Babu', 'Sales', 60000.00, '2023-04-10'),
(106, 'Nisha Jain', 'Marketing', 58000.00, '2020-12-05'),
(107, 'Ravi Teja', 'Finance', 69000.00, '2021-09-17'),
(108, 'Priya Iyer', 'HR', 62000.00, '2022-07-11'),
(109, 'Kiran Rao', 'Sales', 61000.00, '2021-03-03'),
(110, 'Aarti Patel', 'IT', 82000.00, '2023-01-29');
๐ค Step 3: Create Users and Rolesโ
-- Users
CREATE USER it_user
PASSWORD = 'ItUser#123'
DEFAULT_ROLE = role_it_analyst
MUST_CHANGE_PASSWORD = FALSE;
CREATE USER finance_user
PASSWORD = 'FinanceUser#123'
DEFAULT_ROLE = role_finance_analyst
MUST_CHANGE_PASSWORD = FALSE;
-- Roles
CREATE ROLE role_it_analyst;
CREATE ROLE role_finance_analyst;
-- Assign roles to users
GRANT ROLE role_it_analyst TO USER it_user;
GRANT ROLE role_finance_analyst TO USER finance_user;
๐ Step 4: Create Viewsโ
-- โ
Regular View for IT Department
CREATE OR REPLACE VIEW vw_it_employees AS
SELECT * FROM emp_data WHERE department = 'IT';
-- ๐ Secure View for Finance Department
CREATE OR REPLACE SECURE VIEW vw_finance_employees_secure AS
SELECT * FROM emp_data WHERE department = 'Finance';
๐ Step 5: Grant View Accessโ
-- Grant access to IT Analyst role
GRANT USAGE ON DATABASE EMPLOYEE_DEMO_DB TO ROLE role_it_analyst;
GRANT USAGE ON SCHEMA EMPLOYEE_DEMO_DB.HR TO ROLE role_it_analyst;
GRANT SELECT ON VIEW vw_it_employees TO ROLE role_it_analyst;
-- Grant access to Finance Analyst role (Secure View only)
GRANT USAGE ON DATABASE EMPLOYEE_DEMO_DB TO ROLE role_finance_analyst;
GRANT USAGE ON SCHEMA EMPLOYEE_DEMO_DB.HR TO ROLE role_finance_analyst;
GRANT SELECT ON VIEW vw_finance_employees_secure TO ROLE role_finance_analyst;
๐งช Step 6: Test as Finance Userโ
-- This will work if logged in as finance_user
SELECT * FROM EMPLOYEE_DEMO_DB.HR.vw_finance_employees_secure;
๐ Step 7: Try Viewing Definition (Blocked)โ
-- As finance_user or other non-owner role
SELECT view_definition
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'VW_FINANCE_EMPLOYEES_SECURE';
๐ซ Returns NULL โ because this is a Secure View!
๐ Metadata Access Restrictionsโ
In secure views:
- View definitions are invisible in
INFORMATION_SCHEMA.VIEWS - Even Account Admins cannot see secure view logic unless granted owner-level access
๐ Bonus Tip: Secure Views + Row Access Policiesโ
Secure Views can be combined with:
- Row Access Policies for per-user data filtering
- Dynamic masking policies
- Snowflake Shares or Reader Accounts
โ Best Practices Summaryโ
-
Use
SECURE VIEWfor:- ๐ Sensitive logic
- ๐ Shared datasets
- ๐ Federated BI usage
- Combine with role-based access, row filters, and masking as needed
- Avoid using normal views when view definition must remain confidential