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.

    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

    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.


    col_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.


    col_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. If the associated UDF has a scalar type declaration, it must match the data type of this column.

    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

    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 will apply and mask data for. If the associated UDF has a scalar type declaration, it must match the data type of this column.


    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 masking policy to. Multiple column names may be specified here, separated by a comma.

    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.


    col_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.


    col_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);