Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query optimization, and usability that you won’t want to miss.

Resource and User Management Tables

This section includes information schema tables that contain information which is useful for monitoring and managing the users, permissions, and resouces of a cluster.

information_schema.AVAILABILITY_GROUPS

This table contains information about availability groups.

Column Name Description
AVAILABILITY_GROUP_ID The unique ID of a given availability group.
SITE_ID The ID of a given availability group’s related site.
AVAILABILITY_GROUP_NAME The availability group name.

information_schema.CURRENT_USER_GROUPS

This table contains information about the group(s) the current user belongs to.

Column Name Description
GROUP The group(s) the current user belongs to.

information_schema.CURRENT_USER_ROLES

This table contains information about the role(s) of the current user.

Column Name Description
ROLE The role(s) of the current user.

information_schema.GROUP_PRIVILEGES

This table contains information about privileges as they relate to the roles and groups they are associated with. If you (the current user) has the GRANT permission, you can see all role-group pairs in this table. Otherwise, you will see only the pairs from groups you are part of.

Column Name Description
GROUP The name of a given group.
ROLE The name of a role associated with a given group.
DATABASE The name of the related database.
TABLE The name of the table to which a given privilege relates.
PRIVILEGES The privileges associated with a given role.

information_schema.GROUPS_ROLES

This table contains information about groups and the roles that are part of them. If you (the current user) has the GRANT permission, you can see all role-group pairs in this table. Otherwise, you will see only the pairs from groups you are part of.

Column Name Description
GROUP The name of a given group.
ROLE The role related to a given group.

information_schema.MV_PERMISSIONS_CLUSTER_METADATA

This table contains information about cluster permissions metadata.

Column Name Description
PERMISSIONS The permissions for a given cluster.

information_schema.MV_RESOURCE_POOL_STATUS

This view contains information about the status of resource pools.

Column Name Description
NODE_ID The ID of the related node.
POOL_NAME The name of a given resource pool.
RUNNING_QUERIES The number of queries running on a given resource pool. This statistic is dependent on the current state of the server.
QUEUEING_QUERIES The number of queries queued for a given resource pool. This statistic is dependent on the current state of the server.
KILLED_QUERIES The number of queries killed on a given resource pool. This statistic is cumulative from the start of the server for that node.
FINISHED_QUERIES The number of queries finished on a given resource pool. This statistic is cumulative from the start of the server for that node.
TOTAL_QUEUE_TIME_US The historical total queue time for a given resource pool. This statistic is cumulative from the start of the server for that node.
AVERAGE_QUEUE_TIME_US The historical average queue time for a given resource pool. This statistic is cumulative from the start of the server for that node.
CPU_AFFINITY_MASK A boolean mask showing which CPU is designated for the queries in a given resource pool.

information_schema.RESOURCE_POOLS

This table contains information about resource pools. For more information about these fields, see CREATE RESOURCE POOL.

Column Name Description
POOL_NAME The name of a given resource pool.
MEMORY_PERCENTAGE The percentage of available memory that the given resource pool is allowed to use.
QUERY_TIMEOUT The number of seconds after which a resource pool is set to cancel queries.
MAX_CONCURRENCY The maximum number of concurrent queries that can be run accross all aggregators at a time for a given resource pool.
SOFT_CPU_LIMIT_PERCENTAGE An integer between 1 to 100. CPU usage for users can be limited from 1% - 100% of available processing capacity.
HARD_CPU_LIMIT_PERCENTAGE An integer between 1 to 100. CPU usage for users can be limited from 1% - 100% of available processing capacity.
MAX_QUEUE_DEPTH The maximum number of queries that will be queued when MAX_CONCURRENCY is exceeded.

information_schema.ROLE_PRIVILEGES

This table contains information about roles as they relate to privileges in a cluster.

Column Name Description
ROLE The name of a given role.
DATABASE The name of the related database.
TABLE The name of the table for which a related privilege is given.
PRIVILEGES The privilege associated with a given role.

information_schema.SCHEMA_PRIVILEGES

This table contains information about the privileges in a given cluster.

Column Name Description
GRANTEE The user for which a given privilege has been granted.
TABLE_CATALOG The name of the catalog of the related table for a given column privilege.
TABLE_SCHEMA The name of the related schema (database) for a given privilege.
PRIVILEGE_TYPE The type of privilege granted to a given user. See a list of all types in GRANT.
IS_GRANTABLE Whether a given privilege can be granted by a given user.

information_schema.TABLE_PRIVILEGES

This table contains information about tables and their related privileges in a given cluster.

Column Name Description
GRANTEE The user for which a given privilege has been granted.
TABLE_CATALOG The name of the catalog of the related table for a given column privilege.
TABLE_SCHEMA The name of the related schema (database) for a given privilege.
TABLE_NAME The name of a given table that a privilege has been granted for.
PRIVILEGE_TYPE The type of privilege granted to a given user. See a list of all types in GRANT.
IS_GRANTABLE Whether a given privilege can be granted by a given user.

information_schema.USER_PRIVILEGES

This table contains information about the users and their related privileges in a given cluster. If you (the current user) have the GRANT and SHOW METADATA permissions, you can see all user privileges. Otherwise, you will see only your own privileges.

Column Name Description
GRANTEE The user for which a given privilege has been granted.
TABLE_CATALOG The name of the catalog of the related table for a given column privilege.
PRIVILEGE_TYPE The type of privilege granted to a given user. See a list of all types in GRANT.
IS_GRANTABLE Whether a given GRANTEE can grant permissions to other users.

information_schema.USERS

This table contains information about users for a given cluster. If you (the current user) has the GRANT permission, you can see all role-group pairs in this table. Otherwise, you will see only the pairs from groups you are part of.

Column Name Description
USER The name of a given user.
HOST The hostname or IP address for a given user.
TYPE The type of connection authentication.
CONNECTIONS The number of connections a given user has open.
IS_DELETED Whether a given user had been deleted.
LAST_UPDATED The timestamp at which a given user was last updated.
DEFAULT_RESOURCE_POOL The default resource pool for a given user.
IS_LOCAL Whether a given user is local to the node on which USERS table is being accessed or if it’s a distributed user.
CREATED The timestamp at which a given user was created.
PASSWORD_UPDATED The timestamp a given user’s password was last updated.
EFFECTIVE_FAILED_LOGIN_ATTEMPTS The number of failed login attempts for a given user.
EFFECTIVE_PASSWORD_LOCK_TIME The amount of time, in milliseconds, a given user’s account has been locked due to failed login attempts.
ACCOUNT_STATUS Whether a given user account is LOCKED due to failed login attempts exceeding the set limit, or if the account is OPEN. N/A means that a policy has not been set for exceeding login attempts.

information_schema.USERS_GROUPS

This table contains information about how groups and users are related for a given cluster. If you (the current user) has the GRANT permission, you can see all role-group pairs in this table. Otherwise, you will see only the pairs from groups you are part of.

Column Name Description
GROUP The name of the group for a given user.
USER The name of the user for a given group.