Snowflake’s Policy Context for Testing Security Policies

Thank you for reading my latest article Snowflake’s Policy Context for Testing Security Policies.

Here at Medium I regularly write about modern data platforms and technology trends. To read my future articles simply join my network here or click ‘Follow’. Also feel free to connect with me via YouTube.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

Recently, I found myself updating my Mastering Snowflake Program once again. Given the rapid changes in Snowflake, I regularly make updates to ensure my members have access to the latest knowledge and get the most value from the program.

This time, I added lessons on Projection and Aggregation policies — features I don’t see discussed often. Despite that, they’re easy to set up and highly effective for data-sharing use cases. If you share data with partners or third parties, I’d strongly recommend exploring these features.

However, this post isn’t about the policies themselves but rather how you can test that they’re working as intended. That’s when I discovered the POLICY_CONTEXT function. This useful function allows you to execute a query under the context of a different role and looks something like this:

execute using policy_context(current_role => ‘PUBLIC’) as select * from customer;

There’s a couple of reasons why I like this function. Firstly, it supports the testing for:

• Aggregation Policies
• Projection Policies
• Row-Access Policies
• Masking Policies

Second, it eliminates the need to assign a role to users just for testing — and remembering to remove the role afterward!

When designing these policies, you typically include context functions such as:

• CURRENT_USER
• CURRENT_ROLE
• CURRENT_AVAILABLE_ROLES
• CURRENT_ACCOUNT

Thankfully, the POLICY_CONTEXT function supports all four of these contexts, making it incredibly useful for testing security and access controls. Since these policies are often built to ensure that certain accounts, roles, or users can’t access data they shouldn’t, thorough testing is critical to guarantee everything works as designed. To see a short demo check out the video below.

To stay up to date with the latest business and tech trends in data and analytics, make sure to subscribe to my newsletter, follow me on LinkedIn, and YouTube, and, if you’re interested in taking a deeper dive into Snowflake check out my books ‘Mastering Snowflake Solutions’ and ‘SnowPro Core Certification Study Guide’.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

About Adam Morton

Adam Morton is an experienced data leader and author in the field of data and analytics with a passion for delivering tangible business value. Over the past two decades Adam has accumulated a wealth of valuable, real-world experiences designing and implementing enterprise-wide data strategies, advanced data and analytics solutions as well as building high-performing data teams across the UK, Europe, and Australia.

Adam’s continued commitment to the data and analytics community has seen him formally recognised as an international leader in his field when he was awarded a Global Talent Visa by the Australian Government in 2019.

Today, Adam is dedicated to helping his clients to overcome challenges with data while extracting the most value from their data and analytics implementations. You can find out more information by visiting his website here.

He has also developed a signature training program that includes an intensive online curriculum, weekly live consulting Q&A calls with Adam, and an exclusive mastermind of supportive data and analytics professionals helping you to become an expert in Snowflake. If you’re interested in finding out more, check out the latest Mastering Snowflake details.