Admin: Set up a user to query a table

This quickstart 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

  • Your Databricks account must be on the Premium plan.

  • You must have a Databricks subscription and workspace. You can use an existing workspace or create a new one. For information about setting up a suscription and creating workspaces, see Get started: Free trial & setup.

  • You must be a Databricks workspace admin.

WARNING: SQL warehouses won’t start if you do not provision the required amount of CPU and storage resources. See Compute Engine API. If needed, you can increase the resource quotas to support your use of SQL warehouses. See Review and increase quotas. For information about workspace cost, see cost per workspace.

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 Databricks logo, and select a persona.

    change persona
  • To pin a persona so that it appears the next time you log in, click pin persona 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

Step 2: Create a table of 10 million people

  1. Use the sidebar persona switcher to select SQL.

    Your landing page looks like this:

    Admin landing page
  2. Click New Icon New in the sidebar and select Query.

  3. In the drop-box at the left, select a SQL warehouse, such as Starter Warehouse.

  4. 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.

  5. Press Ctrl/Cmd + Enter or click the Execute button. The query will return No data was returned.

  6. To refresh the schema, click the Refresh Schema button at the bottom of the schema browser.

  7. Type peo in the text box to the right of the schema. The schema browser displays the new table.

    Schema browser

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

  1. Click the Data Icon Data in the sidebar.

  2. 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.

  3. Type peo in the text box following the default schema. Data Explorer displays the people10m table.

  4. Click the Permissions tab.

  5. Click the Grant button.

    Default db grant
  6. Enter user@example.com.

  7. Select the USAGE checkbox.

  8. Click OK.

  9. Click the people10m table.

  10. Click the Permissions tab.

  11. Click the Grant button.

    People table grant
  12. Enter user@example.com.

  13. Click the SELECT and READ_METADATA checkboxes.

  14. Click OK.

SQL editor

  1. Click New Icon New in the sidebar and select Query.

  2. In the drop-down box at the left, select a SQL warehouse, such as Starter Warehouse.

  3. 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 5: Generate a query

Complete the user quickstart.

Step 6: View query history

  1. Click History Icon Query History in the sidebar.

  2. Click a string in the Query column to display query details:

    Query history details

Next steps

To learn how to enable access to your own data and tables, see Databricks SQL security model and data access overview.

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 1234567890123456.7.gcp.databricks.com.