Colum Masking Policies ... how to apply with variables

1

Hello
Dremio documentation Row-Access & Column-Masking | Dremio Documentation explains how to set a policy on a column. Documented function protect_ssn() contains hardcoded names and groups criterias … I tried to improve function with groupname in variables to create a new function :
CREATE OR REPLACE FUNCTION new_protect_ssn(col VARCHAR, grp VARCHAR)
RETURNS VARCHAR
RETURN SELECT
CASE WHEN “is_member”(“grp”) THEN “col”
ELSE ‘Not Authorized’
END

New function works fine but I’m unable to set function via policy :
ALTER TABLE “NASSERVER”.“FOLDER”.“TABLE”
MODIFY COLUMN “COLUMN1”
SET MASKING POLICY new_protect_ssn(“COLUMN1”,‘group’)

Dremio returns an error Encountered “, 'group'” at line 3, column 58 which seems to be coherent with Dremio Documentation of SET MASKING POLICY because it expects only columns …
Is there a workaround to have generic column masking function with variables for goupname or username compatible with SET MASKING POLICY ?

2

I got an additional question :

How to understand that ALTER TABLE … MODIFY COLUMN points to 1 specific column while SET MASKING POLICY can point to multiple column that have to be names ?
My need is to apply function to all columns. Should I use mulitple functions with dedicated MODIFY COLUMN and specify the same COLUMN in SET MASKING POLICY ?
Also do you plan to use wildcards to apply policy to all columns (allowing to specify each column name).

3

I don’t think the UDF can take a constant as input so a workaround could be to wrap the table with a view and project a constant. The constant can then be fed into the UDF as your second argument.

I think it would be better to wrap the table with a view anyway so that you have a level of indirection with downstream dependencies.

4

ALTER TABLE … MODIFY COLUMN … SET MASKING POLICY is applied to a single column at a time and can pass multiple columns as input to the UDF.

There’s no current plan to use wildcards. Do other query engines support this?

5

Thanks Benny. Maybe Dremio does not support wildcards because it expects to respect data types. Then maybe to support wildcard per data type would be possible (eg : all test columns, all int columns …).
Let’s see what Dremio plans.

6

To go on this thread - does anyone have a clearer definition of SET Masking Policy ? I don’t understand why it can take a list of different columns while MODIFY COLUMN can take only one columnname … does anyone have more expérience ?