Achieve Row Level Security(RLS) on Snowflake by assigning Secure views to roles and provide filter on multiple conditions

Question

Let's say we have a table Fruits details,

Country	Fruit
USA	Apple
India	Mango
Italy	Kiwi
Australia	Guava

We have 3 Roles i.e.
region1_role,
region2_role and
global_role.
I want region1_role to have access to both USA and Australia Data,
region2_role to have access to both India and Italy Data,
global_role to have access to all the country's Data.

We can of course use Secure views to achieve this but could it be achieved using only one SECURE VIEW?

Answer 1

Let's start with achieving RLS where Regions are restricted to one Country only. Let this table reside inside
Database: Fruits_DB
Schema: Fruits_Schema
Table: Fruits

Country	Fruit
USA	Apple
India	Mango
Italy	Kiwi
Australia	Guava

Let there be 3 roles REGION1_ROLE, REGION2_ROLE and GLOBAL_ROLE
Master_role be the role having access to all the Tables etc. on the Database

// Create roles using Master_role
CREATE ROLE REGION1_ROLE;
CREATE ROLE REGION2_ROLE;
CREATE ROLE GLOBAL_ROLE;

// Grant access to Database and Schema to these roles
GRANT USAGE ON DATABASE Fruits_DB TO ROLE REGION1_ROLE;
GRANT USAGE ON SCHEMA Fruits_DB.Fruits_Schema TO ROLE REGION1_ROLE;

GRANT USAGE ON DATABASE Fruits_DB TO ROLE REGION2_ROLE;
GRANT USAGE ON SCHEMA Fruits_DB.Fruits_Schema TO ROLE REGION2_ROLE;

GRANT USAGE ON DATABASE Fruits_DB TO ROLE GLOBAL_ROLE;
GRANT USAGE ON SCHEMA Fruits_DB.Fruits_Schema TO ROLE GLOBAL_ROLE;

Creating a Secure View for GLOBAL_REGION

//Secure view DDL
CREATE SECURE VIEW GLOBAL_VIEW
AS
SELECT * FROM Fruits_DB.Fruits_Schema.Fruits;

// Grant Access to this View to Global_role
GRANT SELECT ON GLOBAL_VIEW TO ROLE GLOBAL_ROLE;

Creating a Secure View for REGION1_ROLE and REGION2_ROLE

//Secure view DDL
CREATE SECURE VIEW REGIONAL_VIEW
AS
SELECT * FROM Fruits_DB.Fruits_Schema.Fruits
WHERE COUNTRY =
  CASE
    WHEN CURRENT_ROLE() = 'REGION1_ROLE' THEN 'USA'
    WHEN CURRENT_ROLE() = 'REGION2_ROLE' THEN 'INDIA'
  END;
// Note that Function CURRENT_ROLE() evaluates to the role this Function is being executed in

// Grant Access to this View to REGIONAL ROLES
GRANT SELECT ON REGIONAL_VIEW TO ROLE REGION1_ROLE;
GRANT SELECT ON REGIONAL_VIEW TO ROLE REGION2_ROLE;

The above Secure Views provide the needed RLS but pose 2 limitations:

1. Having to create 2 Views instead of one.
2. A single role can access only one Country cause CASE WHEN only returns a single value which is the root cause of the limitation.
   We can't use other Conditional statements either since they too return a single Value.

CASE
  WHEN
  THEN
END

SOLUTION 1: We use Snowflake's JavaScript UDTF to have us return multiple countries.

CREATE OR REPLACE FUNCTION Fruits_DB.Fruits_Schema.ROLES_REGIONS(CURRENT_ROLE STRING, COUNTRY STRING)
RETURNS TABLE (COUNTRY VARCHAR)
LANGUAGE JAVASCRIPT
AS
$$
  {
    processRow: function f(row, rowWriter, context)  
    {
        var role = row.CURRENT_ROLE;
        
        if (role == 'GLOBAL_ROLE')
        {
            rowWriter.writeRow( {COUNTRY: row.COUNTRY});
   
        }
        else if (role == 'REGION1_ROLE')
        {
            while(this.count == 0)
            {
                rowWriter.writeRow( {COUNTRY: 'USA'});
                rowWriter.writeRow( {COUNTRY: 'AUSTRALIA'});
                this.count = 1;
            }
            
        }
        else if (role == 'REGION2_ROLE')
        {
            while(this.count == 0)
            {
                rowWriter.writeRow( {COUNTRY: 'INDIA'});
                rowWriter.writeRow( {COUNTRY: 'ITALY'});
                this.count = 1;
            }
            
        }
    },
    
    initialize: function(argumentInfo, context) 
    {
       this.count = 0;
    }
  }
  
$$;

In the above JavaScript UDTF, we pass CURRENT_ROLE() and The Country Column from the Fruits table and apply If-Else condition based on the roles
The Function returns a column containing all the countries specific to that role
It returns all the countries passed to it when the CURRENT_ROLE() evaluates to GLOBAL_ROLE

New Unified Secure View:

CREATE SECURE VIEW REGIONAL_VIEW
AS
SELECT * FROM Fruits_DB.Fruits_Schema.Fruits
WHERE COUNTRY IN
  ( SELECT DISTINCT COUNTRY 
    FROM 
    Fruits_DB.Fruits_Schema.Fruits,
    TABLE(Fruits_DB.Fruits_Schema.ROLES_REGIONS(CURRENT_ROLE(),COUNTRY::STRING))
  );
// We use DISTINCT to eliminate any duplicate countries if present

SOLUTION 2: Use Many to Many relationship Table
This is an easy but Tedious approach since it is not very each to maintain

Create a table Roles_Relations

Role	Country
REGION1_ROLE	USA
REGION2_ROLE	INDIA
REGION2_ROLE	ITALY
REGION1_ROLE	AUSTRALIA
GLOBAL_ROLE	USA
GLOBAL_ROLE	INDIA
GLOBAL_ROLE	ITALY
GLOBAL_ROLE	AUSTRALIA

Create a secure view:

// Secure view DDL
CREATE SECURE VIEW REGIONAL_VIEW
AS
SELECT * FROM Fruits_DB.Fruits_Schema.Fruits
WHERE COUNTRY IN
(
  SELECT COUNTRY FROM Roles_Relations 
  WHERE ROLE = CURRENT_ROLE()
);

This approach becomes very difficult to handle when the number of roles increase
Both of these approaches do not require to update the view definition. Updating a view's definition revokes it's access to the other roles unless you use COPY GRANTS, and updating the UDTF or Roles_Relations table doesn't require to Grant the view on roles again and again