# Administer Default Resource Pools This page explains how to set and change the default resource pool for users. ## Set the Default Resource Pool for a User The default resource pool of a user is the `resource_pool` value that will be set when a user first connects to SingleStore server. You can set this value when you create a user, or modify this value later. To set this value when creating a user, use the following syntax: ```sql CREATE USER general_user WITH DEFAULT RESOURCE POOL = general; ``` Run the `SHOW USERS` command to see the default pool assigned to each user. ```sql SHOW USERS; ``` ```output +--------------------+--------+-------------+------------+-----------------------+ | User | Type | Connections | Is deleted | Default resource pool | +--------------------+--------+-------------+------------+-----------------------+ | 'general_user'@'%' | Native | 0 | | general | | 'root'@'%' | Native | 1 | | | +--------------------+--------+-------------+------------+-----------------------+ ``` If the default resource pool is not explicitly set for the user, the default resource pool for that user is the pool set by the command `SET GLOBAL resource_pool = foo`, or `default_pool` if the session variable is not set globally. The default resource pool information is also in the information schema table. ```sql SELECT * FROM INFORMATION_SCHEMA.USERS; ``` ```output +--------------+------+-------------+------------+---------------------+-----------------------+ | USER | HOST | CONNECTIONS | IS_DELETED | LAST_UPDATED | DEFAULT_RESOURCE_POOL | +--------------+------+-------------+------------+---------------------+-----------------------+ | root | % | 1 | 0 | 2018-06-29 11:51:51 | | | general_user | % | 0 | 0 | 2018-06-29 11:54:38 | general | +--------------+------+-------------+------------+---------------------+-----------------------+ ``` You can also change the default pool of a user using the `ALTER USER` command. For more information, see [ALTER USER](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/alter-user.md). ## Change 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. ```sql SHOW USERS; ``` ```output +-------------+--------+-------------+------------+-----------------------+----------+ | 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. ```sql 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. ```sql SHOW RESOURCE POOLS; ``` ```output +-----------------------+-------------------+---------------+-----------------+---------------------------+---------------------------+-----------------+ | 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. ```sql ALTER USER 'rw'@'%' SET DEFAULT RESOURCE POOL = limReg; ``` ```sql SHOW USERS; ``` ```output +-------------+--------+-------------+------------+-----------------------+----------+ | 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`, and `system_auto`. * The default resource pool set for the user when the user is created. ```sql 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/grant.md) as follows: ```sql GRANT USAGE ON RESOURCE POOL to ''@'%'; ``` Use \`\*\` to grant permissions on all resource pools, even if the user does not have the `SUPER` permission: ```sql GRANT USAGE ON RESOURCE POOL * to ''@'%'; ``` *** Modified at: March 7, 2025 Source: [/db/v9.1/user-and-cluster-administration/use-the-workload-manager-and-set-resource-limits/set-resource-limits/administer-default-resource-pools/](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/use-the-workload-manager-and-set-resource-limits/set-resource-limits/administer-default-resource-pools/) (An index of the documentation is available at /llms.txt)