How to edit permissions of Databricks queries with REST API?

Question

I am trying to give CAN_MANAGE permission to other users for a given set of SQL queries on Azure Databricks.

Browsing Databricks REST API reference, under the Databricks SQL > ACL / Permissions section there are both Get object ACL and Set object ACL endpoints available.

I am able to use the Get object ACL to return the current permissions however, using the Set object ACL does not work.

After further investigation I find that the docs state that:

The SQL Permissions API is similar to the endpoints of the Permissions/Set. However, this exposes only one endpoint, which gets the Access Control List for a given object. You cannot modify any permissions using this API.

There are three levels of permission:

CAN_VIEW: Allows read-only access

CAN_RUN: Allows read access and run access (superset of CAN_VIEW)

CAN_MANAGE: Allows all actions: read, run, edit, delete, modify permissions (superset of CAN_RUN)

As such, I then moved to try the Identity and Access Management > Permissions APIs however both Get object permissions and Set permissions rely upon passing a request_object_type value.

Looking at the documentation it seems that queries aren't supported:

The Permissions API lets you manage permissions for:

Clusters

Cluster policies

Delta Live Tables

pipelines

Directories

Jobs

MLflow experiments

MLflow registered models

Notebooks

Pools

Repos

Databricks SQL warehouses

Tokens

Payload:

request_object_id = <QUERY_ID>
request_object_type = "sql/queries" # Tested these values also: "queries", "sql_query_id", "query"
generic_permissions_url = f"https://<DATABRICKS_HOST>/api/2.0/permissions/{request_object_type}/{request_object_id}"

generic_permissions_url_response_get = requests.get(generic_permissions_url, headers=HEADERS)

if generic_permissions_url_response_get.status_code == 200:
    print('Data retrieved succesfully.')
    generic_permissions_url_get_parsed = json.loads(generic_permissions_url_response_get.text)
else:
    print('Error retrieving data: ', generic_permissions_url_response_get.text)

Response:

Error retrieving data:  {"error_code":"BAD_REQUEST","message":"Invalid Object Type"}

As such:

Is it currently possible to alter query permissions programmatically in (Azure) Databricks?
If so, how?

you can look into the terraform code as a reference: https://github.com/databricks/terraform-provider-databricks/blob/master/permissions/resource_permissions.go — Alex Ott, May 23 '23 at 08:24
@AlexOtt Thanks for the link! However, it is still not clear to me for the [Get object permissions](https://docs.databricks.com/api-explorer/workspace/permissions/get) endpoint what `request_object_type` I should be passing for an SQL query... Trying with `queries`, `sql_query_id`, `query`, `sql/queries` _(from [this line](https://github.com/databricks/terraform-provider-databricks/blob/master/permissions/resource_permissions.go#L308))_ I receive a `{"error_code":"BAD_REQUEST","message":"Invalid Object Type"}` response. Are you able to confirm what should be the correct value? — followingell, May 23 '23 at 09:43
can you update your question with the payload that you use & command that is used to send that payload — Alex Ott, May 23 '23 at 09:46

score 1 · Accepted Answer · answered May 23 '23 at 12:39

If you look into corresponding documentation section, then you will see that you need to use another URL: /api/2.0/preview/sql/permissions/{objectType}/{objectId} instead of /api/2.0/permissions/{objectType}/{objectId}. Also, objectType should be queries, not the sql/queries. Here is a working command for getting queries using curl:

curl -s -H "Authorization: Bearer $DATABRICKS_TOKEN" -H 'Accept: application/json' 
  "$DATABRICKS_HOST/api/2.0/preview/sql/permissions/queries/$QUERY_ID"

gives:

{
  "object_id": "queries/<....>",
  "object_type": "query",
  "access_control_list": [
    {
      "user_name": "....@domain.com",
      "permission_level": "CAN_MANAGE"
    },
    {
      "group_name": "users",
      "permission_level": "CAN_VIEW"
    },
    {
      "group_name": "admins",
      "permission_level": "CAN_MANAGE"
    }
  ]
}

How to edit permissions of Databricks queries with REST API?

1 Answers1