REVOKE PRIVILEGE

Description

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

To revoke roles on Onehouse resources, use REVOKE ROLE.

Syntax

Revoke on a single object:

REVOKE PRIVILEGE <privilege> [, <privilege> ...] ON <object_type> '<object_name>' FROM <principal_type> '<principal_identifier>'

Revoke a bulk grant:

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

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

Examples

REVOKE PRIVILEGE SELECT ON TABLE 'prod_lake.sales.orders' FROM GROUP 'analysts'

REVOKE PRIVILEGE SELECT, INSERT ON TABLE 'prod_lake.sales.orders' FROM USER 'steve@apple.com'

REVOKE PRIVILEGE CREATE ON DATABASE 'prod_lake.sales' FROM SERVICE_PRINCIPAL 'ingest_sp'

REVOKE PRIVILEGE ALL_PRIVILEGES ON LAKE 'prod_lake' FROM GROUP 'data_platform'

REVOKE PRIVILEGE SELECT ON ALL TABLES IN DATABASE 'prod_lake.sales' FROM GROUP 'analysts'

Required parameters

• privilege: Specify one of {ALL_PRIVILEGES|DESCRIBE|CREATE|SELECT|INSERT|DELETE|ALTER|DROP}. Revoking is not restricted to the privileges legal on that object type — see What gets rejected.
• 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

Privilege lists

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

REVOKE PRIVILEGE SELECT, INSERT ON TABLE 'prod_lake.sales.orders' FROM GROUP 'analysts'

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

ALL_PRIVILEGES revokes exactly that object type's privilege list. See ALL_PRIVILEGES for the expansion on each object type.

Revoking one child of a bulk grant

A bulk grant (ON ALL TABLES IN DATABASE, ON ALL DATABASES IN LAKE) covers every child, current and future. Revoking a single child does not break the bulk grant apart — it records an exclusion, carving that one child out and leaving the rest of the cascade intact. This is how you narrow a bulk grant without enumerating children that may not exist yet.

Re-granting that child deletes the exclusion in the same write, so grant → revoke → grant round-trips back to the original state:

GRANT  PRIVILEGE SELECT ON ALL TABLES IN DATABASE 'prod_lake.sales' TO   GROUP 'analysts';
REVOKE PRIVILEGE SELECT ON TABLE 'prod_lake.sales.orders'           FROM GROUP 'analysts';
GRANT  PRIVILEGE SELECT ON TABLE 'prod_lake.sales.orders'           TO   GROUP 'analysts';

• After line 1, analysts can SELECT every table in sales, now and in future.
• After line 2, every table in sales except orders.
• After line 3, every table in sales again — the exclusion is removed, not layered over.

Revoking the whole bulk grant drops the cascading grant and its exclusions outright:

REVOKE PRIVILEGE SELECT ON ALL TABLES IN DATABASE 'prod_lake.sales' FROM GROUP 'analysts'

There is no DENY

A revoke only ever subtracts from an existing grant. Revoking a privilege the principal was never granted writes nothing at all — in particular, it writes no exclusion, so it cannot be used to block a privilege the principal might be granted later.

What gets rejected

Statement	Error
REVOKE PRIVILEGE UPDATE ON LAKE 'prod_lake' ...	Privileges UPDATE are not supported on object type LAKE
REVOKE PRIVILEGE CREATE ON TABLE 'prod_lake.sales.orders' ...	Privileges CREATE are not supported on object type TABLE
REVOKE PRIVILEGE VIEWER, SELECT ON TABLE 'prod_lake.sales.orders' ...	VIEWER is a role, not a privilege, and cannot appear in a privilege list
REVOKE PRIVILEGE SELECT ON ALL TABLES IN LAKE 'prod_lake' ...	ON ALL TABLES IN LAKE is not supported: lake-level grants are out of scope. Use ON ALL TABLES IN DATABASE instead
REVOKE PRIVILEGE SELECT ON ALL DATABASES IN DATABASE 'prod_lake.sales' ...	ON ALL DATABASES IN DATABASE is not supported: a database contains no databases

A malformed object name is rejected the same way it is on a grant — see Object names.

Role names (VIEWER, EDITOR, MANAGER, USER, MEMBER) cannot appear in a privilege list. Use REVOKE ROLE instead.

Revoke skips the object-type legality check

GRANT PRIVILEGE checks each privilege against the list legal on that object type. REVOKE PRIVILEGE skips that check, so REVOKE PRIVILEGE SELECT ON LAKE 'prod_lake' and REVOKE PRIVILEGE MANAGE ON TABLE 'prod_lake.sales.orders' succeed — which is what lets you clear a grant that would be rejected if you tried to make it today.

A privilege with no data meaning at all is still rejected: UPDATE or CANCEL on any object, and CREATE on a table.