REVOKE

Function Description

Revokes previously granted privileges from a role or user. The REVOKE statement can revoke permissions at various levels, including workspace, workspace object, Schema, and Schema object levels.

Workspace User and Role Permission Management Syntax

REVOKE workspacePrivileges ON WORKSPACE workspace_name | workspaceObjectPrivileges ON { ROLE | SCHEMA | VCLUSTER } workspace_object_name | schemaPrivileges ON SCHEMA schema_name | schemaObjectPrivileges ON { TABLE | VIEW | MATERIALIZED VIEW } schema_object_name | functionPrivileges ON FUNCTION schema_object_name | volumePrivileges ON VOLUME schema_object_name FROM { ROLE role_name | USER user_name };

Privilege Type Description

Privilege Type	Description	Example
workspacePrivileges	Privileges to create objects in a workspace	CREATE SCHEMA, CREATE VCLUSTER
workspaceObjectPrivileges	Privileges to modify workspace objects and view metadata	ALTER, DROP, READ METADATA, ALL [PRIVILEGES]
schemaPrivileges	Privileges to create objects in a Schema	CREATE TABLE, CREATE FUNCTION, CREATE VOLUME
schemaObjectPrivileges	Privileges to modify, drop, and query Schema objects	ALTER, DROP, SELECT, INSERT, READ METADATA, ALL
functionPrivileges	Privileges to use, alter, and drop a Function	USE FUNCTION, ALTER FUNCTION, DROP FUNCTION, READ METADATA, ALL PRIVILEGES
volumePrivileges	Privileges to read, write, alter, and drop a Volume	READ VOLUME, WRITE VOLUME, ALTER VOLUME, DROP VOLUME, READ METADATA, ALL PRIVILEGES

Functions and Volumes are schema child objects. Revoke CREATE FUNCTION and CREATE VOLUME from the parent schema, and revoke the remaining privileges from the specific object.

Usage Examples

1. Revoke from role simple_role the privilege to create VCLUSTERs in the workspace:

   REVOKE CREATE VCLUSTER ON WORKSPACE lakehouse_public FROM ROLE simple_role;

2. Revoke from role simple_role the ALTER privilege on the VCLUSTER named default:

   REVOKE ALTER VCLUSTER ON VCLUSTER DEFAULT FROM ROLE simple_role;

3. Revoke from role uat_demo the privilege to create tables and views in the public Schema:

   REVOKE CREATE VIEW, CREATE TABLE ON SCHEMA public FROM ROLE uat_demo;

4. Revoke from role reporting_role read access to Volume public.sales_data:

   REVOKE READ VOLUME ON VOLUME public.sales_data FROM ROLE reporting_role;

5. Revoke from role admin_role alter and drop privileges on Function public.order_summary:

   REVOKE ALTER FUNCTION ON FUNCTION public.order_summary FROM ROLE admin_role; REVOKE DROP FUNCTION ON FUNCTION public.order_summary FROM ROLE admin_role;

6. Revoke from role analyst_role the SELECT and INSERT privileges on the table customer_orders in the public Schema:

   REVOKE SELECT, INSERT ON TABLE public.customer_orders FROM ROLE analyst_role;

7. Revoke role test_readonly_role from user tester:

   REVOKE ROLE test_readonly_role FROM USER tester;

Notes

• Executing this command requires the workspace_admin or security_admin role.
• After revocation, the revoked party will immediately lose the ability to access the corresponding resource.
• You can verify the revocation result using SHOW GRANTS TO ROLE role_name or SHOW GRANTS TO USER user_name.

Instance Role Permission Management

LakeHouse supports fine-grained revocation of cross-workspace permissions for Instance Roles, ensuring flexibility and security in permission control.

Syntax

-- Revoke an Instance Role's permissions on a workspace REVOKE <privilege> ON WORKSPACE <workspace_name> FROM INSTANCE ROLE <role_name>; -- Revoke an Instance Role's permissions on a table REVOKE <privilege> ON TABLE <workspace>.<schema>.<table> FROM INSTANCE ROLE <role_name>; -- Revoke an Instance Role's permissions on a Schema REVOKE <privilege> ON DATABASE <workspace>.<schema> FROM INSTANCE ROLE <role_name>; -- Remove a user's Instance Role membership REVOKE INSTANCE ROLE <role_name> FROM USER <user_name>;

Examples

-- Revoke all permissions on a workspace REVOKE ALL ON WORKSPACE ws1 FROM INSTANCE ROLE inst_role; -- Verify the revocation result (expected: no ALL privilege) SHOW GRANTS TO INSTANCE ROLE inst_role; -- Revoke all permissions on a single table REVOKE ALL ON TABLE ws1.public.sales FROM INSTANCE ROLE inst_role; -- Remove a user's Instance Role REVOKE INSTANCE ROLE inst_role FROM USER lh_engine_test_01;