Row-Level Access Control in Snowflake: How to Keep Your Data Safe

How to keep your data safe with row-level access control in Snowflake? Snowflake is a cloud-based data warehouse that enables businesses to analyze data in real-time. It is designed for data warehouses of all sizes and offers a variety of features, including row-level security.

It also offers row-level access control to keep your data safe. This means that you can control who has access to which rows of data, and you can be sure that only authorized users can see your data. This is done by adding a security label to each row. The security label is a string associated with the row and can be used to determine who can access the row.

Let’s see what row-level access is and how you can implement it to better control your data access.

How does row-level access control work in Snowflake?

Image Source

Row-level access control restricts access to specific data rows instead of the entire table. This allows you to control who can see and modify specific data without affecting other data in the table. Row-level access control is implemented in Snowflake using security policies.

Security policies are defined using SQL and can be applied at the table or column level. When a user queries or modifies data, Snowflake compares the user’s security policy to the security policy assigned to the table or column. If the user’s policy allows access, the operation is allowed. The operation is not allowed if the user’s policy does not allow access.

Implementing row-level access in Snowflake

To enable row-level access control, you first need to create a role that will be used to access the data. This role can be used to access data in any table in the database. Then, you need to add the appropriate privileges to the role.

You can use the following privileges to control access to data:

SELECT: Allows the user to query the data

UPDATE: Allows the user to update the data

DELETE: Allows the user to delete the data

INSERT: Allows the user to insert new data into the table

Let’s see how you can implement row-level access to different users in Snowflake:

Step 1 : Create a Table for the Data

create table sales (

  customer varchar,

  product varchar,

  spend decimal(20, 2),

  sale_date date,

  region varchar

);

Step 2 : Create a Mapping Table, Custom Role, and Grant the SELECT Privilege

Create a mapping table, so you can assign specific rows to various custom roles like sales manager or regional manager. This access allows specified users to query the mapping table.

create table security.salesmanagerregions (

  sales_manager varchar,

  region varchar

);

use role securityadmin;

create role mapping_role;

grant select on table security.salesmanagerregions to role mapping_role;

Step 3 : Create a row access policy

Here, we will give row access to two roles: sales executive and sales manager.

You must understand that the schema owners will be automatically granted access to CREATE ROW ACCESS POLICY privilege. This helps to determine whether roles/users can provide privileges to other users.

use role <schema_owner_role>;

create or replace row access policy security.sales_policy as (sales_region varchar) returns boolean ->

  'sales_executive_role' = current_role()

      or exists (

            select 1 from salesmanagerregions

              where sales_manager = current_role()

                and region = sales_region

          )

;

Step 4 : Grant Privileges to Custom Roles

The GRANT <privileges> … TO ROLE

grant ownership on row access policy security.sales_policy to mapping_role;

grant apply on row access policy security.sales_policy to role sales_analyst_role;

Step 5 : Add the Row Access Policy to a Table

use role securityadmin;

alter table sales add row access policy security.sales_policy on (region);

Step 6 : Set a Role to Query the Protected Table Data

grant select on table sales to role sales_manager_role;

Step 7 : Test your row-level Access Policy

After you have populated data and implemented a row-access policy, test it with the following code:

use role sales_manager_role;

select product, sum(spend)

from sales

where year(sale_date) = 2020

group by product;

Conclusion

Row-level security is important in Snowflake because it enables businesses to restrict access to specific data rows for specific users. This helps businesses protect sensitive data and ensure that only authorized users can access it.

Datameer includes role-based access to Snowflake data, which is an explicit set of rows or expressions or query that defines a group of rows meeting a certain condition.