Structured Data Access Controls

New in version runtime-3.1.

Databricks supports fine-grained access control via the Spark SQL interface. In this context, access can be restricted for any securable objects, e.g., tables, views, databases or functions. Fine-grained level access control (i.e. on rows or columns matching specific conditions) can be accomplished via access control on derived views that can contain arbitrary queries. These access control policies are enforced by the Databricks SQL query analyzer at runtime. Fine-grained access control can be enabled by setting a custom spark config on cluster creation page:

spark.databricks.acl.sqlOnly true

Privileges

  • SELECT privilege – gives read access to an object.
  • CREATE privilege – gives ability to create an object (e.g., a table in a database).
  • MODIFY privilege – gives ability to add/delete/modify data to/from an object (e.g., a table).
  • READ_METADATA privilege – gives ability to view an object and its metadata.
  • CREATE_NAMED_FUNCTION privilege – gives ability to create a named UDF in an existing catalog or database.
  • ALL PRIVILEGES – gives all privileges (gets translated into all the above privileges).

Objects

These privileges apply to the following class of objects:

  • CATALOG - controls access to the entire data catalog.
  • DATABASE - controls access to a database.
  • TABLE - controls access to a managed or external table.
  • VIEW - controls access to SQL views.
  • FUNCTION - controls access to a named function.
  • ANONYMOUS FUNCTION - controls access to anonymous or temporary functions.
  • ANY FILE - controls access to the underlying filesystem.

Objects Ownership

For certain actions, the ownership of the object (table/view/database) determines if you are authorized to perform the action. The user who creates the table, view or database becomes its owner. In the case of tables and views, the owner gets all the privileges with grant option.

Users & Groups

Privileges can be granted to users or groups that are created via the groups API. Each user is uniquely identified via their username (that typically maps to their email address) in Databricks. Users that are workspace administrators in Databricks belong to a special admin role and can also access objects that they haven’t been given explicit access to.

Privilege Hierarchy

Privileges on objects are hierarchical. This means that granting a privilege on the entire CATALOG automatically grants to it all the databases (and tables/views). Similarly, granting a privilege to a given DATABASE automatically grants it to all tables and views in that database.

Managing Object Privileges

The following commands can be used to manage the object privileges:

GRANT

GRANT
  privilege_type [, privilege_type ] ...
  ON (CATALOG | DATABASE db_name | [TABLE] table_name | [VIEW] view_name | [FUNCTION] function_name | ANONYMOUS FUNCTION | ANY FILE)
  TO user [, user] ...

privilege_type
  : SELECT | CREATE | MODIFY | READ_METADATA | CREATE_NAMED_FUNCTION | ALL PRIVILEGES

REVOKE

REVOKE
  privilege_type [, privilege_type ] ...
  ON (CATALOG | DATABASE db_name | [TABLE] table_name | [VIEW] view_name | [FUNCTION] function_name | ANONYMOUS FUNCTION | ANY FILE)
  FROM user [, user] ...

privilege_type
  : SELECT | CREATE | MODIFY | READ_METADATA | CREATE_NAMED_FUNCTION | ALL PRIVILEGES

Examples

GRANT SELECT ON table_name to `user1@databricks.com`;
GRANT SELECT ON ANONYMOUS FUNCTION to `user2@databricks.com`;
GRANT SELECT ON ANY FILE to `user3@databricks.com`;
REVOKE ALL PRIVILEGES ON DATABASE default FROM `user4@databricks.com`

Note

We do not support an explicit DENY command for objects.

SHOW GRANT

SHOW GRANT [user] ON (CATALOG | DATABASE db_name | [TABLE] table_name | [VIEW] view_name | [FUNCTION] function_name | ANONYMOUS FUNCTION | ANY FILE)

Examples

SHOW GRANT `user1@databricks.com` ON DATABASE default

Fine-grained Access Control

Fine-grained level access control (i.e. on rows or columns matching specific conditions) can be accomplished by granting access on derived views that can contain arbitrary queries.

Examples

CREATE OR REPLACE VIEW view_name AS SELECT columnA, columnB FROM table_name WHERE columnC > 1000;
GRANT SELECT ON VIEW view_name to `user1@databricks.com`;

Privileges Required for SQL Operations

The following table roughly maps the privileges to various SQL operations:

Operations ↓ / Privilege → SELECT CREATE MODIFY READ_METADATA CREATE_NAMED_FUNCTION Ownership Admin
CREATE TABLE   X       X X
DROP TABLE     X     X X
DESCRIBE TABLE       X   X X
ALTER TABLE     X     X X
DROP TABLE     X     X X
CREATE VIEW   X       X X
DROP VIEW     X     X X
SELECT X         X X
CREATE FUNCTION         X X X
MSCK             X
CREATE DATABASE   X       X X
EXPLAIN       X   X X
DROP DATABASE     X     X X
GRANT           X X
REVOKE           X X