How to apply Column Masking for Specific users of a View (VDS) – Dremio Support

Summary

Dremio allows for Row-access and column-masking policies to be applied to tables, views, and individual columns by a user with the ADMIN role based on the criteria set by user-defined functions (UDFs). The below working example will step through how to create and apply a column masking policy to a view (VDS) for specific users.

Reported Issue

One particular customer requirement was to apply a column masking policy to a view (VDS) for certain users (including those with ADMIN role).

Overview

Given a view with path: TestSpace.weather and the following users: user1, user2, user3 (admin), we will apply column masking to the view column called NAME for user3 who is also an admin. Other users including user1 and user2 will be able to view the unmasked NAME column data in the VDS.

Relevant Versions Tools and Integrations

All versions of Dremio.

Steps to Resolve

Step 1- First create a UDF (User-Defined Function) called 'protect_name' to actually check and validate the user who is querying the vds and apply the masking of the column data. The check condition below returns the fully unmasked NAME column if the user querying the view is user1 or user2 however if the user querying the view is any other user (for example user3) then the policy will returned the NAME column in masked form.

CREATE OR REPLACE FUNCTION "@dremio".protect_name (NAME VARCHAR(50))
    RETURNS VARCHAR(11)
        RETURN SELECT CASE WHEN query_user()='user1' OR query_user()='user2' THEN NAME
            ELSE CONCAT('XXX-XXX-', SUBSTR(NAME,9,3))
END;

NOTE: This UDF was created under the root of the "@dremio" user. So when referencing the UDF, the path to the UDF should be explicitly and fully qualified.

Step 2- Now assign the 'protect_name' UDF as the masking policy function for the above VDS (TestSpace.weather):

ALTER VIEW TestSpace.weather
    MODIFY COLUMN NAME
    SET MASKING POLICY "@dremio".protect_name (NAME);

NOTE: If run successfully, then you will get back the response: Successfully set column masking for column NAME in table TestSpace.weather.

Next Steps

Once you have created and applied the column masking policy with the said rules, you can now get user1, user2 and user3 to query the above view (VDS) and confirm that only user3 will see masked output like the below for the NAME column in the VDS:

NAME
XXX-XXX-<partial-value>

Additional Resources

Row-Access & Column-Masking: https://docs.dremio.com/25.x/reference/sql/commands/row-column-policies/