Set up a user to query a table
This article shows how an administrator can add a user, create a table containing 10 million people records from a Databricks dataset, and allow a user to access the table. Next, it shows how the user can run a query and view the query runtime details.
Important
Data access control is always enabled in Databricks SQL, which means that users must always be granted access to data.
Requirements
You must be a Databricks workspace admin.
Use the sidebar
You can access all of your Databricks assets using the sidebar. The sidebar’s contents depend on the selected persona: Data Science & Engineering, Machine Learning, or SQL.
By default, the sidebar appears in a collapsed state and only the icons are visible. Move your cursor over the sidebar to expand to the full view.
To change the persona, click the icon below the Databricks logo
, and select a persona.
To pin a persona so that it appears the next time you log in, click
next to the persona. Click it again to remove the pin.
Use Menu options at the bottom of the sidebar to set the sidebar mode to Auto (default behavior), Expand, or Collapse.
Step 1: Add a user
Use the sidebar persona switcher to select Data Science & Engineering.
Go to the admin console.
On the Users tab, click Add User.
Enter the user email ID. The examples in this article assume the user is
user@example.com
.Click Send invite.
Databricks sends a confirmation email with a temporary password. If the user does not receive the confirmation email within 5 minutes, ask the user to check their spam folder.
The user is added to the workspace.
If necessary, allow the the user to access Databricks SQL by selecting the Databricks SQL access checkbox. All users in new workspaces have this entitlement by default.
Note
If your workspace was enabled for Databricks SQL during the Public Preview—that is, before the week beginning September 7, 2021—users retain the entitlement assigned before that date, unless you change it. In other words, if a user did not have the Databricks SQL access entitlement during the Public Preview, they will not have it now unless an administrator gives it to them.
Click Confirm.
Step 2: Create a table of 10 million people
Use the sidebar persona switcher to select SQL.
Your landing page looks like this:
Click
New in the sidebar and select Query.
In the drop-box at the left, select a SQL warehouse, such as Starter Warehouse.
Paste the following in the SQL editor:
CREATE TABLE default.people10m OPTIONS (PATH 'dbfs:/databricks-datasets/learning-spark-v2/people/people-10m.delta')
This statement creates a Delta table using Delta Lake files stored in Databricks datasets.
Press Ctrl/Cmd + Enter or click the Execute button. The query will return
No data was returned.
To refresh the schema, click the
button at the bottom of the schema browser.
Type
peo
in the text box to the right of the schema. The schema browser displays the new table.
Step 3: Configure access to the default.people10m
table
Enable the user you created in Step 1 to access the default.people10m
table you created in Step 3.
You can configure access using Data Explorer or SQL editor.
Data explorer
Click the
Data in the sidebar.
In the drop-down list at the top right, select a SQL warehouse, such as Starter Warehouse. The
default
schema is selected. The schema comment and owner display.Type
peo
in the text box following thedefault
schema. Data Explorer displays thepeople10m
table.Click the Permissions tab.
Click the Grant button.
Enter
user@example.com
.Select the USAGE checkbox.
Click OK.
Click the
people10m
table.Click the Permissions tab.
Click the Grant button.
Enter
user@example.com
.Click the SELECT and READ_METADATA checkboxes.
Click OK.
SQL editor
Click
New in the sidebar and select Query.
In the drop-down box at the left, select a SQL warehouse, such as Starter Warehouse.
Enter the following queries one by one:
GRANT USAGE ON SCHEMA default TO `user@example.com`; GRANT SELECT, READ_METADATA ON TABLE default.people10m TO `user@example.com`; SHOW GRANTS `user@example.com` ON TABLE default.people10m;
After each query, press Ctrl/Cmd + Enter or click the Execute button. After the last query, it should display:
+------------------+---------------+------------+-----------------------+ | Principal | ActionType | ObjectType | ObjectKey | +------------------+---------------+------------+-----------------------+ | user@example.com | READ_METADATA | TABLE | `default`.`people10m` | +------------------+---------------+------------+-----------------------+ | user@example.com | SELECT | TABLE | `default`.`people10m` | +------------------+---------------+------------+-----------------------+ | user@example.com | USAGE | SCHEMA | default | +------------------+---------------+------------+-----------------------+
Step 6: View query history
Click
Query History in the sidebar.
Click a string in the Query column to display query details:
Next steps
To learn how to enable access to your own data and tables, see Access control.
To walk through the process to enable access, go to the Get Started pane on https://<databricks-instance>/sql
, replacing <databricks-instance>
with the Databricks workspace instance name, for example dbc-a1b2345c-d6e7.cloud.databricks.com
.