Dremio Cloud
Dremio Software
 
On this page

    Row-Access & Column-Masking

    Row-access and column-masking policies may 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). Row access entails the exclusion or inclusion of specific records from query results, whereas column-level masking hides sensitive information without altering the original data.
    Row-access and column-masking policies may 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). Row access entails the exclusion or inclusion of specific records from query results, whereas column-level masking hides sensitive information without altering the original data.

    Setting a Masking Policy

    Creating a New Table/View

    Syntax 
    CREATE [ OR REPLACE ] { TABLE | VIEW } 
    [ IF NOT EXISTS ] table_name
        ( col_name col_type
        [ MASKING POLICY function_name
            ( column_name [, ... ] ) 
        ])

    Parameters

    <column_name>

    String

    The unique name of the column. Multiple columns may be specified, provided they include their data type and are separated by a comma.

    <data_type>

    String

    Specifies the data type associated with the column and its underlying data.

    <function_name>

    String

    The name of the UDF you wish to associate with this policy. Function names within a project must unique and are case-insensitive.

    <column_name>

    String

    The column names to which this masking policy will apply. Multiple columns may be specified if separated by a comma. A UDF serving as column-masking policy must accept and return the same data type as the column it is masking.

    Example

    Set a column-masking policy to multiple columns 
    ALTER TABLE e.employees
    MODIFY COLUMN ssn_col
    SET MASKING POLICY protect_ssn (ssn_col, region);

    Using an Existing Table/View

    Syntax 
    ALTER { TABLE | VIEW } [ IF EXISTS ] table_name 
    MODIFY COLUMN column_name
    SET MASKING POLICY function_name ( column_name [, ... ] );

    Parameters

    MODIFY COLUMN <column_name>

    String

    Specifies the column to which the masking policy will apply and mask data for. The UDF serving as the masking policy must accept and return the same data type as the column it is masking.

    <function_name>

    String

    Specifies the function to use with this security policy. If a function with this name does not exist, then the affected table/view will not be reachable until the policy is dropped or a UDF created.

    Example

    Set a column-masking policy to multiple columns 
    ALTER TABLE e.employees
    MODIFY COLUMN ssn_col
    SET MASKING POLICY protect_ssn (ssn_col, region);

    Unset a Masking Policy

    Syntax 
    ALTER { TABLE | VIEW } [ IF EXISTS ] table_name
   MODIFY COLUMN column_name
   UNSET MASKING POLICY function_name;

    Parameters

    [ IF EXISTS ]

    String

    If included in a query, this clause prevents exceptions from being thrown should the specified object not exist.

    <table_name>

    String

    Identifies the table or view to modify the column data for. This must match the name for the associated object type (e.g., TABLE or VIEW).

    MODIFY COLUMN <column_name>

    String

    Sets the main column to which the masking policy is currently applied.

    <function_name>

    String

    Specifies the function being used with this security policy. If a function with this name does not exist, then the system will display an error message.

    Example

    Unset a column-masking policy 
    ALTER TABLE e.employees
    MODIFY COLUMN ssn_col
    UNSET MASKING POLICY protect_ssn;

    Adding a Row-Access Policy

    Creating a New Table/View

    Syntax 
    CREATE [ OR REPLACE ] { TABLE | VIEW } 
    [ IF NOT EXISTS ] table_name
        ( col_name col_type
        [ ROW ACCESS POLICY function_name
            ( column_name [, ... ] ) 
        ]);

    Parameters

    [ OR REPLACE ]

    String

    If specified, any table/view with the same name will be replaced. This is primarily used to create new tables/views with security policies applied for restricted access. You cannot specify this parameter with the IF NOT EXISTS qualifier.

    [ IF NOT EXISTS ]

    String

    When specified, this creates a table/view only if one does not exist. The creation of the object succeeds (without any errors) only if the specified entity does not already exist in the system. You cannot specify this parameter with OR REPLACE.

    <table_name>

    String

    The name of the object. Object names within a project must be unique, cannot conflict with system-defined objects, and are case-insensitive.

    <column_name>

    String

    The unique name of the column. Multiple columns may be specified, provided they include their data type and are separated by a comma.

    <data_type>

    String

    Specifies the data type associated with the column and its underlying data.

    <function_name>

    String

    The name of the UDF you wish to associate with this policy. Function names within a project must unique and are case-insensitive.

    <column_name>

    String

    The column names to which this row-access policy will apply. Multiple columns may be specified if separated by a comma.

    Example

    Set a row-access policy to a column 
    CREATE TABLE e.employees 
    (ssn_col VARCHAR,region VARCHAR,state_col VARCHAR)
    ROW ACCESS POLICY state_policy (state_col));

    Using an Existing Table/View

    Syntax 
    ALTER { TABLE | VIEW } [ IF EXISTS ] table_name 
    MODIFY COLUMN column_name
    ADD ROW ACCESS POLICY function_name ( column_name [, ... ] );

    Parameters

    [ IF EXISTS ]

    String

    If included in a query, this clause prevents exceptions from being thrown should the specified object not exist.

    <table_name>

    String

    Identifies the table or view to modify the column data for. This must match the name for the associated object type (e.g., TABLE or VIEW).

    MODIFY COLUMN <column_name>

    String

    Sets the main column to which the row-access policy will apply and mask data for.

    <function_name>

    String

    Specifies the function to use with this security policy. If a function with this name does not exist, then the affected table/view will not be reachable until the policy is dropped or a UDF created.

    <column_name>

    String

    Identifies the columns with which to apply the row-access policy to. Multiple column names may be specified here, separated by a comma.

    Example

    Set a row-access policy to a column 
    ALTER TABLE e.employees
    ADD ROW ACCESS POLICY state_policy ( state_col );

    Dropping a Row-Access Policy

    Syntax 
    ALTER { TABLE | VIEW } [ IF EXISTS ] table_name
   MODIFY COLUMN column_name
   DROP ROW ACCESS POLICY function_name ( column_name [, ... ] );

    Parameters

    [ IF EXISTS ]

    String

    If included in a query, this clause prevents exceptions from being thrown should the specified object not exist.

    <table_name>

    String

    Identifies the table or view to modify the column data for. This must match the name for the associated object type (e.g., TABLE or VIEW).

    MODIFY COLUMN <column_name>

    String

    Sets the main column to which the row-access policy is currently applied.

    <function_name>

    String

    Specifies the function being used with this security policy. If a function with this name does not exist, then the system will display an error message.

    Example

    Unset a row-access policy 
    ALTER TABLE employees
    MODIFY COLUMN ssn_col
    DROP ROW ACCESS POLICY protect_ssn (ssn_col, region);