Many Snowflake environments need to limit data sets to particular user groups. These can be based on accounts, regions, departments, etc. In a Salesforce ecosystem, accounts typically will only be viewable by the sales team associated with the account. There may be hierarchies for who can see sets of accounts, as compared to the individual sales account executive who can only see their own accounts and opportunities.
Your row access policy: understanding Snowflake row-level security
In many cases, it’s important to know how to implement row-level security for your Snowflake environment. Here I’ll detail how to create a row access plan (aka row-level security), regardless of your level of experience within Snowflake. I’ll use a modified Salesforce opportunity entity to show how you can accomplish this if you’re bringing Salesforce data into Snowflake. In this example, I’ll use two sales teams, but you can tailor this to suit your own needs.
The first thing is to create a table called Opportunity_Partial which has the three main attributes in a Salesforce opportunity: ID, Account_ID, and Name.
USE DATABASE RAP_Example;
USE SCHEMA SALESFORCE;
CREATE OR REPLACE TABLE OPPORTUNITY_PARTIAL (
ID VARCHAR(18) NOT NULL,
ACCOUNT_ID VARCHAR(18),
NAME VARCHAR(360),
PRIMARY_INDUSTRY varchar(100),
PRIMARY_SUB_INDUSTRY varchar(250),
primary key (ID)
);
To ensure no data is used from an actual Salesforce environment, let’s load this table using a Snowflake Marketplace free data set from ZoomInfo. The record set has 71 records.
USE SCHEMA SALESFORCE;
TRUNCATE TABLE opportunity_partial;
insert into opportunity_partial
(ID
,ACCOUNT_ID
,NAME
,PRIMARY_INDUSTRY
,PRIMARY_SUB_INDUSTRY)
select zi_c_location_id
, zi_es_ecid
, zi_c_cbsa_name
, zi_c_industry_primary
, zi_c_sub_industry_primary
from zoominfo_company_master_sample.zoominfo.company_master_sample
where zi_c_cbsa_name is not null;
Now let’s create a schema to manage the security for account management. In this schema, create a table to match accounts to Snowflake user roles. We are going to call the attribute that stores the Snowflake_Role in it Account_Manager. This can be named something that makes sense for your business model. The other attribute(s) will be the values needed to associate other tables to the role.
CREATE SCHEMA security;
CREATE TABLE security.salesaccounts (
Account_Manager varchar,
Account_ID varchar(18)
);
You have the option to create a role to manage the security on this table. For this example, I’m using SysAdmin as my master user, but another role would allow for a business user to have access to manage the account assignments (vs. an IT individual).
USE ROLE SECURITYADMIN;
CREATE ROLE Sales_Accounts_Manager;
GRANT ROLE Sales_Accounts_Manager to USER <Sales manager>
GRANT USAGE on Database RAP_Example to Sales_Accounts_Manager;
GRANT USAGE on Schema Security to Sales_Accounts_Manager;
GRANT Select, Update, Delete on Table SalesAccounts
to USER Sales_Accounts_Manager;
For this example, there are two sales teams that will only need to see accounts associated with their role. Let’s create the roles and assign users to it.
USE ROLE SECURITYADMIN;
CREATE ROLE Sales_Team_East;
CREATE ROLE Sales_Team_West;
GRANT ROLE Sales_Team_East to USER <user account>;
GRANT ROLE Sales_Team_West to USER <user account>;
Now add accounts to our security table and assign accounts to the two sales teams. This is a distinct list of accounts.
USE ROLE SYSADMIN;
TRUNCATE TABLE security.salesaccounts;
INSERT INTO security.salesaccounts
SELECT 'SALES_TEAM_EAST', ACCOUNT_ID
FROM salesforce.opportunity_partial;
UPDATE security.salesaccounts
SET Account_Manager = 'SALES_TEAM_WEST'
WHERE Account_ID > '2030252345';
We need to add security for the new roles to the security schema and table.
GRANT USAGE on WAREHOUSE DE_WH to Role SALES_TEAM_EAST;
GRANT USAGE on WAREHOUSE DE_WH to Role SALES_TEAM_WEST;
GRANT USAGE on Database RAP_Example to ROLE SALES_TEAM_EAST;
GRANT USAGE on Database RAP_Example to ROLE SALES_TEAM_WEST;
GRANT USAGE on Schema Security to ROLE SALES_TEAM_EAST;
GRANT USAGE on Schema Security to ROLE SALES_TEAM_WEST;
GRANT SELECT ON TABLE security.salesaccounts TO ROLE sysadmin;
GRANT SELECT ON TABLE security.salesaccounts TO ROLE SALES_TEAM_EAST;
GRANT SELECT ON TABLE security.salesaccounts TO ROLE SALES_TEAM_WEST;
Now, verify that accounts are assigned correctly.
USE ROLE SALES_TEAM_EAST;
select * from security.salesaccounts
where Account_Manager = current_role();
USE ROLE SALES_TEAM_WEST;
select * from security.salesaccounts
where Account_Manager = current_role();
Creating the row access policy
In this example, the master user is SysAdmin and that role can see all rows in Opportunity_Partial. If the user is in a different role, then the user will only be able to see records assigned to the current_Role. The Return_ID is a variable used to represent the values/IDs of the rows the role is able to access. You can name the variable however you’d like.
In order to use multiple values, multiple variables need to be in the AS clause, such as (Return_ID varchar, Return_Region varchar). Account_ID is the attribute in the security table that has the IDs, whereas Account_Manager is the attribute in the security table with the roles.
USE ROLE SYSADMIN;
CREATE OR REPLACE ROW ACCESS POLICY security.sales_accounts_policy
AS (Return_ID varchar) RETURNS BOOLEAN ->
'SYSADMIN' = CURRENT_ROLE()
OR EXISTS (
SELECT 1 FROM security.salesaccounts
WHERE Account_manager = CURRENT_ROLE()
AND Account_ID = Return_ID
);
You can now grant ownership of your row access policy to a different user. In this example, it is assigned to SysAdmin.
GRANT OWNERSHIP ON ROW ACCESS POLICY security.sales_accounts_policy TO sysadmin;
You can add capabilities to your row access policy. This will allow your sales team manager to add or drop the row access policy. However, this would be unnecessary if you have the IT team manage the row access policy definitions.
GRANT APPLY ON ROW ACCESS POLICY security.sales_accounts_policy TO ROLE SALES_ACCOUNTS_MANAGER;
Apply the row access policy to the opportunity table. The Return ID in the row access policy will be matched to the column(s) in the ON clause.
ALTER TABLE opportunity_partial
ADD ROW ACCESS POLICY security.sales_accounts_policy ON (Account_id);
Here is the syntax to remove the row access policy from a table. You can’t drop a row access policy if it is assigned to any tables.
ALTER TABLE opportunity_partial drop ROW ACCESS POLICY security.sales_accounts_policy;
Grant access to our opportunity table to the roles.
GRANT USAGE ON SCHEMA SALESFORCE to ROLE SALES_TEAM_EAST;
GRANT USAGE ON SCHEMA SALESFORCE to ROLE SALES_TEAM_WEST;
GRANT SELECT ON TABLE opportunity_partial TO ROLE sysadmin;
GRANT SELECT ON TABLE opportunity_partial TO ROLE SALES_TEAM_EAST;
GRANT SELECT ON TABLE opportunity_partial TO ROLE SALES_TEAM_WEST;
Let’s test it out…
USE ROLE SYSADMIN;
SELECT name
FROM opportunity_partial;
USE ROLE SALES_TEAM_EAST;
SELECT name
FROM opportunity_partial;
USE ROLE SALES_TEAM_WEST;
SELECT name
FROM opportunity_partial;
Success with your Snowflake row access policy
Now you know how to implement row-level security for your Snowflake environment! For further Snowflake support, whether it’s around your row access policy, bringing Salesforce and Snowflake data together, or something else, our team of certified Snowflake experts can help you take the next step.
