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 ?
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).
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.