Skip to content

Lesson 5 of 6

Control access with Unity Catalog

Register your Delta table in Unity Catalog, create a schema, and grant read access to a test user or group.

12 minDatabricks

After this lesson, you will be able to register your Delta table in Unity Catalog, create a schema to organise it, and grant read access to another user or group.

What is Unity Catalog

Unity Catalog is a data governance tool built into Databricks. It stores metadata about your data, such as table names, locations, and permissions. With Unity Catalog, you manage who can see and use data from one central place.

In earlier lessons, you created a Delta table called people_delta using Spark SQL. That table is registered in the Hive metastore, which is the older metadata service. Unity Catalog replaces the Hive metastore and adds fine-grained access control.

To use Unity Catalog, you need a catalog, a schema, and a table. A catalog is the top-level container, usually one per organisation or project. A schema sits inside a catalog and groups related tables. A table sits inside a schema.

Create a catalog and schema

Before you can register your table, you need a catalog and a schema. You create them with SQL statements in a notebook attached to your cluster.

First, create a catalog. Use a name that is unique within your workspace. For example, my_catalog.

CREATE CATALOG IF NOT EXISTS my_catalog;

Next, create a schema inside that catalog. A schema is sometimes called a database. The command is similar.

CREATE SCHEMA IF NOT EXISTS my_catalog.my_schema;

Now you have a place to put your table. The full name of a table in Unity Catalog is catalog.schema.table.

Register your Delta table in Unity Catalog

Your existing people_delta table is in the Hive metastore. To move it to Unity Catalog, you create a new table in the catalog that points to the same Delta files. This is called registering an external table.

Run this SQL in your notebook. Replace <path-to-delta-table> with the same location you used in lesson 4.

CREATE TABLE my_catalog.my_schema.people_delta
USING DELTA
LOCATION '<path-to-delta-table>';

This statement creates a new table entry in Unity Catalog. It does not copy the data. The table still reads from the same Delta files.

Now you can query the table using its three-part name.

SELECT * FROM my_catalog.my_schema.people_delta;

You should see the same rows as before. The data is unchanged, but the metadata is now managed by Unity Catalog.

Grant read access

Unity Catalog controls access with SQL statements. To let another user or group read the table, you use the GRANT statement. In Unity Catalog, a user needs three things to query a table: permission to use the catalog, permission to use the schema, and permission to select from the table.

First, decide who needs access. You can grant to a user by their email address, or to a group. In this lesson, we use a placeholder email. Replace it with a real user or group in your workspace.

Grant USE CATALOG on the catalog so the user can see the catalog and its schemas.

GRANT USE CATALOG ON CATALOG my_catalog TO `[email protected]`;

Grant USE SCHEMA on the schema so the user can see the schema and its tables.

GRANT USE SCHEMA ON SCHEMA my_catalog.my_schema TO `[email protected]`;

Grant the SELECT privilege on the table.

GRANT SELECT ON TABLE my_catalog.my_schema.people_delta TO `[email protected]`;

The backticks around the email are required because the email contains special characters.

If you want to grant access to all tables in the schema, you can grant SELECT on the schema instead of on each table. The user still needs the USE SCHEMA privilege you granted above.

GRANT SELECT ON SCHEMA my_catalog.my_schema TO `[email protected]`;

Now the test user can run SELECT queries on the table, but they cannot modify it or create new tables in the schema.

Check permissions

To see who has access to a table, use the SHOW GRANTS statement.

SHOW GRANTS ON TABLE my_catalog.my_schema.people_delta;

This lists all privileges granted on the table. You should see the SELECT privilege for the test user, plus some default permissions for your own user and workspace admins.

You can also check permissions on the schema.

SHOW GRANTS ON SCHEMA my_catalog.my_schema;

This shows who can use the schema or create tables in it.

You can check permissions on the catalog as well.

SHOW GRANTS ON CATALOG my_catalog;

This shows the USE CATALOG privilege for the test user and any default permissions.

Revoke access

If you need to remove access later, use the REVOKE statement.

REVOKE SELECT ON TABLE my_catalog.my_schema.people_delta FROM `[email protected]`;

After this, the test user can no longer query the table. You can also revoke the USE SCHEMA and USE CATALOG privileges if the user should no longer see the schema or catalog.

REVOKE USE SCHEMA ON SCHEMA my_catalog.my_schema FROM `[email protected]`;
REVOKE USE CATALOG ON CATALOG my_catalog FROM `[email protected]`;

Clean up

If you created a catalog and schema only for practice, you can drop them. Dropping a schema removes all tables in it, but it does not delete the underlying Delta files. Dropping a catalog removes all schemas inside it.

To drop the table entry only, use DROP TABLE.

DROP TABLE my_catalog.my_schema.people_delta;

To drop the schema and all its tables, use DROP SCHEMA with the CASCADE option.

DROP SCHEMA my_catalog.my_schema CASCADE;

To drop the catalog, use DROP CATALOG.

DROP CATALOG my_catalog CASCADE;

Only drop these if you no longer need them. In a real project, you would keep the catalog and schema for your data.

Key takeaways

  • Unity Catalog is a central place to manage metadata and permissions for your data.
  • A table in Unity Catalog has a three-part name: catalog.schema.table.
  • Register an existing Delta table by creating a new table with the same location.
  • To read a table, a user needs USE CATALOG, USE SCHEMA, and SELECT privileges.
  • SHOW GRANTS lists the permissions on a catalog, schema, or table.

Try it

Create a new catalog and schema with names of your choice. Register your existing Delta table from lesson 4 in that catalog and schema. Grant USE CATALOG and USE SCHEMA to a test user or group, then grant SELECT on the table. Run SHOW GRANTS on the catalog, schema, and table to confirm the privileges. Then, using a different user account or by asking a colleague, try to query the table using the three-part name. The query should succeed. Finally, revoke the SELECT privilege and run SHOW GRANTS on the table to verify it is removed.

Official documentation

Ready to use this in a job?

Rolebound applies to matching roles for you.

Start now

Written with AI and checked automatically. Tools change, so a command may behave differently in the version you install. Spotted a mistake? Tell us.

    Cookies on Rolebound

    We use necessary cookies to sign you in and keep the site working — those cannot be turned off. We would also like to set analytics and advertising cookies so we can see which pages help people get interviews and which adverts brought them here. Nothing optional is set unless you say yes, and you can change your mind at any time from “Cookie settings” in the footer. Read our Privacy Policy.