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

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

I am not sure if I understand your question correctly, but view is a view and you could write any logic you want:

CREATE OR REPLACE SECURE VIEW Fruits_v
COPY GRANTS
AS
SELECT *
FROM Fruits
WHERE (current_role(), region) IN ( ('region1_role', 'USA',
                                   ,('region1_role', 'Australia')
                                   ,('region2_role', 'Italy')
                                   ,('region2_role', 'India')
                                  )
  OR current_role() = 'global_role';

The open question is about maintaining this approach. When new role is added you will need to update view definition instead of adding new row to driving table.

Recommended reading: Understanding Row-level Security and using policy approach.

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

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

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

Why not use UDF and entitlement/mapping table instead? Same way upcoming row level access will work when GA. I see recommended reading for Snowflake. Now the UDF can be used in more places. Or you can use Snowflake's policies in Enterprise version as preview.