Databricks Unity Catalog - Table ACL

The main feature of Unity Catalog is to provide you an easy way to setup Table ACL (Access Control Level), but also build Dynamic Views based on each individual permission.

Typically, Analysts will only have access to customers from their country and won't be able to read GDPR/Sensitive informations (like email, firstname etc.)

A typical workflow in the Lakehouse architecture is the following:

* Data Engineers / Jobs can read and update the main data/schemas (ETL part)
* Data Scientists can read the final tables and update their features tables
* Data Analyst have READ access to the Data Engineering and Feature Tables and can ingest/transform additional data in a separate schema.
* Data is masked/anonymized dynamically based on each user access level

With Unity Catalog, your tables, users and groups are defined at the account level, cross workspaces. Ideal to deploy and operate a Lakehouse Platform across all your teams.

Let's see how this can be done with the Unity Catalog

Cluster setup for UC

To be able to run this demo, make sure you create a cluster with the security mode enabled.

Go in the compute page, create a new cluster.

Under "Access mode", select "Single User" and your UC-user (the user needs to exist at the workspace and the account level)

Creating the CATALOG

The first step is to create a new catalog.

Unity Catalog works with 3 layers:

* CATALOG
* SCHEMA (or DATABASE)
* TABLE

To access one table, you can specify the full path: `SELECT * FROM <CATALOG>.<SCHEMA>.<TABLE>`

Note that the tables created before Unity Catalog are saved under the catalog named `hive_metastore`. Unity Catalog features are not available for this catalog.

Note that Unity Catalog comes in addition to your existing data, not hard change required!

Creating the SCHEMA

Next, we need to create the SCHEMA (or DATABASE).

Unity catalog provide the standard GRANT SQL syntax. We'll use it to GRANT CREATE and USAGE on our SCHEMA to all the users for this demo.

They'll be able to create extra table into this schema.

Creating our table

We're all set! We can use standard SQL to create our tables.

We'll use a customers dataset, loading data about users (id, email etc...)

Because we want our demo to be available for all, we'll grant full privilege to the table to all USERS.

Note that the table owner is the current user. Owners have full permissions.

If you want to change the owner you can set it as following: ```ALTER TABLE .uc_acl.customers OWNER TO `account users`;```

Our customer data was filled for us!

The initialization cell already filled the table for us with fake data for the demo, let's review it's content.

Granting users or group access

Let's now use Unity Catalog to GRANT permission on the table.

Unity catalog let you GRANT standard SQL permission to your objects, using the Unity Catalog users or group:

Creating groups

Databricks groups can be created at the account level using the Account Admin UI, or the SCIM API. Here, we created the `dataengineers` group for this demo.

*Note on workspace-level groups: you can also create groups at a workspace level, however, we recommend managing permissions with UC at an account level.*

Conclusion

Unity Catalog gives you Table ACL permissions, leveraging users, group and table across multiple workspaces.

But UC not only gives you control over Tables. You can do more advanced permission and data access pattern such as dynamic masking at the row level.

Next: Fine Grain Access control

Databricks Unity Catalog provides built-in capabilities to add dynamic masking on columns or rows.

Let's see how this can be done in the [01-Row-Column-access-control notebook ]($./01-Row-Column-access-control).