GRANT PRIVILEGE

Description

Grant one or more privileges on a specified data object. See the permissions documentation for more details.

To grant roles on Onehouse resources, use GRANT ROLE.

Data Privilege Limitation

Data privileges on Lakes, Databases, and Tables are currently not enforced on SQL, Spark, and Open Engines Clusters. Cluster-side enforcement is a separate opt-in, available on clusters that use the Onehouse Catalog — contact Onehouse support to have it enabled for your organization.

For now, data privileges may be used to manage data access for Flows and Table Services.

Syntax

Grant on a single object:

GRANT PRIVILEGE <privilege> [, <privilege> ...] ON <object_type> '<object_name>' TO <principal_type> '<principal_identifier>'

Grant on every child of a container, current and future:

GRANT PRIVILEGE <privilege> [, <privilege> ...] ON ALL TABLES IN DATABASE '<lake>.<database>' TO <principal_type> '<principal_identifier>'

GRANT PRIVILEGE <privilege> [, <privilege> ...] ON ALL DATABASES IN LAKE '<lake>' TO <principal_type> '<principal_identifier>'

Examples

GRANT PRIVILEGE SELECT ON TABLE 'prod_lake.sales.orders' TO GROUP 'analysts'

GRANT PRIVILEGE SELECT, INSERT, DESCRIBE ON TABLE 'prod_lake.sales.orders' TO USER 'steve@apple.com'

GRANT PRIVILEGE CREATE ON DATABASE 'prod_lake.sales' TO SERVICE_PRINCIPAL 'ingest_sp'

GRANT PRIVILEGE ALL_PRIVILEGES ON LAKE 'prod_lake' TO GROUP 'data_platform'

GRANT PRIVILEGE SELECT ON ALL TABLES IN DATABASE 'prod_lake.sales' TO GROUP 'analysts'

GRANT PRIVILEGE DESCRIBE ON ALL DATABASES IN LAKE 'prod_lake' TO GROUP 'analysts'

Required parameters

• privilege: Specify one of {ALL_PRIVILEGES|DESCRIBE|CREATE|SELECT|INSERT|DELETE|ALTER|DROP}. Which ones are legal depends on the object_type — see Privileges by object type.
• object_type: Specify one of {LAKE|DATABASE|TABLE}.
• object_name: Specify the fully qualified name of the object, quoted. The number of parts must match the object type — see Object names.
• principal_type: Specify one of {USER|GROUP|SERVICE_PRINCIPAL}.
• principal_identifier: Specify one of {<user_email>|<group_id>|<service_principal_id>}.

Object names

object_type	object_name format	Example
LAKE	'<lake>'	'prod_lake'
DATABASE	'<lake>.<database>'	'prod_lake.sales'
TABLE	'<lake>.<database>.<table>'	'prod_lake.sales.orders'

A name with the wrong number of parts is rejected:

• Database name must be in format 'lake.database', got: X
• Table name must be in format 'lake.database.table', got: X

A name with the right number of parts but an empty part is rejected separately:

• Lake name and database name cannot be empty in: X
• Lake name, database name, and table name cannot be empty in: X

Privileges by object type

Each object type has its own list of legal privileges; granting one outside that list is rejected rather than silently stored.

Object type	Grantable privileges
LAKE	DESCRIBE, CREATE (recorded as CREATE_DATABASE), ALTER, DROP
DATABASE	DESCRIBE, CREATE (recorded as CREATE_TABLE), ALTER, DROP
TABLE	SELECT, INSERT, DELETE, DESCRIBE, ALTER, DROP

ALL_PRIVILEGES is legal on every object type.

CREATE resolves to the child object

CREATE names the child it creates, so it resolves to the child of the object it is granted on:

Statement	Privilege granted
CREATE ON LAKE 'prod_lake'	CREATE_DATABASE — create databases in the lake
CREATE ON DATABASE 'prod_lake.sales'	CREATE_TABLE — create tables in the database
CREATE ON TABLE '...'	Rejected — nothing is created inside a table

ALL_PRIVILEGES

ALL_PRIVILEGES expands to exactly that object type's privilege list, in this order:

Object type	ALL_PRIVILEGES expands to
LAKE	DESCRIBE, CREATE_DATABASE, ALTER, DROP
DATABASE	DESCRIBE, CREATE_TABLE, ALTER, DROP
TABLE	SELECT, INSERT, DELETE, DESCRIBE, ALTER, DROP

ALL_PRIVILEGES does not grant anything on child objects. To cover children, use a bulk grant.

Privilege lists

A comma-separated privilege list is supported on a single object and on the bulk form.

GRANT PRIVILEGE SELECT, INSERT, DELETE ON TABLE 'prod_lake.sales.orders' TO GROUP 'analysts'

• The list is all-or-nothing: every element is validated before anything is written, so a list with one bad element grants none of them.
• The error names every offending privilege, not just the first.
• The result is deduplicated, preserving first-occurrence order.

Bulk grants

A bulk grant covers every child of a container — current and future. Only two pairings exist:

Statement	Covers
ON ALL TABLES IN DATABASE '<lake>.<database>'	Every table in that database
ON ALL DATABASES IN LAKE '<lake>'	Every database in that lake

GRANT PRIVILEGE SELECT ON ALL TABLES IN DATABASE 'prod_lake.sales' TO GROUP 'analysts'

Any other pairing is rejected:

Statement	Error
ON ALL TABLES IN LAKE	ON ALL TABLES IN LAKE is not supported: lake-level grants are out of scope. Use ON ALL TABLES IN DATABASE instead
ON ALL DATABASES IN DATABASE	ON ALL DATABASES IN DATABASE is not supported: a database contains no databases

To carve a single child out of a bulk grant, revoke it individually — see REVOKE PRIVILEGE.

What gets rejected

Statement	Error
GRANT PRIVILEGE MANAGE ON TABLE 'prod_lake.sales.orders' ...	MANAGE is not grantable at any data grain (attempted on PROJECT_RESOURCE_TYPE_TABLE): it is pending the DATA_ADMIN role. Grant the specific privileges instead
GRANT PRIVILEGE SELECT ON LAKE 'prod_lake' ...	Privileges DATA_RESOURCE_PRIVILEGE_SELECT are not supported on PROJECT_RESOURCE_TYPE_LAKE
GRANT PRIVILEGE UPDATE ON LAKE 'prod_lake' ...	Privileges UPDATE are not supported on object type LAKE
GRANT PRIVILEGE VIEWER, SELECT ON TABLE 'prod_lake.sales.orders' ...	VIEWER is a role, not a privilege, and cannot appear in a privilege list

• MANAGE is not grantable on a Lake, Database, or Table. Grant the specific privileges instead, or make the principal a data admin with GRANT DATA_ADMIN.
• UPDATE and CANCEL parse but have no meaning on a data object.
• Role names (VIEWER, EDITOR, MANAGER, USER, MEMBER) cannot appear in a privilege list. Use GRANT ROLE instead.