Use the SQL statements in this topic to manage virtual warehouse access: view which users have access, grant or revoke permissions, set default virtual warehouses, and release active connections.
Superusers have access to all virtual warehouses in an instance by default. The statements in this topic apply to non-superuser accounts.
Prerequisites
Before you begin, make sure that:
-
Your Hologres instance is V2.0.4 or later. If it is not, upgrade the instance in the Hologres console (see the "Manual upgrade" section in Instance upgrades) or join the DingTalk group for technical support (see How to get more online support?).
-
You have joined the Hologres DingTalk group and applied for the required background configurations.
Set up warehouse access for a user
The following steps show a typical end-to-end workflow for granting a new user access to a virtual warehouse. You can also run individual statements from the sections below as needed.
Step 1: Confirm which users already have access.
SELECT * FROM hologres.hg_warehouse_users;Step 2: Grant the user access to a virtual warehouse. Granting access allows the user to route queries to and run workloads on that warehouse.
CALL hg_grant_warehouse_access_privilege ('analytics_wh','"BASIC$user1"');Step 3: Optionally, set that warehouse as the user's default so their queries run on it automatically.
CALL hg_set_user_default_warehouse ('"BASIC$user1"','analytics_wh');View access permissions on all virtual warehouses
Query the hg_warehouse_users system view to see which users have access to which virtual warehouses.
SELECT * FROM hologres.hg_warehouse_users;To filter results — for example, to see all warehouses a specific user has access to — add a WHERE clause:
SELECT * FROM hologres.hg_warehouse_users WHERE username = 'BASIC$user1';Fields
| Field | Data type | Description | Example |
|---|---|---|---|
warehouse_name |
TEXT | Name of the virtual warehouse | init_warehouse |
warehouse_id |
INTEGER | Unique ID of the virtual warehouse | 1 |
username |
TEXT | Name of a user who has access to the virtual warehouse | BASIC$user1 |
View default virtual warehouses of all users
Query the hg_user_default_warehouse system view to see each user's default virtual warehouse. Each user has at most one default virtual warehouse.
SELECT * FROM hologres.hg_user_default_warehouse;Fields
| Field | Data type | Description | Example |
|---|---|---|---|
warehouse_name |
TEXT | Name of the virtual warehouse | init_warehouse |
warehouse_id |
INTEGER | Unique ID of the virtual warehouse | 1 |
username |
TEXT | Name of the user | BASIC$user1 |
Set a default virtual warehouse for an instance
Call hg_set_default_warehouse to set the instance-level default virtual warehouse. All users in the instance automatically get access to this warehouse.
CALL hg_set_default_warehouse ('<warehouse_name>');Parameters
| Parameter | Data type | Description |
|---|---|---|
warehouse_name |
TEXT | Name of the virtual warehouse to set as the instance default |
Example: Set analytics_wh as the default virtual warehouse for the instance.
CALL hg_set_default_warehouse ('analytics_wh');Grant access to a virtual warehouse
Call hg_grant_warehouse_access_privilege to give a specific user access to a virtual warehouse. Once granted, the user can route queries to and run workloads on that warehouse.
CALL hg_grant_warehouse_access_privilege ('<warehouse_name>','"<user_name>"');Parameters
| Parameter | Data type | Description |
|---|---|---|
warehouse_name |
TEXT | Name of the virtual warehouse to grant access to |
user_name |
TEXT | User account to grant access. RAM user format: p4_2xxxxxxxxx. Custom account format: BASIC$user1 |
Example: Grant BASIC$user1 access to analytics_wh.
CALL hg_grant_warehouse_access_privilege ('analytics_wh','"BASIC$user1"');Revoke access to a virtual warehouse
Call hg_revoke_warehouse_access_privilege to remove a user's access to a virtual warehouse.
CALL hg_revoke_warehouse_access_privilege ('<warehouse_name>','"<user_name>"');Parameters
| Parameter | Data type | Description |
|---|---|---|
warehouse_name |
TEXT | Name of the virtual warehouse to revoke access from |
user_name |
TEXT | User account to revoke access. RAM user format: p4_2xxxxxxxxx. Custom account format: BASIC$user1 |
Example: Revoke BASIC$user1's access to analytics_wh.
CALL hg_revoke_warehouse_access_privilege ('analytics_wh','"BASIC$user1"');Set a default virtual warehouse for a user
Set or clear a per-user default virtual warehouse. When a user has a default virtual warehouse configured, queries run on that warehouse unless the user specifies a different one.
Set a default virtual warehouse:
CALL hg_set_user_default_warehouse ('"<user_name>"','<warehouse_name>');Remove a user's default virtual warehouse:
CALL hg_reset_user_default_warehouse ('"<user_name>"');Parameters
| Parameter | Data type | Description |
|---|---|---|
user_name |
TEXT | User account to configure. RAM user format: p4_2xxxxxxxxx. Custom account format: BASIC$user1 |
warehouse_name |
TEXT | Name of the virtual warehouse to set as the user's default |
Example: Set analytics_wh as the default virtual warehouse for BASIC$user1.
CALL hg_set_user_default_warehouse ('"BASIC$user1"','analytics_wh');Release connections to virtual warehouses
Call hg_kill_unprivileged_warehouse_connections to disconnect non-superuser accounts from all virtual warehouses in the instance. Only superusers can run this statement, and it does not affect superuser connections.
SELECT hg_kill_unprivileged_warehouse_connections();