表创建失败,出现安全异常Table creation fails with security exception

问题Problem

您尝试使用启用了表 acl的群集来创建表,但发生了以下错误:You attempt to create a table using a cluster that has Table ACLs enabled, but the following error occurs:

Error in SQL statement: SecurityException: User does not have permission SELECT on any file.

原因Cause

如果你不是管理员,并且你没有足够的特权来创建表,则已启用表 ACL 的群集会出现此错误。This error occurs on a Table ACL-enabled cluster if you are not an administrator and you do not have sufficient privileges to create a table.

例如,在笔记本中,尝试使用位于 Azure Blob 存储上的 Parquet 数据源创建表:For example, in your notebook you attempt to create a table using a Parquet data source located on Azure Blob Storage:

CREATE TABLE mytable
  USING PARQUET
  OPTIONS (PATH='wasbs://my-container@my-storage-account.blob.core.windows.net/my-table')

解决方案Solution

应使用以下选项之一,要求管理员向你授予对 blob 存储文件系统的访问权限You should ask your administrator to grant you access to the blob storage filesystem, using either of the following options. 如果管理员无法授予您对数据对象的访问权限,则必须要求管理员为您创建表。If an administrator cannot grant you access to the data object, you’ll have to ask an administrator to make the table for you.

  • 如果要使用 CTAS (CREATE TABLE AS SELECT) 语句创建表,则管理员应授予您 SELECT 对文件系统的特权:If you want to use a CTAS (CREATE TABLE AS SELECT) statement to create the table, the administrator should grant you SELECT privileges on the filesystem:

    GRANT SELECT ON ANY FILE TO `user1`
    

    示例 CTAS 语句:Example CTAS statement:

    CREATE TABLE mytable
          AS SELECT * FROM parquet.`wasbs://my-container@my-storage-account.blob.core.windows.net/my-table`
    
  • 如果要使用 CTOP (CREATE TABLE OPTIONS PATH) 语句来创建表,则管理员除了授予外,还必须提升您的特权 MODIFY SELECTIf you want to use a CTOP (CREATE TABLE OPTIONS PATH) statement to make the table, the administrator must elevate your privileges by granting MODIFY in addition to SELECT.

    GRANT SELECT, MODIFY ON ANY FILE TO `user1`
    

    示例 CTOP 语句:Example CTOP statement:

    CREATE TABLE mytable
       USING PARQUET
       OPTIONS (PATH='wasbs://my-container@my-storage-account.blob.core.windows.net/my-table')
    

重要

务必要了解对文件系统授予权限时的安全隐患 ANY FILEIt is important to understand the security implications of granting ANY FILE permissions on a filesystem. 你应仅授予 ANY FILE 特权用户。You should only grant ANY FILE to privileged users. 在群集上具有较低权限的用户不应通过引用实际存储位置来访问数据。Users with lower privileges on the cluster should never access data by referencing an actual storage location. 相反,他们应从特权用户创建的表中访问数据,从而确保强制实施表 ACL。Instead, they should access data from tables that are created by privileged users, thus ensuring that Table ACLS are enforced.

此外,如果 Azure Databricks 根和数据存储桶中的文件可供群集访问,并且用户具有 MODIFY 权限,则管理员应锁定 DBFS 根。In addition, if files in the Azure Databricks root and data buckets are accessible by the cluster and users have MODIFY privileges, the admin should lock down the DBFS root.

授予以上所述的数据访问权限不会取代任何基础用户权限或 Blob 存储容器访问控制。Granting the data access privileges described above does not supersede any underlying user permissions or Blob Storage container access control. 例如,如果执行的是 user1 "" 之类的 grant 语句, GRANT SELECT, MODIFY ON ANY FILE TO 但附加到群集的用户权限显式拒绝对目标容器的读取,则该 GRANT 语句不会使容器或容器中的对象突然被读取。For example, if a grant statement like GRANT SELECT, MODIFY ON ANY FILE TO user1`` is executed but a user permission attached to the cluster explicitly denies reads to the target container, then the GRANT statement will not make the container or the objects within the container suddenly readable.