Create and Govern Data Objects Effectively with Unity Catalog in Databricks

With Unity calalog you can create calalogs, schemas, tables, view and user-defined functions providing a robust framework to utilize the data assets. We are going to control access these objects with permission by ensuring that authorized user can only view or modify sensitive information. Using dynamic views unity calalog can provide protection in row-level and column-level in tables. exploring grants and revoke on various objects with unity calalog. We are going to use EV Global sales data set.

Create a new catalog and select it as default catalog:

First, we need to create a new catalog in our metastore. Run the create statement. the USE statement to select a default schema.

In the above provided SQL code, we are creating a new catalog named sales_calalog if it doesn’t already exist. A catalog in Unity Catalog is a top-level container that holds databases and tables. The USE CATALOG sales_calalog; command then sets this catalog as the current working catalog, meaning any subsequent operations will apply to it.

SQL Scripts:

Create and use the new schema:

Next, let’s create a schema in this new catalog. Because we’re now using a unique catalog that is isolated from the rest of the metastore. also set this as the default schema.

Here, the SQL code CREATE SCHEMA VEHICAL_SALES; command creates a new schema named VEHICAL_SALES within the current catalog. A schema is used to logically group related tables for better organization. The subsequent USE SCHEMA VEHICAL_SALES; command sets this schema as the active one, so any further operations will take place within this schema.

SQL Script:

Set up tables and views:

Under the schema let create a table name as EV_SALES inject data from azure storage account.

In SQL Editor let run the script to check if the data is ingested into table:

The hierarchical structure of data objects in Unity Catalog. Here’s the breakdown of the query components:

SQL Script:

Create and grant access to a user-defined function:

User-defined functions can be created within schema in unity catalog. Here we will create a function to mask the data except the last two character in the string.

The above SQL code is how user-defined function (UDF) in Databricks is designed to mask sensitive information. The CREATE OR REPLACE FUNCTION mask_function(x STRING) defines a function that takes a string as input and returns a masked version of the string. It works by replacing all characters except the last two with asterisks (*). The REPEAT("*", LENGTH(x) - 2) replaces the characters, while RIGHT(x, 2) keeps the last two characters visible. as shown in the SELECT output, leaving the last two characters unmasked.

SQL Script:

Use EXECUTE statement to allow member to account user to run the function.

SQL Script:

GRANT EXECUTE ON FUNCTION mask_function TO `account users`;

Using dynamic views to protect table column and rows:

Dynamic views provide the ability to fine-grained access control of columns and rows within table. With partially obscure we can provide column values or redact them entirely. Also, we can omit the rows based on specific criteria.

For access control we can use the functions below:

• current_user(): returns the email address of the user querying the view.
• is_account_group_member(): returns TRUE if the user querying the view is a member of the specified group.

1. Redact columns:

Let’s create a view name ev_sales_view use is_account_group_member() function.

The view checks whether the user belongs to the 'account users' group using the is_account_group_member('account users') function. If the user is a member of this group, the region and parameter fields are replaced with the string 'REDACTED' to protect sensitive information. If the user is not in the group, the actual region and parameter values are displayed.

SQL Script:

View the data in SQL Editor:

2. Restrict rows:

If you want to restrict few rows, we can simply filter out the row based on the condition. Using WHERE clause we can apply the function is_account_group_member().

In this View, the key part is the WHERE condition, which uses the is_account_group_member('account users') function to apply a filter based on the user’s group membership. If the user belongs to the 'account users' group, the view filters the data to include only rows where the year is less than 2018. If the user is not part of this group, all rows are included, as the condition defaults to TRUE.

SQL Script:

View the data in SQL Editor:

Before:

After:

3. Data masking:

Dynamic views final use case is data masking. Masking is similar to redacted columns. Redacted replace the entire column, Masking displays some of the data rather than replacing the entirely. Here we will use mask() function to manipulate the data for the column region.

As shown in the SQL code, we are using the conditional masking based on user group membership. The critical part is the use of the is_account_group_member('account users') function, which checks if the current user belongs to the 'account users' group. If the user is a member of this group, the region column is masked using the mask(region) function to hide sensitive information. If the user is not part of the group, the original region value is displayed without any masking.

SQL Script:

View the data in SQL Editor:

Explore permissions:

Using SHOW GRANTS we can view the permissions for the user for the catalog, schemas, table, views or functions.

SHOW GRANTS ON VIEW: This command displays the permissions that have been granted on the specific view. It helps identify who has access to view or modify the view’s data.

SHOW GRANTS ON TABLE: This command reveals the permissions associated with the table. It shows which users or roles have specific access rights to the table, such as reading or writing data.

SHOW GRANTS ON SCHEMA: This command lists the permissions granted on the schema. It provides insights into the access rights granted at the schema level, affecting all objects contained within it.

SHOW GRANTS ON CATALOG: This command shows the permissions granted on the catalog. It outlines access rights at the catalog level, which can affect all schemas and objects within the catalog.

SHOW GRANTS ON FUNCTION: This command displays the permissions for the function mask_function. It identifies who has the rights to execute or modify this function.

SQL Script:

Revoke access:

No data governance platform would be complete without the ability to revoke previously issued grants.

SQL Script:

Conclusion

Unity Catalog offers a comprehensive and structured approach to managing and governing data objects within your data lakehouse. By creating distinct catalogs, schemas, tables, views, and user-defined functions, you can organize your data assets efficiently. The explicit permission model ensures robust access control, allowing you to grant specific permissions such as USAGE and SELECT to maintain data security and integrity. Utilizing dynamic views further enhances data protection by implementing row- and column-level security.

With Unity Catalog, you not only streamline the creation and management of data objects but also ensure that data governance policies are adhered to, providing a secure and compliant data environment. Embrace the power of Unity Catalog to achieve effective data governance, enabling your organization to harness the full potential of its data while maintaining control and security.