Skip to main content

๐Ÿ” 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 VIEW for:

    • ๐Ÿ” 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