Tutorial: Create your first table and grant privileges in Unity Catalog

This article provides a quick walkthrough of creating a table and granting privileges in Unity Catalog. It is intended for users but may also be of interest to admins who are newly responsible for Unity Catalog management.

To get started immediately, you can go straight to the first section of this article. If you want to familiarize yourself with basic concepts first, see What is Unity Catalog?.

If you are an admin who is responsible for setting up Unity Catalog and managing it for your organization, see Set up and manage Unity Catalog.

Before you begin

In order to perform the tasks described in this article, you must have:

  • A Databricks workspace that is enabled for Unity Catalog. See Set up and manage Unity Catalog.

  • Access to compute that uses a Unity Catalog-compliant access mode. SQL warehouses are all Unity Catalog-compliant. To learn about Unity Catalog-compliant clusters, see Access modes.

  • Appropriate privileges on Unity Catalog objects. These are listed at the beginning of each task.

  • Other users and groups added to the workspace. See Manage users, service principals, and groups.

Create your first table and manage permissions (for workspaces that were enabled automatically)

Use this tutorial if your workspace was enabled for Unity Catalog automatically. See Determine if your workspace was enabled for Unity Catalog automatically.

Unity Catalog enables you to define access to tables declaratively using SQL or the Databricks Explorer UI.

In this example, you’ll run a notebook that creates a table named department in the workspace catalog and default schema (database). This catalog and schema are created automatically for all workspaces that were enabled for Unity Catalog automatically.

Permissions required: USE CATALOG privilege on the workspace catalog and USE SCHEMA on the <workspace-catalog>.default schema. All workspace users are granted these privileges when the workspace is created. No other permissions are required to complete this example apart from those that you grant as you run it.

  1. Create a notebook and attach it to a Unity Catalog-compliant cluster or SQL warehouse. See Before you begin.

    Select SQL as your notebook language.

  2. Add the following commands to the notebook and run them:

    GRANT CREATE TABLE ON SCHEMA <workspace-catalog>.default TO `<user>@<domain>.com`;
    

    Replace <workspace-catalog> with the name of your workspace catalog and <user>@<domain>.com with your Databricks username. You must enclose the username with backticks ( ` ` ). To find the workspace catalog name, click Catalog icon Catalog and browse for the catalog whose name uses the format <workspace-name>.

    CREATE TABLE IF NOT EXISTS default.department
    (
      deptcode   INT,
      deptname  STRING,
      location  STRING
    );
    
    INSERT INTO default.department VALUES
      (10, 'FINANCE', 'EDINBURGH'),
      (20, 'SOFTWARE', 'PADDINGTON');
    

    Note

    You don’t need to provide the complete three-level namespace (<workspace-catalog>.default.department) because the workspace catalog is defined as the default catalog for the workspace and is therefore assumed. Any time you reference a catalog other than the default catalog, you must specify the catalog in the statement or declare it with a USE CATALOG <catalog> statement.

    You now have a table in Unity Catalog.

  3. Find the new table in Catalog Explorer.

    In the sidebar, click Catalog icon Catalog, then browse or search for the workspace catalog (<workspace-name>) and the default schema, where you’ll find the department table.

    Use Catalog Explorer to find a table

    Notice that you don’t need a running cluster or SQL warehouse to browse data in Catalog Explorer.

  4. Grant permissions on the table.

    As the original table creator, you’re the table owner, and you can grant other users permission to read or write to the table. You can even transfer ownership, but we won’t do that here.

    On the table page in Catalog Explorer, go to the Permissions tab and click Grant.

    On the Grant on dialog:

    1. Select the users and groups you want to give permission to. In this example, we use a group called data-consumers.

    2. Select the privileges you want to grant. For this example, assign the SELECT (read) privilege and click Grant.

    For more information about the Unity Catalog privileges and permissions model, see Manage privileges in Unity Catalog.

    You can also grant those permissions using the following SQL statement in a Databricks notebook or the Databricks SQL query editor:

    GRANT SELECT ON default.department TO `data-consumers`;
    

Create your first table and manage permissions (for workspaces that were enabled manually)

Use this tutorial if your workspace was enabled for Unity Catalog manually. This is true of all workspaces created before November 8, 2023. See Automatic enablement of Unity Catalog.

Unity Catalog enables you to define access to tables declaratively using SQL or the Databricks Explorer UI.

In this example, you’ll run a notebook that creates a table named department in the main catalog and default schema (database). This catalog and schema are created automatically for all metastores.

You can also try running an example notebook that performs the same tasks.

Permissions required: The USE CATALOG privilege on the main catalog and USE SCHEMA privilege on the main.default schema. All account users have these privileges by default. No other permissions are required to complete this example apart from those that you grant as you run it.

  1. Create a notebook and attach it to a cluster that supports Unity Catalog. See Before you begin.

    Select SQL as your notebook language.

  2. Add the following commands to the notebook and run them:

    GRANT CREATE TABLE ON SCHEMA main.default TO `<user>@<domain>.com`;
    

    Replace <user>@<domain>.com with your Databricks username. You must enclose the username with backticks ( ` ` ).

    CREATE TABLE IF NOT EXISTS main.default.department
    (
      deptcode   INT,
      deptname  STRING,
      location  STRING
    );
    
    INSERT INTO main.default.department VALUES
      (10, 'FINANCE', 'EDINBURGH'),
      (20, 'SOFTWARE', 'PADDINGTON');
    

    You now have a table in Unity Catalog.

  3. Find the new table in Catalog Explorer.

    In the sidebar, click Catalog icon Catalog, then use the browser (or search) to find the main catalog and the default schema, where you’ll find the department table.

    Use Catalog Explorer to find a table

    Notice that you don’t need a running cluster or SQL warehouse to browse data in Catalog Explorer.

  4. Grant permissions on the table.

    As the original table creator, you’re the table owner, and you can grant other users permission to read or write to the table. You can even transfer ownership, but we won’t do that here.

    On the table page in Catalog Explorer, go to the Permissions tab and click Grant.

    On the Grant on dialog:

    1. Select the users and groups you want to give permission to. In this example, we use a group called data-consumers.

    2. Select the privileges you want to grant. For this example, assign the SELECT privilege and click Grant.

    For more information about the Unity Catalog privileges and permissions model, see Manage privileges in Unity Catalog.

    You can also grant those permissions using the following SQL statement in a Databricks notebook or the Databricks SQL query editor:

    GRANT SELECT ON main.default.department TO `data-consumers`;
    
  5. Run one of the example notebooks that follow for a more detailed walkthrough that includes catalog and schema creation, a summary of available privileges, a sample query, and more.

Example notebooks: Create your first catalog, schema, and table

You can use the following example notebooks to create a catalog, schema, and table, as well as manage permissions on each.

Create and manage a Unity Catalog table using Python notebook

Open notebook in new tab

Create and manage a Unity Catalog table using SQL notebook

Open notebook in new tab