snowflake behavior of Dynamic masking in case of using parent Role

Question

This is rather a clarification than a question itself.

I have a parent role named temp_parent_role which has two child role temp_role and temp_role_2. Now I have a table whose access is granted to both role however there is a dynamic policy attach to temp_role_2 as below.

create or replace masking policy col_mask1 as (val string) returns string ->
  case
    when current_role() in ('TEMP_ROLE_2') then val
    else '*********'
  end;

alter table if exists COE_INFO.POC.COL_TABLE modify column COL2 set masking policy col_mask1;

Now as expected when I use temp_role, I can see column2 masked and when I use temp_role_2, I can see actual data in the table.

However, when I use parent role (temp_parent_role). the Column is getting masked, So basically it seems parent_role is considering the role which will see masked data. Will it be always the case.

And if I want parent_role to consider the role which can see unmask data, then is it possible.

Answer 1

You are leveraging current_role() in your DDM definition, which means only TEMP_ROLE_2 can see unmasked data, and every other active role will not. The reason the parent is seeing masked data is that it's not TEMP_ROLE_2 and falls into ELSE of your case statement.

This is always the case unless you change your logic to include the parent role or change the function that you are using in your DDM policy. For example, you could instead do something like this, which would all any roles above TEMP_ROLE_2 to see unmasked data:

create or replace masking policy col_mask1 as (val string) returns string ->
  case
    when is_role_in_session('TEMP_ROLE_2') = True then val
    else '*********'
  end;

This should give you unmasked data for TEMP_ROLE_2 and TEMP_PARENT_ROLE