CODEWITHSUNDEEP
databricksazure-databricksdatabricks-rest-api
followingell
followingell

Reputation: 501

How to edit permissions of Databricks queries with REST API?

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:

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

Upvotes: 2

Views: 2192

Answers (1)

Alex Ott
Alex Ott

Reputation: 87259

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": "[email protected]",
      "permission_level": "CAN_MANAGE"
    },
    {
      "group_name": "users",
      "permission_level": "CAN_VIEW"
    },
    {
      "group_name": "admins",
      "permission_level": "CAN_MANAGE"
    }
  ]
}

Upvotes: 1

Related Questions