What is row-level security with Snowflake?

Secure better decisions

Business data can play a crucial role in making important decisions. In that way, they are very valuable. We just need to collect it, store it somewhere and then analyze it. Often, this involves personal and other sensitive data that we don't want to share with others. Indeed, in many cases we are not even allowed to do so: we are bound by laws and regulations, such as the General Data Protection Regulation.

Failure to comply with these rules can have enormous (financial) consequences. Therefore, properly setting up and securing a company's information security is essential. On the one hand, it has great (financial) value because it can prevent high costs. On the other hand, it also has positive value if stakeholders(stakeholders) have confidence in how we handle (their) data!

Companies benefit greatly from properly protecting and shielding sensitive personal and business data. To do this, we can start by determining who is allowed to see which tables, but that is not always enough. It is often also necessary to determine which lines of that table someone is allowed to see and which are not. This is also called row-level security. In this blog, we look at how to do that, in great detail, with Snowflake (a data platform in the cloud).

Company data in Snowflake

A company's information system can be set up in a variety of ways. Data can simply be stored on a computer (or server). Then the necessary calculations can be performed on that computer. We can also choose to arrange storage and calculations in the cloud.

The latter can have a number of advantages, though much depends on which provider you choose. Snowflake provides a place to store, analyze and prepare data for data visualization (with Tableau, for example), all in the cloud! This gives us flexibility in how much space we have to store data, but also in how much computing power we have at our disposal.

No matter how information security is designed, security is always important:

• (1) How do we prevent the wrong people from accessing our data?

Nowadays, we can hardly do without a good answer to that question, but there is also a second question that is becoming increasingly important:

• (2) Who do we grant what access to what data?

This second question is fundamentally different from the first. It concerns people to whom we do want to grant access, but whose access we may want to restrict. By delineating which people are allowed to see what, we ensure that things can go wrong in fewer places. At the same time, we know who is responsible for what.

We must, in short, take a lot into account. Fortunately, Snowflake is set up in such a way that a lot is already taken care of automatically. Working with Snowflake means we can focus on who we want to give access to rather than how we (should) do it. In other words, we can deal with the business logic from the second question in the previous paragraph, while leaving the first (security) question largely to Snowflake.

Row-level access (row-level security)

In Snowflake, we can globally control who can enter the system at all. Then we determine what kind of access these people have to which securable objects. For example, we can restrict which tables users can see. We can also give them permission to change those tables. This is all done at the securable object level, but we can also set it more specifically, namely at the rule level!

This is also known as row-level security. Roughly speaking, this means that when users view a table, they see only the rules they are allowed to see. We can also call this row-level access control: that is, we control user access to certain rules.

Controlling at the line level what users can see is very useful, especially when personal data is involved. For example, we can ensure that managers can only see the data of employees they supervise. By preventing them from seeing other employees' data, we limit the number of places where things can go wrong. We give access only where it is needed and not beyond that!

Masking data

We can go even further and define not only per line, but also per column how data is displayed. As an example, we might want an employee's date of birth and name to be fully visible to an immediate supervisor, but not to the managers above. Those may only see that there is a name and, instead of the date of birth, perhaps only January 1 of the year of birth is shown.

Whereas initially we're talking about securing tables at the line level, in the above example we're talking about masking (or masking) columns: we're not removing the columns but adjusting what can be seen.

Policies in Snowflake

So how is all this done in Snowflake? In other words, how are data policies set up? For that, we use 2 types of Snowflake policies:

1. ROW ACCESS POLICY
2. MASKING POLICY

With the first we can control which rules are shown to whom and with the second we control how the columns in those rules are shown to the user.

Example bookstore

To see what we can do with the above, it might be helpful to think of an example. Imagine that Anna owns a bookstore. On Monday morning, 3 books are sold:

1. T. de Pauwissen The winding paths €19.95(Bert)
2. E. Boering Bygone Times €9.5(Cynthia)
3. H.J. van Ginsbergen Evening after evening €15(Dylan)

Bert, Cynthia and Dylan all three sell books in the store. They at least get to see what they have sold themselves. Bert is also responsible for buying the books. He needs to know which books are selling so he can better determine which books the bookstore should buy. At the same time, he does not need to know who has sold what. Only owner Anna gets to see that.

If we arrange this in Snowflake with policies, the characters will see the following:

Owner Anna

Owner Anna is allowed to see everything.

Book buyer and seller Bert

Instead of seeing the names of the vendors Bert masked values: ****. Only with books he has sold himself may he see the seller's name: Bert.

Vendor Cynthia

Cynthia sees only the book she sold herself.

Vendor Dylan

Dylan sees only the book he sold himself.

The examples of Cynthia and Dylan show the result of row-level security/access control with a ROW ACCESS POLICY in Snowflake. (We are looking at screenshots from Snowflake's Snowsight web interface.) In the example of Bert On the contrary, we see a MASKING POLICY at work: Bert may not see who sold what, but may see which book sold. Anna may, as owner, see all the rules without masking anything.

Conclusion

Snowflake takes away a lot of concerns when it comes to securing important corporate data. This allows us to focus entirely on determining who should be able to see what, rather than how we are going to ensure that. Technically, we take care of that by writing 2 types of policies and applying them where needed. All of this can help us meet legal obligations. Apart from that, it can also be very useful when we want to shield business-critical data (even internally). I hope you got something out of this blog!

Check out our other blogs on Tableau, Alteryx and Snowflake or work with one of our consultants. Questions? Get in touch with us!