In part one<\/a> of our Snowflake Security blog series<\/a>, we discussed how to think about storing and organizing your data, from the Organization level all the way down to individual tables.<\/p>\n Using some of Snowflake\u2019s advanced features, we can take this further and apply rules that control which specific rows and columns from a single table that a given user can see.<\/p>\n Before we dive into leveraging specific Snowflake security features, let\u2019s first review a couple of key concepts that drive our decisions.<\/p>\n In part one of our series, we discussed applying privacy and access controls at increasing levels of detail and specificity. Grain refers to the amount of detail that exists in the data itself as stored. A table with each individual sales transaction is very granular or fine-grained. A table with total spending per customer per month is less granular, and a table with total purchases per month across all customers by country is an even broader grain.<\/p>\n Data granularity is a key nuance to consider when planning your security model. It is easy to say finance analysts at your company should not have access to credit card transaction data, but we really mean they shouldn\u2019t have access to it at the individual customer level. If you roll it up by month across all customers or by category, it may still be proprietary to your company, but it\u2019s no longer private in the same sense.<\/strong><\/p>\n Even ignoring the security ramifications, it\u2019s important to remember data granularity when we look to correlate information from different sources and for different purposes. For example, the data we have may not be fine-grained enough: individual sales transaction data is very fine-grained but may not give us the information we need for detailed product performance analysis. For that, we might want access to each individual item purchased, not only the order information.<\/p>\n Obviously, the finer-grained the data, the more of it there is \u2013 the more it costs to store and the slower it is to query. However, modern cloud platforms like Snowflake<\/a> make it more affordable to store large amounts of data and query it with vastly better performance, assuming it is organized properly<\/strong>. With the added ability to isolate, aggregate, tag and secure information, the industry best practice has evolved to collecting and storing all available data at the finest-possible grain, so you have it when you need it.<\/p>\n This \u201cgrab everything\u201d bias must also acknowledge legal, regulatory and ethical practices regarding collection and retention of data \u2013 granular data that may be useful in the future may also be too \u201cexpensive\u201d to collect and hold from a legal or ethical point of view. Ideally, we will:<\/p>\n Technology developments in the last few years have made this nuanced approach far more feasible than before, especially combined with some careful planning as laid out in the rest of this series. Here are a few Snowflake security features to consider when planning for granular control:<\/strong><\/p>\n The simplest approach is to create a view, which acts as a lens or filter on a given table. (Note in traditional databases, views could cause performance problems, especially when combined. This is much less of a concern in Snowflake, where they encourage views).<\/p>\n If you create a view that shows everything from the employee table except for private fields and remove all access to the underlying table, your private columns are secure. By itself, this approach can be inflexible \u2013 if different people need different access, you may need to create one view that hides all sensitive columns, another that hides all except for birthdate and so on. However, combined with the other tools below, you can avoid this issue.<\/p>\n With a standard view, tech-savvy users might glean some limited information about the underlying tables (such as column names) by looking at the performance plan. If you need to completely prevent this, Snowflake lets us create a Secure View, which obscures all information about the underlying tables. However, there is a performance trade-off as it can no longer optimize for the underlying table structures either.<\/strong><\/p>\n If query performance on a live view becomes an issue, you can create a materialized view, which runs the query but stores a copy of the results as if it were a table. This can conflict with some of the advanced security concepts that follow, but you can avoid this conflict by choosing a consistent pattern.<\/p>\nKey Concepts<\/h2>\n
Grain \/ Granularity<\/h3>\n
Retention<\/h3>\n
\n
\n
\n
\n
\n
Pulling Snowflake Security Features into Your Granular Control Toolbox<\/h2>\n
Views<\/h3>\n
Variations<\/h3>\n
Columns: Dynamic Data Masking<\/h3>\n