{"id":39320,"date":"2022-11-02T07:27:39","date_gmt":"2022-11-02T11:27:39","guid":{"rendered":"https:\/\/centricconsulting.com\/?p=39320"},"modified":"2022-11-28T11:04:59","modified_gmt":"2022-11-28T16:04:59","slug":"snowflake-security-and-privacy-establishing-row-access-policies","status":"publish","type":"post","link":"https:\/\/centricconsulting.com\/blog\/snowflake-security-and-privacy-establishing-row-access-policies\/","title":{"rendered":"Snowflake Security and Privacy: Establishing Row Access Policies"},"content":{"rendered":"

As we continue our blog series, we walk through how you can use Snowflake row access policies to keep sensitive data protected.<\/h2>\n
\n

In part one<\/a> of our Snowflake security and privacy 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

In part two<\/a>, we discussed ways to think about data access and control at a granular level. And in part three<\/a>, we outlined how you can use tagging to identify sensitive data and combine it with dynamic data masking to prevent unauthorized access.<\/p>\n

In this blog, we\u2019ll dive into how we can restrict access to specific rows within a table using Snowflake row access policies.<\/strong> We\u2019ll follow along with a specific example to show exactly how to put these policies in place.<\/p>\n

Snowflake Row-Level Security<\/h2>\n

Combining the storage hierarchy, flexibility and security policies described in parts one to three, it is quite easy for us to limit access to data<\/a> at a highly granular level. For simple restrictions, we can separate data and use custom views.<\/strong><\/p>\n

\"Snowflake<\/a><\/p>\n

In this scenario, no one from MyPartsCo has any access to the \u201cRaw_Data\u201d database \u2013 they cannot even see it exists. They can only see the \u201cParts_Transaction_Data\u201d database. When they query the \u201cSales_Transactions_View,\u201d they see what appears to be the \u201cSales_Transactions\u201d table, but only the rows containing MyPartsCo data. Unless explicitly granted access to the other database, it is impossible for them to see anything else.<\/strong><\/p>\n

For more complex restrictions, we\u2019ll use Snowflake\u2019s row access policies. Much like tags, you can create row access policies once and apply them in multiple locations. A row access policy can use the data present in the table, information about the table itself (e.g., tags), information about the current context (who is asking and what role they have), or information it looks up from somewhere else (a mapping table that associates a user to a region), in any combination.<\/p>\n

Example table:<\/p>\n

\"Snowflake<\/a><\/p>\n

The simplest policy looks at a single attribute: \u201ccurrent_user = Jane?\u201d If yes, all rows are returned. If no, none are returned.<\/p>\n

More realistically, we might compare the current user\u2019s \u201crole\u201d (or the group they belong to) combined with information in the table itself: \u201ccurrent_role = [Region]?\u201d<\/strong><\/p>\n

If the user is in the role \u201cNorthAmerica,\u201d no matter what query they run, the results will only include information from rows 1 and 4. This includes queries like \u201cSUM(Amount)\u201d \u2013 the sum will not include the EU amounts. For this user, rows 2 and 3 do not exist.<\/p>\n

\"Snowflake<\/a>For something like region access, we would extend this further, using a mapping table to tell us who should have access to which region rather than relying on named roles matching regions.<\/p>\n

\"Snowflake<\/a><\/p>\n

Our row access policy can quickly look up the user in the mapping table above, take note of special circumstances like \u201cAll,\u201d and apply filtering from there. We can apply this single lookup function to as many tables and views as we would like.<\/p>\n

Finally, you can combine and stack policies with other tools.<\/p>\n

Example table:<\/p>\n

\"Snowflake<\/a><\/p>\n

Example view (\u201cWHERE Subsidiary = MyPartsCo\u201d):<\/p>\n

\"Snowflake<\/a><\/p>\n

After applying the row access policy \u201ccurrent_role = [Region]?\u201d to the view:<\/p>\n

\"Snowflake<\/a><\/p>\n

Now let\u2019s imagine we also applied a masking policy to the original table (not the MyPartsCo view), based on whether the specific user is allowed to see financial data:<\/p>\n

\"Snowflake<\/a><\/p>\n

Key Considerations for Establishing Snowflake Row Access Policies<\/h2>\n

Now that we walked through our scenario setting up our security policies, there are a few points to keep in mind.<\/p>\n

    \n
  • We must take care to define all policies with a \u201cdefault-nothing\u201d result in mind. If we define a rule as \u201cwhere Subsidiary is not ProductsCo,\u201d then that will return MyPartsCo records, but it will also return records where the Brand is blank, unknown or misspelled, which could accidentally include ProductsCo records. If we define our rule as \u201cwhere Subsidiary is MyPartsCo,\u201d then our default position \u2013 anything else, including unknown \u2013 is \u201cyou see nothing.\u201d<\/li>\n
  • Since there can be performance impacts based on where and how we apply these policies, and since there are multiple tools at our disposal, we\u2019ll need to consider the most effective and efficient approach. For example, putting a row access policy on the underlying data table might be the most secure since no derived views can possibly circumvent that rule, but it will impact every query on that table (including technical operations like loading data).<\/strong> If we can isolate access at a high level first (e.g., through views), we can reduce the performance impact while remaining secure.<\/li>\n
  • Note that we can combine row access and data masking policies on the same table, but we cannot use a masked column to drive a Row Access Policy. For example, we cannot use a \u201cRegion\u201d column to filter a table if we have also masked that same \u201cRegion\u201d column so certain people cannot see it.<\/li>\n<\/ul>\n

    Conclusion<\/h2>\n

    Row access policies, especially when combined with dynamic data masking, alleviate the headaches previously associated with managing access to sensitive data. This opens the door to sharing our \u201csingle version of the truth\u201d wherever it\u2019s needed within our organization (or even outside, via Data Shares) without worrying about inadvertent access to sensitive information.<\/p>\n\n

    \n
    \n How can you develop a data strategy that takes your business maturity model to the next level? We explain in our white paper.\n <\/div>\n
    \n \n\n Download Here\n <\/a>\n <\/div>\n <\/div>\n","protected":false},"excerpt":{"rendered":"

    As we continue our blog series, we walk through how you can use Snowflake row access policies to keep sensitive data protected.<\/p>\n","protected":false},"author":178,"featured_media":39333,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_oasis_is_in_workflow":0,"_oasis_original":0,"_oasis_task_priority":"","_relevanssi_hide_post":"","_relevanssi_hide_content":"","_relevanssi_pin_for_all":"","_relevanssi_pin_keywords":"","_relevanssi_unpin_keywords":"","_relevanssi_related_keywords":"","_relevanssi_related_include_ids":"","_relevanssi_related_exclude_ids":"","_relevanssi_related_no_append":"","_relevanssi_related_not_related":"","_relevanssi_related_posts":"","_relevanssi_noindex_reason":"","footnotes":""},"categories":[1],"tags":[18616],"coauthors":[15561],"acf":[],"publishpress_future_action":{"enabled":false,"date":"2024-07-21 21:48:26","action":"change-status","newStatus":"draft","terms":[],"taxonomy":"category"},"_links":{"self":[{"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/posts\/39320"}],"collection":[{"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/users\/178"}],"replies":[{"embeddable":true,"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/comments?post=39320"}],"version-history":[{"count":0,"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/posts\/39320\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/media\/39333"}],"wp:attachment":[{"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/media?parent=39320"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/categories?post=39320"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/tags?post=39320"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/coauthors?post=39320"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}