数据对象特权Data object privileges

重要

此功能目前以公共预览版提供。This feature is in Public Preview.

利用 Azure Databricks 数据治理模型,你可以以编程方式授予、拒绝和撤消对来自 Spark SQL API 数据的访问权限。The Azure Databricks data governance model lets you programmatically grant, deny, and revoke access to your data from the Spark SQL API. 利用此模型,你可以控制对安全对象(如表、数据库、视图和函数)的访问权限。This model lets you control access to securable objects like tables, databases, views, and functions. 它还允许对从任意查询创建的派生视图设置权限,从而进行精细的访问控制(例如对表的特定子集)。It also allows for fine-grained access control (to a particular subset of a table, for example) by setting privileges on derived views created from arbitrary queries. Azure Databricks SQL 查询分析器在启用了表访问控制的群集上,在运行时强制执行这些访问控制策略。The Azure Databricks SQL query analyzer enforces these access control policies at runtime on clusters with table access control enabled.

本文将介绍组成 Azure Databricks 数据治理模型的权限、对象和所有权规则。This article describes the privileges, objects, and ownership rules that make up the Azure Databricks data governance model. 还将介绍如何授予、拒绝和撤消对象权限。It also describes how to grant, deny, and revoke object privileges.

要求Requirements

在管理员或对象所有者可以对数据对象授予、拒绝或撤消权限之前:Before an administrator or object owner can grant, deny, or revoke privileges on data objects:

数据治理模型Data governance model

本部分介绍 Azure Databricks 的数据治理模型。This section describes the Azure Databricks data governance model. 对数据对象的访问权限由权限控制。Access to data objects is governed by privileges.

权限Privileges

  • SELECT - 为对象提供读取访问权限SELECT – gives read access to an object
  • CREATE - 提供创建对象(例如数据库中的表)的功能CREATE – gives ability to create an object (for example, a table in a database)
  • MODIFY - 提供在对象中添加、删除和修改数据的功能MODIFY – gives ability to add, delete, and modify data to or from an object
  • READ_METADATA - 提供查看对象及其元数据的功能READ_METADATA – gives ability to view an object and its metadata
  • CREATE_NAMED_FUNCTION - 提供在现有的目录或数据库中创建命名 UDF 的功能CREATE_NAMED_FUNCTION – gives ability to create a named UDF in an existing catalog or database
  • ALL PRIVILEGES - 提供所有权限(转换为上述所有权限)ALL PRIVILEGES – gives all privileges (is translated into all the above privileges)

权限层次结构Privilege hierarchy

对象上的权限是分层的。Privileges on objects are hierarchical. 这意味着向 CATALOG 对象授予或拒绝某项权限会自动向其所有包含的数据库(以及所有表和视图)授予或拒绝该权限。This means that granting or denying a privilege on the CATALOG object automatically grants or denies the privilege to all of its contained databases (as well as all tables and views). 同样,向 DATABASE 授予或拒绝某项权限将自动向该数据库中所有表和视图授予或拒绝该权限。Similarly, granting or denying a privilege to a DATABASE automatically grants or denies the privilege to all tables and views in that database.

对象Objects

权限可应用于下列对象的类:Privileges can apply to the following classes of objects:

  • CATALOG - 控制对整个数据目录的访问。CATALOG - controls access to the entire data catalog.
  • DATABASE - 控制对数据库的访问。DATABASE - controls access to a database.
  • TABLE - 控制对托管表或外部表的访问。TABLE - controls access to a managed or external table.
  • VIEW - 控制对 SQL 视图的访问。VIEW - controls access to SQL views.
  • FUNCTION - 控制对命名函数的访问。FUNCTION - controls access to a named function.
  • ANONYMOUS FUNCTION - 控制对匿名函数或临时函数的访问。ANONYMOUS FUNCTION - controls access to anonymous or temporary functions.
  • ANY FILE - 控制对基础文件系统的访问。ANY FILE - controls access to the underlying filesystem.

对象所有权Object ownership

在群集上启用表 ACL 后,创建数据库、表、视图或函数的用户将成为其所有者。When table ACLs are enabled on a cluster, a user who creates a database, table, view, or function becomes its owner. 所有者被授予所有权限,并且可以向其他用户授予权限。The owner is granted all privileges and can grant privileges to other users.

所有权决定是否可以将派生对象的权限授予其他用户。Ownership determines whether or not you can grant privileges on derived objects to other users. 例如,假设用户 A 拥有表 T,并授予用户 B 对表 T 的 SELECT 权限。即使用户 B 可以从表 T 中进行选择,用户 B 也不能向用户 C 授予对表 T 的 SELECT 权限,因为用户 A 仍是基础表 T 的所有者。此外,用户 B 不能仅通过在表 T 上创建视图 V,并向用户 C 授予对该视图的权限来规避此限制。当 Azure Databricks 检查用户 C 访问视图 V 的权限时,还会检查 V 和基础表 T 的所有者是否相同。For example, suppose user A owns table T and grants user B SELECT privilege on table T. Even though user B can select from table T, user B cannot grant SELECT privilege on table T to user C, because user A is still the owner of the underlying table T. Furthermore, user B cannot circumvent this restriction simply by creating a view V on table T and granting privileges on that view to user C. When Azure Databricks checks for privileges for user C to access view V, it also checks that the owner of V and underlying table T are the same. 如果所有者不相同,则用户 C 还必须对基础表 T 具有 SELECT 权限。If the owners are not the same, user C must also have SELECT privileges on underlying table T.

在群集上禁用表 ACL 时,创建数据库、表、视图或函数时不会注册所有者。When table ACLs are disabled on a cluster, an owner is not registered when a database, table, view, or function is created. 若要测试某个对象是否具有所有者,请运行 SHOW GRANT ON <object-name>To test if an object has an owner, run SHOW GRANT ON <object-name>. 如果未看到带有 ActionType OWN 的条目,则该对象没有所有者。If you do not see an entry with ActionType OWN, the object does not have an owner.

为对象分配所有者Assign owner to object

管理员可以使用 ALTER <object> OWNER TO@.com 命令为对象分配所有者:An administrator can assign an owner to an object using the ALTER <object> OWNER TO@.com command:

ALTER DATABASE <database-name> OWNER TO `<user-name>@<user-domain>.com`
ALTER TABLE <table-name> OWNER TO `<user-name>@<user-domain>.com`
ALTER VIEW <view-name> OWNER TO `<user-name>@<user-domain>.com`

用户和组Users and groups

管理员和所有者可以向使用组 API 创建的用户和组授予权限。Administrators and owners can grant privileges to users and groups created using the Groups API. 每个用户都通过其在 Azure Databricks 中的用户名中唯一标识(通常映射到其电子邮件地址)。Each user is uniquely identified by their username in Azure Databricks (which typically maps to their email address).

备注

必须将用户规范括在反引号 (``) 中,而不是单引号 (‘’) 中。You must enclose user specifications in backticks (``), not single quotes (‘’).

操作和权限Operations and privileges

下表将 SQL 操作映射到执行该操作所需的权限或角色:The following table maps SQL operations to the privilege or role required to perform that operation:

操作/权限或角色Operation / Privilege or role SELECT CREATE MODIFY READ_METADATA CREATE_NAMED_FUNCTION 所有者Owner 管理员Admin
CREATE DATABASECREATE DATABASE xx xx xx
CREATE TABLECREATE TABLE xx xx xx
CREATE VIEWCREATE VIEW xx xx xx
CREATE FUNCTIONCREATE FUNCTION xx xx xx
ALTER DATABASEALTER DATABASE xx xx
ALTER TABLEALTER TABLE xx xx
ALTER VIEWALTER VIEW xx xx
DROP DATABASEDROP DATABASE xx xx
DROP TABLEDROP TABLE xx xx
DROP VIEWDROP VIEW xx xx
.DROP FUNCTIONDROP FUNCTION xx xx
DESCRIBE TABLEDESCRIBE TABLE xx xx xx
EXPLAINEXPLAIN xx xx xx
DESCRIBE HISTORYDESCRIBE HISTORY xx xx
SELECTSELECT xx xx xx
INSERTINSERT xx xx xx
UPDATEUPDATE xx xx xx
MERGE INTOMERGE INTO xx xx xx
DELETE FROMDELETE FROM xx xx xx
TRUNCATE TABLETRUNCATE TABLE xx xx xx
OPTIMIZEOPTIMIZE xx xx xx
VACUUMVACUUM xx xx xx
FSCK REPAIR TABLEFSCK REPAIR TABLE xx xx xx
MSCKMSCK xx xx
GRANTGRANT xx xx
DENYDENY xx xx
REVOKEREVOKE xx xx

重要

使用表 ACL 时,DROP TABLE 语句区分大小写。When using table ACL, DROP TABLE statements are case sensitive. 如果表名称为小写,且 drop table 使用混合或大写形式引用表名称,则 DROP TABLE 语句将失败。If a table name is lower case and the drop table references the table name using mixed or upper case, the DROP TABLE statement will fail.

管理对象权限Manage object privileges

可以使用 GRANTDENYREVOKE 操作来管理对象权限。You use the GRANT, DENY, and REVOKE operations to manage object privileges.

备注

  • 对象的所有者或管理员可以执行 GRANTDENYREVOKE 操作。An owner or an administrator of an object can perform GRANT, DENY, and REVOKE operations. 但是,管理员不能拒绝所有者的权限或撤消所有者的权限。However, an administrator cannot deny privileges to or revoke privileges from an owner.
  • 不是所有者或管理员的主体只能在授予了所需的权限后才可以执行操作。A principal that’s not an owner or administrator can perform an operation only if the required privilege has been granted.
  • 若要为所有用户授予、拒绝或撤消权限,请在 TO 之后指定关键字 usersTo grant, deny, or revoke a privilege for all users, specify the keyword users after TO.

示例Examples

GRANT SELECT ON DATABASE <database-name> TO `<user>@<domain-name>`
GRANT SELECT ON ANONYMOUS FUNCTION TO `<user>@<domain-name>`
GRANT SELECT ON ANY FILE TO `<user>@<domain-name>`

SHOW GRANT `<user>@<domain-name>` ON DATABASE <database-name>

DENY SELECT ON <table-name> TO `<user>@<domain-name>`

REVOKE ALL PRIVILEGES ON DATABASE default FROM `<user>@<domain-name>`
REVOKE SELECT ON <table-name> FROM `<user>@<domain-name>`

GRANT SELECT ON ANY FILE TO users

常见问题解答 (FAQ)Frequently asked questions (FAQ)

如何为所有用户授予、拒绝或撤消权限How do I grant, deny, or revoke a privilege for all users

TOFROM 之后指定关键字 usersSpecify the keyword users after TO or FROM. 例如:For example:

GRANT SELECT ON ANY FILE TO users

我创建了一个对象,但现在不能对其进行查询、删除或修改。I created an object but now I can’t query, drop, or modify it.

出现此错误的原因可能是你在未启用表 ACL 的群集上创建了该对象。This error can occur because you created that object on a cluster without table ACLs enabled. 在群集上禁用表 ACL 时,创建数据库、表或视图时不会注册所有者。When table ACLs are disabled on a cluster, owners are not registered when a database, table, or view is created. 管理员必须使用以下命令为对象分配所有者:An admin must assign an owner to the object using the following command:

ALTER [DATABASE|TABLE|VIEW] <object-name> OWNER TO `<user-name>@<user-domain>.com`;

如何授予全局和本地临时视图的权限?How do I grant privileges on global and local temporary views?

很遗憾,不支持全局和本地临时视图的权限。Unfortunately privileges on global and local temporary views are not supported. 本地临时视图仅在同一会话中可见,在 global_temp 数据库中创建的视图对共享群集的所有用户可见。Local temporary views are visible only within the same session, and views created in the global_temp database are visible to all users sharing a cluster. 但是,会强制执行对任何临时视图所引用的基础表和视图的权限。However, privileges on the underlying tables and views referenced by any temporary views are enforced.

如何同时向用户或组授予多个表的权限?How do I grant a user or group privileges on multiple tables at once?

授予、拒绝或撤销语句一次只能应用于一个对象。A grant, deny, or revoke statement can be applied to only one object at a time. 建议通过数据库向主体组织并授予多个表的权限。The recommended way to organize and grant privileges on multiple tables to a principal is via databases. 如果授予数据库的主体 SELECT 权限,则会向该数据库中的所有表和视图隐式授予该主体 SELECT 权限。Granting a principal SELECT privilege on a database implicitly grants that principal SELECT privileges on all tables and views in that database. 例如,如果数据库 D 具有表 t1 和 t2,并且管理员发出以下 GRANT 命令:For example, if a database D has tables t1 and t2, and an admin issues the following GRANT command:

GRANT SELECT ON DATABASE D TO `<user>@<domain-name>`

主体 <user>@<domain-name> 可以从表 t1 和 t2 中进行选择,还可以从将来在数据库 D 中创建的任何表和视图进行选择。The principal <user>@<domain-name> can select from tables t1 and t2, as well as any tables and views created in database D in the future.

如何向用户授予除一个表之外的所有表的权限?How do I grant a user privileges on all tables except one?

向数据库授予 SELECT 权限,然后拒绝要限制访问的特定表的 SELECT 权限。You grant SELECT privilege to the database and then deny SELECT privilege for the specific table you want to restrict access to.

GRANT SELECT ON DATABASE D TO `<user>@<domain-name>`
DENY SELECT ON TABLE D.T TO `<user>@<domain-name>`

主体 <user>@<domain-name> 可以从 D 中的所有表中选择(D.T 除外)。The principal <user>@<domain-name> can select from all tables in D except D.T.

用户对表 T 的视图具有 SELECT 权限,但当该用户尝试从该视图中 SELECT 时,他们会收到错误 User does not have privilege SELECT on tableA user has SELECT privileges on a view of table T, but when that user tries to SELECT from that view, they get the error User does not have privilege SELECT on table.

此常见错误可能是由以下任一原因所致:This common error can occur for one of the following reasons:

  • 表 T 没有注册所有者,因为它是使用禁用了表 ACL 的群集创建的。Table T has no registered owner because it was created using a cluster for which table ACLs are disabled.
  • 表 T 视图上的 SELECT 权限的授予者不是表 T 的所有者,或者用户在表 T 上也没有 SELECT 权限。The grantor of the SELECT privilege on a view of table T is not the owner of table T or the user does not also have select SELECT privilege on table T.

假设有一个表 T 由 A 拥有。A 拥有 T 上的视图 V1,B 拥有 T 上的视图 V2。Suppose there is a table T owned by A. A owns view V1 on T and B owns view V2 on T.

  • 如果 A 已授予对视图 V1 的 SELECT 权限,则用户可以在 V1 上选择。A user can select on V1 when A has granted SELECT privileges on view V1.
  • 如果 A 已授予对表 T 的 SELECT 权限,并且 B 已授予对 V2 的 SELECT 权限,则用户可以在 V2 上选择。A user can select on V2 when A has granted SELECT privileges on table T and B has granted SELECT privileges on V2.

对象所有权部分中所述,这些条件确保只有对象的所有者才能向其他用户授予对该对象的访问权限。As described in the Object ownership section, these conditions ensure that only the owner of an object can grant other users access to that object.

我尝试在已启用表 ACL 的群集上运行 sc.parallelize,但失败了。I tried to run sc.parallelize on a cluster with table ACLs enabled and it fails.

在已启用表 ACL 的群集上,只能使用 Spark SQL 和 Python 数据帧 API。On clusters with table ACLs enabled you can use only the Spark SQL and Python DataFrame APIs. 出于安全原因,不允许使用 RDD API,因为 Azure Databricks 无法检查和授权 RDD 中的代码。The RDD API is disallowed for security reasons, since Azure Databricks does not have the ability to inspect and authorize code within an RDD.