In Unity Catalog, you can use dynamic views to configure fine-grained access control, including:

• Security at the level of columns or rows.
• Data masking.

Unity Catalog introduces the following functions, which allow you to dynamically limit which users can access a row, column, or record in a view:

• current_user(): Returns the current user’s email address.
• is_account_group_member(): Returns TRUE if the current user is a member of a specific account-level group. Recommended for use in dynamic views against Unity Catalog data.
• is_member(): Returns TRUE if the current user is a member of a specific workspace-level group. This function is provided for compatibility with the existing Hive metastore. Avoid using it with views against Unity Catalog data, because it does not evaluate account-level group membership.

Dynamic views in Unity Catalog allow administrators to define a logical view of a table that restricts the data that is accessible to different users or applications. This can be achieved by filtering out specific rows or columns within the table based on a set of rules or conditions.

Data masking

Because views in Unity Catalog use Spark SQL, you can implement advanced data masking by using more complex SQL expressions and regular expressions.

-- The regexp_extract function takes an email address such as
-- [email protected] and extracts 'example', allowing
-- analysts to query the domain name.

CREATE VIEW sales_redacted AS
SELECT
  user_id,
  region,
  CASE
    WHEN is_account_group_member('auditors') THEN email
    ELSE regexp_extract(email, '^.*@(.*)$', 1)
  END
  FROM sales_raw

Column-level permissions

With a dynamic view, you can limit the columns a specific user or group can access. In the following example, only members of the auditors group can access email addresses from the sales_raw table. During query analysis, Apache Spark replaces the CASE statement with either the literal string REDACTED or the actual contents of the email address column. Other columns are returned as normal. This strategy has no negative impact on the query performance.

-- Alias the field 'email' to itself (as 'email') to prevent the
-- permission logic from showing up directly in the column name results.
CREATE VIEW sales_redacted AS
SELECT
  user_id,
  CASE WHEN
    is_account_group_member('auditors') THEN email
    ELSE 'REDACTED'
  END AS email,
  country,
  product,
  total
FROM sales_raw

Restrict Columns

CREATE OR REPLACE VIEW non_sensitive_healthcare_data_view AS
SELECT
  CASE WHEN is_account_group_member('analysts') THEN 'REDACTED'
    ELSE mrn
  END AS mrn,
  CASE WHEN is_account_group_member('analysts') THEN 'REDACTED'
    ELSE name
  END AS name,
  diagnosis,
  medication
FROM healthcare_data_table;

Row-level permissions