Day118: Row-Level Security using Secure Views in Snowflake

Опубликовано: 23 Июль 2026
на канале: CloudFire Technologies

252

--------row-level-security-using-secure-views-in-snowflake

--- Steps to implement Row-Level Security using Secure Views in Snowflake

1. Create a table to apply Row-Level Security
2. Create a Role Mapping table
3. Create a Secure View on source table using role mapping table
4. Create Custom Roles and their Role Hierarchy
5. Grant access on Secure View to custom roles
6. Grant access on virtual warehouse to custom roles
7. Assign Custom Roles to Users
8. Query and verify Row-Level Security on data using custom roles

1. Create a table to apply Row-Level Security

use role SYSADMIN;
create or replace database analytics_db;
create or replace schema analytics_db.hr;

create or replace table analytics_db.hr.employees(
employee_id number,
first_name varchar(50),
last_name varchar(50),
email varchar(50),
hire_date date,
country varchar(50)
);

INSERT INTO analytics_db.hr.employees(employee_id,first_name,last_name,email,hire_date,country) VALUES
(100,'Steven','King','[email protected]','2013-06-17','US'),
(101,'Neena','Kochhar','[email protected]','2015-09-21','US'),
(102,'Lex','De Haan','[email protected]','2011-01-13','US'),
(103,'Alexander','Hunold','[email protected]','2016-01-03','UK'),
(104,'Bruce','Ernst','[email protected]','2017-05-21','UK'),
(105,'David','Austin','[email protected]','2015-06-25','UK'),
(106,'Valli','Pataballa','[email protected]','2016-02-05','CA'),
(107,'Diana','Lorentz','[email protected]','2017-02-07','CA'),
(108,'Nancy','Greenberg','[email protected]','2012-08-17','CA')
;

2.Create a Role Mapping table

use role SYSADMIN;
create or replace table analytics_db.hr.role_mapping(
country varchar(50),
role_name varchar(50)
);

INSERT INTO analytics_db.hr.role_mapping(country, role_name) VALUES
('US','DATA_ANALYST_US'),
('UK','DATA_ANALYST_UK'),
('CA','DATA_ANALYST_CA')
;

-- 3. Create a Secure View on source table using role mapping table
use role SYSADMIN;
create or replace secure view analytics_db.hr.vw_employees as
select a.* from employees a join role_mapping b
on a.country = b.country
and current_role() = b.role_name
;

--4. Create Custom Roles and their Role Hierarchy

use role SECURITYADMIN;
create or replace role DATA_ANALYST_US;
create or replace role DATA_ANALYST_UK;
create or replace role DATA_ANALYST_CA;

use role SECURITYADMIN;
grant role DATA_ANALYST_US to role SYSADMIN;
grant role DATA_ANALYST_UK to role SYSADMIN;
grant role DATA_ANALYST_CA to role SYSADMIN;

5. Grant access on Secure View to custom roles

use role SYSADMIN;
grant usage on database analytics_db to role DATA_ANALYST_US;
grant usage on schema analytics_db.hr to role DATA_ANALYST_US;
grant select on all views in schema analytics_db.hr to role DATA_ANALYST_US;

grant usage on database analytics_db to role DATA_ANALYST_UK;
grant usage on schema analytics_db.hr to role DATA_ANALYST_UK;
grant select on all views in schema analytics_db.hr to role DATA_ANALYST_UK;

grant usage on database analytics_db to role DATA_ANALYST_CA;
grant usage on schema analytics_db.hr to role DATA_ANALYST_CA;
grant select on all views in schema analytics_db.hr to role DATA_ANALYST_CA;

use role data_analyst_us;

create warehouse test;

select * from vw_employees;

--- 6. Grant access on virtual warehouse to custom roles

use role ACCOUNTADMIN;
grant usage on warehouse compute_wh to role DATA_ANALYST_US;
grant usage on warehouse compute_wh to role DATA_ANALYST_UK;
grant usage on warehouse compute_wh to role DATA_ANALYST_CA;

-- 7. Assign Custom Roles to Users

-- Creating Users
------------------------------------------------------------
use role USERADMIN;

create or replace user STEVE password = 'SF@123'
COMMENT = 'Sales Administrator'
MUST_CHANGE_PASSWORD = FALSE;

create or replace user TONY password = 'SF@123'
COMMENT = 'Developer'
MUST_CHANGE_PASSWORD = FALSE;

create or replace user BRUCE password = 'SF@123'
COMMENT = 'Analyst'
MUST_CHANGE_PASSWORD = FALSE;

use role SECURITYADMIN;

grant role DATA_ANALYST_US to user TONY;
grant role DATA_ANALYST_UK to user STEVE;
grant role DATA_ANALYST_CA to user BRUCE;