topic Re: Dynamic GRANT in Warehousing & Analytics

Dynamic GRANT

jackintosh — Fri, 28 Mar 2025 11:31:15 GMT

Hello,

I am struggling trying to assign permission dynamically on a schema. I am using databricks asset bundle and I have a parametrized script to assign permission.

grant select on schema {{schema}} to {{group}};

I cannot achieve a dynamic grant statement. I also tried running on a notebook something like

execute immediate 'grant select on schema ? to ?' using ('my_schema', '`my_group`');

but unfortunately this does not work. From docs it seems it can be achievable with IDENTIFUER clause, I tried also that but without success. It throws syntax error.

It this achievable in some way or not? Thanks in advance.

Re: Dynamic GRANT

Vidhi_Khaitan — Mon, 02 Jun 2025 07:23:30 GMT

Hello,

It seems that IDENTIFIER is not supported with GRANT at the moment, however internal teams are working on supporting this.

Thus, as a temporary workaround, I believe you can use the following as you have already been using -

grant select on schema {{schema}} to {{group}};

Re: Dynamic GRANT

jameshughes — Sat, 14 Jun 2025 20:57:39 GMT

I use notebooks as part of the asset bundle deployment to conduct a lot of dynamic configurations based upon the workspace being deployed to (ex. Development, Test, Production). In conjunction, I developed a helper Python library with a number of functions that are used with administrative tasks. Attached is the one I created for adding schema permissions.

from typing import Tuple import re def addSchemaPermissions(schemaName: str, principal: str, permissionList: str, clearAll: bool) -> Tuple[bool, str]: try: if clearAll: spark.sql(f"REVOKE ALL PRIVILEGES ON SCHEMA {schemaName} FROM `{principal}`") spark.sql(f"GRANT {permissionList} ON SCHEMA {schemaName} TO `{principal}`") return True, "" except Exception as e: fullErrorMessage = str(e) # Try to extract the Databricks error class and readable message errorClassMatch = re.search(r"ErrorClass=([A-Z_\.]+)", fullErrorMessage) mainMessageMatch = re.search(r"] (.+?)\\n", fullErrorMessage) errorClass = errorClassMatch.group(1) if errorClassMatch else "Unknown Error" mainMessage = mainMessageMatch.group(1) if mainMessageMatch else fullErrorMessage.splitlines()[0] return False, f"{errorClass}: {mainMessage}" success, errorMessage = addSchemaPermissions('your_catalog_name.your_schema_name', 'Your Principal Name', 'USE SCHEMA, SELECT', True) if not success: print(f"Failed to update schema permissions: {errorMessage}")