0

For a project I have to set a masking policy on multiple columns. These columns have different data types (e.g., varchar, date, number...). Is it possible to use the same masking policy on these different columns?

Here you can see my masking policy so far, but this only works for type varchar. I created different masking policies for each data type, but I would like to use 1 masking policy if possible.

create or replace masking policy name_mask as (val varchar) returns varchar ->
    case
        when current_role() in ('ROLE_1') then val
        when current_role() in ('ROLE_2') then md5(val)
        else '*********'
    end;

It should be noted that I would be not aware of the data type of the column before setting this masking policy. The masking policy should have to work for every data type.

Selin
  • 55
  • 1
  • 4
  • A masking policy that returns `'***'` will only work for strings - because it returns a string. How would you apply it for a number column? – Felipe Hoffa Nov 10 '20 at 20:13
  • I could return NULL values instead of '****', this should work with every type of value. But the problem is that you have to define the data type in the first line of the masking policy ('create or replace masking policy name_mask as (val varchar) returns varchar'). I was hoping there was a way to work around this, so my masking policy would work for every data type. – Selin Nov 12 '20 at 07:30

1 Answers1

0

This is not possible under the current implementation of masking policies.

When you define a masking policy, you need to declare the returning type. The returning type needs to match the type of the column it's being applied to:

enter image description here

Currently, Snowflake does not support different input and output data types in a masking policy, such as defining the masking policy to target a timestamp and return a string (e.g. MASKED); the input and output data types must match. https://docs.snowflake.com/en/sql-reference/sql/create-masking-policy.html

It's an interesting feature request tho!

Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
  • On Snowflake it says to use 'CAST' as a work-around. Could you maybe give an example of what this would look like? I can't seem to put this in the correct place to make it work. – Selin Nov 12 '20 at 09:39