Databricks SQL data access control

This article describes how data object owners manage access to data.

For a general overview of how to enable access to data, see Access control.

Important

  • Data access control is always enabled in Databricks SQL even if table access control is not enabled in the Databricks Data Science & Engineering workspace. Before a user can access any schemas, tables, and views, the user must first be granted access using data access commands.

  • If table access control is enabled in Databricks Data Science & Engineering and you have already specified ACLS (granted and denied privileges) in the workspace, those ACLs are respected in Databricks SQL.

You can manage ownership and permissions by using Data Explorer or by running security commands in the SQL editor.

Note

You cannot manage ownership or permissions for schemas or tables in the samples catalog. This catalog is provided read-only to users in all workspaces.

Manage ownership and permissions in Data Explorer

Manage data object ownership

You must be a Databricks admin to view and manage data object ownership.

If you do not have permission to see the owner, the Owner field displays Obscured schema owner.

If the data object has no owner, the Owner field displays No object owner.

Edit schema ownership

  1. Click Data Icon Data in the sidebar.

  2. Select a running SQL warehouse in the drop-down list at the top right. The default schema is selected. The schema comment and owner displays and the selected Details tab shows the schema location and properties.

    Select warehouse

  3. Select the targeted schema under Data Explorer.

  4. In the Owner field, click the Edit Owner Icon.

  5. In the Set owner for <schema-name> dialog, click Down Arrow Icon, and select an owner.

Edit table ownership

  1. Click Data Icon Data in the sidebar.

  2. Select a running SQL warehouse in the drop-down list at the top right. The default schema is selected. The schema comment and owner displays and the selected Details tab shows the schema location and properties.

    Select warehouse

  3. Select the targeted schema under Data Explorer.

  4. Click the Filter tables field. Optionally type a string to filter the tables.

    Filter tables

  5. Select the targeted table.

  6. In the Owner field, click the Edit Owner Icon.

  7. In the Set owner for <schema-name.table-name> dialog, click Down Arrow Icon, and select an owner.

Manage data object permissions

You must be a Databricks admin or the data object owner to view and manage data object permissions.

Manage schema permissions

  1. Click Data Icon Data in the sidebar.

  2. Select a running SQL warehouse in the drop-down list at the top right. The default schema is selected. The schema comment and owner displays and the selected Details tab shows the schema location and properties.

    Select warehouse

  3. Select the targeted schema under Data Explorer.

  4. Click the Permissions tab.

  5. Grant or revoke privileges.

    • Grant

      1. Click Grant.

      2. Optionally type a string to filter the principals and select a principal.

      3. Select the checkboxes next to the privileges to grant.

      4. Click OK.

    • Revoke

      1. Select the checkbox next to a principal.

      2. Click Revoke.

Manage table permissions

  1. Click Data Icon Data in the sidebar.

  2. Select a running SQL warehouse in the drop-down list at the top right. The default schema is selected. The schema comment and owner displays and the selected Details tab shows the schema location and properties.

    Select warehouse

  3. Select the targeted schema under Data Explorer.

  4. Click the Filter tables field. Optionally type a string to filter the tables.

    Filter tables

  5. Select the targeted table.

  6. Click the Permissions tab.

  7. Grant or revoke privileges.

    • Grant

      1. Click Grant.

      2. Optionally type a string to filter the principals and select a principal.

      3. Select the checkboxes next to the privileges to grant.

      4. Click OK.

    • Revoke

      1. Select the checkbox next to a principal.

      2. Click Revoke.

Security commands

Data object owners apply the SQL GRANT, DENY, REVOKE, and SHOW GRANTS commands to manage access to data objects from users and groups. For a command reference, see Security statements.

To run SHOW GRANTS [<user>] <object> you must be either:

  • A Databricks SQL administrator or the owner of <object>.

  • The user specified in [<user>].

For requirements for using these commands and a detailed explanation of the data governance model, see Data object privileges.

Example

To enable the user user@example.com to access all tables in the default schema, invoke the following commands:

REVOKE ALL PRIVILEGES ON SCHEMA default FROM `user@example.com`;

GRANT USAGE, SELECT, READ_METADATA ON SCHEMA default TO `user@example.com`;

SHOW GRANTS `user@example.com` ON SCHEMA default;

+------------------+---------------+------------+-----------+
| principal        | ActionType    | ObjectType | ObjectKey |
+------------------+---------------+------------+-----------+
| user@example.com | READ_METADATA | SCHEMA     | default   |
+------------------+---------------+------------+-----------+
| user@example.com | SELECT        | SCHEMA     | default   |
+------------------+---------------+------------+-----------+
| user@example.com | USAGE         | SCHEMA     | default   |
+------------------+---------------+------------+-----------+

When you run these commands in the SQL editor, you should see:

Show grants