Snowflake offers us the possibility to restrict user access to data within a table. We can use row access policies and masking policies for that. See my blog on the subject for a more in depth discussion of this topic and for why you would want to do that. Using these techniques, we can design and implement a system that restricts user access. However, we want to make sure that the system actually works the way we intended it to work. Therefore, it is absolutely essential that we are able to test row access policies (and masking policies).
Luckily, Snowflake allows us to do just that. In the following post, we will look at a how we can test these policies using the builtin Snowflake function POLICY_CONTEXT() We will use a simple example that focusses on an easy to understand row access policy. However, POLICY_CONTEXT() is equally well-suited for testing masking policies and very complex policies.
Example row access policy
Imagine we have a Frozen Yoghurt Ice Cream shop. At the end of the day, we all fill in a form with the types of ice cream we sold. For every type we also write down how many we sold. Say, we have the following data of 1 day in Snowflake:
In the PERSON column, we can see there are a few people selling ice creams on the 3rd of September. Now, let’s say someone can only see its own rows and is not allowed to see other people’s rows. Furthermore, the people in the above table have the following Snowflake usernames:
- BILL
- CAMILLA
- KARL
With this, we could easily implement our business logic using a row access policy:
CREATE OR REPLACE ROW ACCESS POLICY yoghurt_rap AS
(name VARCHAR) RETURNS BOOLEAN ->
UPPER(name) = CURRENT_USER()
;Now, we need to apply this ROW ACCESS POLICY to our Snowflake table:
ALTER TABLE frozen_yoghurt_ice_cream
ADD ROW ACCESS POLICY yoghurt_rap
ON (person)
;Our table is immediately protected. If I was Karl I would see the following:
POLICY_CONTEXT()
Obviously, this is a trivial example, but in real life we often have to deal with sensitive data. Therefore, we want to be absolutely sure that Karl only has access to the rows we saw in the previous table. We could ask Karl to log in and tell us what he sees, but that is very impractical. Luckily, there is another way to make sure our policy works as intended.
The query I used to get all data was SELECT * FROM frozen_yoghurt_ice_cream. To see what either Karl, Camilla or Bill would see I simply have to put 1 line in front of that query:
EXECUTE USING POLICY_CONTEXT(CURRENT_USER => 'KARL') AS
SELECT * FROM frozen_yoghurt_ice_cream
;For this, I do need ownership of the aforementioned table (the Snowflake ownership privilege). I also need privileges that allow me to apply the respective row access policy. If both conditions are met, executing this query would show me exactly what Snowflake USER KARL would see.
Note that we can substitute 'KARL' with any other username. In fact, we can substitute the bit after CURRENT_USER => with any string constant '', even if that user does not currently exists(!). In the latter case we would just get no rows. See the following examples:
EXECUTE USING POLICY_CONTEXT(CURRENT_USER => 'CAMILLA') AS
SELECT * FROM frozen_yoghurt_ice_cream
;
EXECUTE USING POLICY_CONTEXT(CURRENT_USER => 'CLEOPATRA') AS
SELECT * FROM frozen_yoghurt_ice_cream
;
Discussion
We looked at an example of how to test our row access policies using the builtin Snowflake function POLICY_CONTEXT(). Make sure to include EXECUTE USING before the POLICY_CONTEXT() function and AS after it. If there is something else you would like to test, you could also use one of the following instead of CURRENT_USER:
CURRENT_ROLE =>CURRENT_AVAILABLE_ROLES =>CURRENT_ACCOUNT =>
Also, don’t forget that you need the right Snowflake privileges for this. Hopefully this will help you develop secure tables and views with fine-grained access control.
Thank you for reading this blog. Also check out our other blogs page to view more blogs on Tableau, Alteryx, and Snowflake here.
Work together with one of our consultants and maximize the effects of your data.
Contact us , and we’ll help you right away.