表创建失败,出现安全异常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.chinacloudapi.cn/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.chinacloudapi.cn/my-table`
    
  • 如果你要使用 CTOP (CREATE TABLE OPTIONS PATH) 语句来创建表,则管理员必须通过授予 MODIFYSELECT 来提升你的权限。If 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.chinacloudapi.cn/my-table')
    

重要

请务必了解在文件系统上授予 ANY FILE 权限的安全隐患。It is important to understand the security implications of granting ANY FILE permissions on a filesystem. 只应向特权用户授予 ANY FILEYou 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 根目录和数据 bucket 中的文件可供群集访问,并且用户具有 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. 例如,如果执行了 GRANT SELECT, MODIFY ON ANY FILE TO user1`` 之类的 grant 语句,但附加到群集的用户权限显式拒绝对目标容器进行读取,则 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.