Create a dynamic view
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()
: ReturnsTRUE
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()
: ReturnsTRUE
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.
Databricks recommends that you not grant users the ability to read the tables and views referenced in the view.
The following examples illustrate how to create dynamic views in Unity Catalog.
Before you begin
To create or read dynamic views, requirements are the same as those for standard views, except for compute requirements. You must use one of the following compute resources:
A SQL warehouse.
Compute with shared access mode.
Compute with single user access mode on Databricks Runtime 15.4 LTS or above (Public Preview).
You cannot read dynamic views using single user compute on Databricks Runtime 15.3 or below.
To take advantage of the data filtering provided in Databricks Runtime 15.4 LTS and above, you must also verify that your workspace is enabled for serverless compute, because the data filtering functionality that supports dynamic views runs on serverless compute. You might therefore be charged for serverless compute resources when you use single user compute to read dynamic views. See Fine-grained access control on single user compute.
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
Row-level permissions
With a dynamic view, you can specify permissions down to the row or field level. In the following example, only members of the managers
group can view transaction amounts when they exceed $1,000,000. Matching results are filtered out for other users.
CREATE VIEW sales_redacted AS
SELECT
user_id,
country,
product,
total
FROM sales_raw
WHERE
CASE
WHEN is_account_group_member('managers') THEN TRUE
ELSE total <= 1000000
END;
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. In the following example, all users can analyze email domains, but only members of the auditors
group can view a user’s entire email address.
-- The regexp_extract function takes an email address such as
-- user.x.lastname@example.com 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