After the introduction of Dynamic Data Masking, which enhances column-level security, Snowflake has enabled Row Access Policies feature, which enhances row-level security in the Data Cloud. To meet the compliance and data security standards , governance teams want to ensure that users can view only column-level and row-level data that they are authorized to access. Snowflake supports row-level security through the use of row access policies to determine which rows to return in the query result.
We can can include conditions and functions in the policy expression to transform the data at query runtime when those conditions meets. A single policy can be apply on different tables and views at the same time.
Note: Row access policies do not restrict the ROWS to be inserted into the table, or prevent visible rows from being updated or deleted. They have control the data visibility not on the DML side.
When a database object has both a row access policy and column masking policies, Snowflake evaluates the row access policy first. Also same column cannot be specified in both a masking policy signature and a row access policy signature at the same time.
Consider the below scenario to understand the ROW Access Policy in detail.
We have REP,FLM and OPS three define roles in our organization and need to ensure that Customer information should be restricted to the User assigned with particular role base on the CRID data present in Customer table.
According to the above diagram, We have multiple records in CUSTOMER_CRID table belongs to the different type of CRID i.e. 2*,3*,T*. As per the requirement:
REP which is lower in hierarchy is authorize to access only the data which belongs to CRID 2*,
FLM superior in hierarchy to the REP should be authorize to access 2* and 3* as well but not the T* CRID data.
OPS the top most in hierarchy should have access to all the data in Customer CRID table.
To implement the solution:
- Create the Row Access policy against the Customer CRID table
- Create the Mapping table to define the ROLE-CRID relationship
- Apply the Policy to the CRID column on Customer table
- Query the data with respective Users login
- Create the ROW Access policy:
create or replace row access policy CRID_PER_ROLE as (LOC varchar) returns boolean ->
from CRID_ROLE e
where substr(LOC,1,2) = e.CRID_DATA
and e.ROLE = current_role());
- Create the Mapping table:
3. Apply the Policy
alter table Customer_CRID add row access policy CRID_PER_ROLE on (CRID);
4. Now grant the privilges on Customer_CRID to respective ROLES
GRANT SELECT ON Customer_CRID TO ROLE REP1;
GRANT SELECT ON Customer_CRID TO ROLE FLM1;
GRANT SELECT ON Customer_CRID TO ROLE OPS;
Login to the REP1:
Login to the FLM1:
Login to the OPS:
As a result, all data access on the Customer_CRID table is filter according to the policy.