Changing the default resource pool for a user
You can change the default pool of an existing user with the ALTER USER
command.
Execute the SHOW USERS
command to view default the resource pools assigned to each user.
SHOW USERS; **** +-------------+--------+-------------+------------+-----------------------+----------+ | User | Type | Connections | Is deleted | Default resource pool | Is local | +-------------+--------+-------------+------------+-----------------------+----------+ | 'root'@'%' | Native | 2 | | | LOCAL | | 'rw'@'%' | Native | 0 | | | LOCAL | | 'view'@'%' | Native | 0 | | | LOCAL | | 'write'@'%' | Native | 0 | | | LOCAL | +-------------+--------+-------------+------------+-----------------------+----------+
Create a resource pool.
CREATE RESOURCE POOL limReg WITH MEMORY_PERCENTAGE = 50, QUERY_TIMEOUT = 10, MAX_QUEUE_DEPTH = 5, MAX_CONCURRENCY = 5;
Run SHOW RESOURCE POOLS
to view the list of available resource pools.
SHOW RESOURCE POOLS; **** +-----------------------+-------------------+---------------+-----------------+---------------------------+---------------------------+-----------------+ | Pool_Name | Memory_Percentage | Query_Timeout | Max_Concurrency | Soft_CPU_Limit_Percentage | Hard_CPU_Limit_Percentage | Max_Queue_Depth | +-----------------------+-------------------+---------------+-----------------+---------------------------+---------------------------+-----------------+ | default_pool | 100 | NULL | NULL | NULL | NULL | NULL | | system_auto | 100 | NULL | NULL | NULL | NULL | NULL | | system_optimizer_pool | 100 | NULL | NULL | 5 | NULL | NULL | | limReg | 50 | 10 | 5 | NULL | NULL | 5 | +-----------------------+-------------------+---------------+-----------------+---------------------------+---------------------------+-----------------+
Use the ALTER USER
command to change the default resource pool for the 'rw'@'%'
user.
ALTER USER 'rw'@'%' SET DEFAULT RESOURCE POOL = limReg;
SHOW USERS; **** +-------------+--------+-------------+------------+-----------------------+----------+ | User | Type | Connections | Is deleted | Default resource pool | Is local | +-------------+--------+-------------+------------+-----------------------+----------+ | 'root'@'%' | Native | 1 | | | LOCAL | | 'rw'@'%' | Native | 0 | | limReg | LOCAL | | 'view'@'%' | Native | 0 | | | LOCAL | | 'write'@'%' | Native | 0 | | | LOCAL | +-------------+--------+-------------+------------+-----------------------+----------+
User Privileges and Resource Pools
Users can view the available resource pools and their status as per the granted permissions. By default, users can access the following resource pools:
Built-in resource pools
system_optimizer_pool
,default_pool
, andsystem_auto
.The default resource pool set for the user when the user is created.
CREATE USER general_user WITH DEFAULT RESOURCE POOL = general;
All available resource pools, if the user has
SUPER
permission.
Permissions for additional resource pools can be given using GRANT as follows:
GRANT USAGE ON RESOURCE POOL <resource_pool_name> to '<user_name>'@'%';
Use `*` to grant permissions on all resource pools, even if the user does note have the SUPER
permission:
GRANT USAGE ON RESOURCE POOL * to '<user_name>'@'%';