User does not have permission SELECT on ANY File

Regular users cannot create tables without permission when access control is enabled.

Written by sivaprasad.cs

Last published at: May 16th, 2023

Problem

You are trying to create an external hive table, but keep getting a User does not have permission SELECT on any file error message.

java.lang.SecurityException: User does not have permission SELECT on any file.


Table access control (AWS | Azure | GCP) is enabled your cluster and you are not an admin.

Cause

The Databricks SQL query analyzer enforces access control policies at runtime on Databricks clusters with table access control enabled as well as all SQL warehouses.

When table access control is enabled on a cluster, the user must have specific permission to access a table in order to be able to read the table.

The only users who can bypass table access control are Databricks admins.

Solution

An admin must grant SELECT permission on files so the selected user can create a table.

Delete

Warning

Users granted access to ANY FILE can bypass the restrictions put on the catalog, schemas, tables, and views by reading from the filesystem directly.

Review the Data object privileges (AWS | Azure | GCP) documentation for more information.

Delete

Info

The following steps must be run as an Admin.

Admins can also grant permissions to groups instead of users.

  1. Start the cluster.
  2. Open a notebook.
  3. Run the following to grant SELECT permission on any file to the specified user. 
    %sql
GRANT SELECT ON ANY FILE TO `<user@domain-name>`