GRANT

Function Description

Grants specified privileges to a role or user, enabling fine-grained access control for workspace resources.

Workspace User and Role Permission Management Syntax

GRANT 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 | dynamicTablePrivileges ON DYNAMIC TABLE schema_object_name | functionPrivileges ON FUNCTION schema_object_name | volumePrivileges ON VOLUME schema_object_name TO { ROLE role_name | USER user_name } [WITH GRANT OPTION];

⚠️ Note: Dynamic Tables require ON DYNAMIC TABLE in the GRANT statement — ON TABLE does not work. Using ON TABLE against a Dynamic Table returns Invalid privilege 'SELECT TABLE' for object type 'TABLE'. Regular managed tables still use ON TABLE; the two are not interchangeable.

Privilege Type Description

-- Workspace level: create objects workspacePrivileges ::= CREATE { SCHEMA | VCLUSTER } -- Workspace object privileges workspaceObjectPrivileges ::= ALTER | DROP | READ METADATA | ALL [PRIVILEGES] -- Schema level: create objects schemaPrivileges ::= CREATE { TABLE | VIEW | MATERIALIZED VIEW | DYNAMIC TABLE | FUNCTION | VOLUME } | ALL -- Schema object privileges (regular tables, views, materialized views) schemaObjectPrivileges ::= ALTER | DROP | SELECT | INSERT | READ METADATA | ALL -- Dynamic Table privileges dynamicTablePrivileges ::= SELECT | ALTER DYNAMIC TABLE | DROP DYNAMIC TABLE | RESTORE DYNAMIC TABLE | READ METADATA | ALL [PRIVILEGES] -- Function object privileges functionPrivileges ::= ALTER FUNCTION | DROP FUNCTION | USE FUNCTION | READ METADATA | ALL [PRIVILEGES] -- Volume object privileges volumePrivileges ::= ALTER VOLUME | DROP VOLUME | READ VOLUME | WRITE VOLUME | READ METADATA | ALL [PRIVILEGES]

Functions and Volumes are schema child objects. Grant CREATE FUNCTION and CREATE VOLUME on the parent schema, and grant the remaining privileges on the specific object. The object suffix in Function and Volume privilege names is optional, but the full names are recommended; SHOW GRANTS displays the full names.

Parameter Description

Parameter	Required	Description
`workspacePrivileges`	Yes (when granting workspace privileges)	Workspace-level privileges, such as `CREATE SCHEMA`, `CREATE VCLUSTER`
`workspaceObjectPrivileges`	Yes (when granting workspace object privileges)	Privileges on workspace objects, such as `ALTER`, `DROP`, `READ METADATA`, `ALL [PRIVILEGES]`
`schemaPrivileges`	Yes (when granting Schema privileges)	Schema-level privileges, such as `CREATE TABLE`, `CREATE FUNCTION`, `CREATE VOLUME`, and `ALL`
`schemaObjectPrivileges`	Yes (when granting Schema object privileges)	Privileges on Schema objects, such as `ALTER`, `DROP`, `SELECT`, `INSERT`, `READ METADATA`, `ALL`
`functionPrivileges`	Yes (when granting Function privileges)	Function privileges such as `USE FUNCTION`, `ALTER FUNCTION`, and `DROP FUNCTION`
`volumePrivileges`	Yes (when granting Volume privileges)	Volume privileges such as `READ VOLUME`, `WRITE VOLUME`, and `DROP VOLUME`
`workspace_name`	Yes (when granting workspace privileges)	Workspace name
`workspace_object_name`	Yes (when granting workspace object privileges)	Name of the workspace object (Role, Schema, or VCluster)
`schema_name`	Yes (when granting Schema privileges)	Schema name
`schema_object_name`	Yes (when granting Schema object privileges)	Full object name in the format `schema_name.object_name`
`role_name`	Yes (when granting to a role)	Name of the role being granted privileges
`user_name`	Yes (when granting to a user)	Name of the user being granted privileges
`WITH GRANT OPTION`	No	Allows the grantee to re-grant this privilege to other users

Usage Examples

Grant role simple_role the privilege to create VCLUSTERs in the workspace:
GRANT CREATE VCLUSTER ON WORKSPACE lakehouse_public TO ROLE simple_role;
Grant role simple_role the privilege to alter the VCLUSTER named default:
GRANT ALTER VCLUSTER ON VCLUSTER default TO ROLE simple_role;
Grant role simple_role the privilege to create tables and views in the public Schema:
GRANT CREATE VIEW, CREATE TABLE ON SCHEMA public TO ROLE simple_role;
Grant role my_role query and modification privileges on a specified table:
GRANT SELECT, ALTER ON TABLE public.my_table TO ROLE my_role;
Grant role my_role query privilege on a specified view:
GRANT SELECT ON VIEW public.my_view TO ROLE my_role;
Grant role my_role query privilege on a specified materialized view:
GRANT SELECT ON MATERIALIZED VIEW public.my_materialized_view TO ROLE my_role;
Grant role my_role query privilege on a specified dynamic table:
GRANT SELECT ON DYNAMIC TABLE public.my_dynamic_table TO ROLE my_role;

Grant role all privileges on a dynamic table:
GRANT ALL PRIVILEGES ON DYNAMIC TABLE public.my_dynamic_table TO ROLE my_role;
Grant user tester the role test_readonly_role:
GRANT ROLE test_readonly_role TO USER tester;
Grant user tester query privilege on a specified table, with the ability to re-grant:
GRANT SELECT ON TABLE public.my_table TO USER tester WITH GRANT OPTION;
Grant a role the privileges required to create and use a Function:
GRANT CREATE FUNCTION ON SCHEMA public TO ROLE function_developer; GRANT USE FUNCTION ON FUNCTION public.image_to_text TO ROLE function_developer;
Grant a role the privileges required to create and access a Volume:
GRANT CREATE VOLUME ON SCHEMA public TO ROLE volume_developer; GRANT READ VOLUME ON VOLUME public.shared_files TO ROLE volume_developer; GRANT WRITE VOLUME ON VOLUME public.shared_files TO ROLE volume_developer;

Successful execution returns an empty result set; no error message means the grant was successful. You can verify the grant result using SHOW GRANTS TO ROLE role_name or SHOW GRANTS TO USER user_name.

Notes

Executing this command requires the workspace_admin or security_admin role, or having WITH GRANT OPTION permission on the target object.
It is recommended to manage permissions through roles (GRANT ROLE) for easier batch granting and unified revocation.
Follow the principle of least privilege, granting only the minimum permissions required to complete the work.
Use WITH GRANT OPTION with caution, as the grantee may propagate permissions to other users.

Instance Role Granting

Instance Roles can be used to grant cross-workspace global permissions. Grant targets include users or other roles.

Syntax

-- Grant an Instance Role to a user GRANT INSTANCE ROLE <role_name> TO USER <user_name>; -- Grant workspace permissions to an Instance Role GRANT <privilege> ON WORKSPACE <workspace_name> TO INSTANCE ROLE <role_name>; -- Grant table-level permissions to an Instance Role GRANT <privilege> ON TABLE <workspace>.<schema>.<table> TO INSTANCE ROLE <role_name>; -- View Instance Role permissions SHOW GRANTS TO INSTANCE ROLE <role_name>;

Examples

-- Grant an Instance Role to a user GRANT INSTANCE ROLE inst_role TO USER lh_engine_test_01; -- Grant all permissions on a workspace GRANT ALL ON WORKSPACE ws1 TO INSTANCE ROLE inst_role; -- Grant all permissions on a single table GRANT ALL ON TABLE ws1.schema.table TO INSTANCE ROLE inst_role; -- View permissions (expected to include cross-workspace grant records) SHOW GRANTS TO INSTANCE ROLE inst_role; -- Revoke all permissions on a workspace REVOKE ALL ON WORKSPACE ws1 FROM INSTANCE ROLE inst_role;