SSC-EWI-TS0017

Masking not supported

Severity

Low

Description

This EWI is added when SnowConvert finds a masked column inside a CREATE TABLE statement. This functionality doesn't work by adding the option in the column declaration. Manual effort is needed to have the same behavior as SQL Server.

Code Example

Input Code:

IN -> SqlServer_01.sql

CREATE TABLE TABLE1
( 
  [COL1] nvarchar MASKED WITH (FUNCTION = 'default()') NULL,
  [COL2] varchar(100) MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL,
  [COL3] varchar(100) MASKED WITH (FUNCTION = 'email()') NOT NULL,
  [COL4] smallint MASKED WITH (FUNCTION = 'random(1, 100)') NULL
);

Output Code:

OUT -> SqlServer_01.sql

CREATE OR REPLACE TABLE TABLE1
(
  COL1 VARCHAR
               !!!RESOLVE EWI!!! /*** SSC-EWI-TS0017 - COLUMN MASKING NOT SUPPORTED IN CREATE TABLE ***/!!!
               MASKED WITH (FUNCTION = 'default()') NULL,
  COL2 VARCHAR(100)
                    !!!RESOLVE EWI!!! /*** SSC-EWI-TS0017 - COLUMN MASKING NOT SUPPORTED IN CREATE TABLE ***/!!!
 MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL,
  COL3 VARCHAR(100)
                    !!!RESOLVE EWI!!! /*** SSC-EWI-TS0017 - COLUMN MASKING NOT SUPPORTED IN CREATE TABLE ***/!!!
 MASKED WITH (FUNCTION = 'email()') NOT NULL,
  COL4 SMALLINT
                !!!RESOLVE EWI!!! /*** SSC-EWI-TS0017 - COLUMN MASKING NOT SUPPORTED IN CREATE TABLE ***/!!!
                MASKED WITH (FUNCTION = 'random(1, 100)') NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

Recommendations

SnowConvert is not generating MASKING POLICIES in the current version, so they have to be created manually. E.g.:

The first step is to create a masking policy administrator role.

create role masking_admin;

The second one is to grant the necessary privileges to the created role.

grant create masking policy on schema PUBLIC to role masking_admin;
allow table_owner role to set or unset the ssn_mask masking policy -- (optional)
grant apply on masking policy ssn_mask to role table_owner;

The next step is to create the masking policy functions.

-- default mask
create or replace masking policy default_mask as (val string) returns string ->
case
when current_role() in ('ANALYST') then val
else 'xxxx'
end;

-- partial mask
create or replace masking policy partial_mask as (val string) returns string ->
case
when current_role() in ('ANALYST') then val
else LEFT(val,1) || 'xxxxx' || RIGHT(val,1)
end;

-- email mask
create or replace masking policy email_mask as (val string) returns string ->
case
when current_role() in ('ANALYST') then val
else LEFT(val,1) || '[e-mail]'
end;

-- random mask
create or replace masking policy random_mask as (val smallint) returns smallint ->
case
when current_role() in ('ANALYST') then val
else UNIFORM(1,100,RANDOM())::SMALLINT
end;

For sample purposes, we are taking some examples of masking functions in SQL Server, and manually translating it into its equivalent in Snowflake.

The final step is to add the masking policy to the column that originally had the masking option in SQL Server.

alter table if exists TABLE1 modify column COL1 set masking policy default_mask;
alter table if exists TABLE1 modify column COL2 set masking policy partial_mask;
alter table if exists TABLE1 modify column COL3 set masking policy email_mask;
alter table if exists TABLE1 modify column COL4 set masking policy random_mask;

If you need more support, you can email us at [e-mail]