ClickHouse元数据 7.用户、角色与权限相关的表

1. users（用户信息表）

name	type	comment
name	String	用户名
id	UUID	用户 ID。
storage	String	用户存储的路径。在 access_control_path 参数中配置
auth_type	Enum8( ‘no_password’ = 0, ‘plaintext_password’ = 1, ‘sha256_password’ = 2, ‘double_sha1_password’ = 3, ‘ldap’ = 4, ‘kerberos’ = 5)	显示身份验证类型。对应着是：无密码、明文密码、SHA256编码密码、双SHA-1编码密码。
auth_params	String	JSON 格式的身份验证参数，具体取决于 auth_type
host_ip	Array(String)	允许连接到 ClickHouse 服务器的主机的 IP 地址
host_names	Array(String)	允许连接到 ClickHouse 服务器的主机的名称
host_names_regexp	Array(String)	允许连接到 ClickHouse 服务器的主机名的正则表达式
host_names_like	Array(String)	允许连接到 ClickHouse 服务器的主机的名称，使用 LIKE 谓词设置
default_roles_all	UInt8	显示默认情况下为用户设置的所有授予的角色
default_roles_list	Array(String)	默认提供的授权角色列表
default_roles_except	Array(String)	除列出的角色外，所有授予的角色都设置为默认角色
grantees_any	UInt8
grantees_except	Array(String)

2. user_directories（用户配置目录）

user_directories：用户配置目录，可以在config.xml里设置里面的值

name	type	comment
name	String	名称
type	String	属性
params	String	参数
precedence	UInt64	优先级

3. roles（角色信息）

包含已配置角色的信息。

name	type	comment
name	String	角色名称
id	UUID	角色 ID
storage	String	角色存储的路径。 在 access_control_path 参数中配置

4. enabled_roles（可用角色）

包含当前所有活跃的角色，包括当前用户的当前角色和当前角色授予的角色。

name	type	comment
role_name	String	角色名称
with_admin_option	UInt8	包含管理员选项
is_current	UInt8	当前是否是该角色
is_default	UInt8	是否是默认角色

5. current_roles（用户角色表）

CK的角色，空表

name	type	comment
role_name	String	角色名称
with_admin_option	UInt8	带管理员选项
is_default	UInt8	是否是默认用户

6. grants（授权信息）

授予 ClickHouse 用户帐户的权限

name	type	comment
user_name	Nullable(String)	用户名
role_name	Nullable(String)	分配给用户帐户的角色
access_type	Enum8( ‘SHOW DATABASES’ = 0, ‘SHOW TABLES’ = 1, ‘SHOW COLUMNS’ = 2, ‘SHOW DICTIONARIES’ = 3, ‘SHOW’ = 4, ‘SELECT’ = 5, ‘INSERT’ = 6, ‘ALTER UPDATE’ = 7, ‘ALTER DELETE’ = 8, ‘ALTER ADD COLUMN’ = 9, ‘ALTER MODIFY COLUMN’ = 10, ‘ALTER DROP COLUMN’ = 11, ‘ALTER COMMENT COLUMN’ = 12, ‘ALTER CLEAR COLUMN’ = 13, ‘ALTER RENAME COLUMN’ = 14, ‘ALTER COLUMN’ = 15, ‘ALTER ORDER BY’ = 16, ‘ALTER SAMPLE BY’ = 17, ‘ALTER ADD INDEX’ = 18, ‘ALTER DROP INDEX’ = 19, ‘ALTER MATERIALIZE INDEX’ = 20, ‘ALTER CLEAR INDEX’ = 21, ‘ALTER INDEX’ = 22, ‘ALTER ADD CONSTRAINT’ = 23, ‘ALTER DROP CONSTRAINT’ = 24, ‘ALTER CONSTRAINT’ = 25, ‘ALTER TTL’ = 26, ‘ALTER MATERIALIZE TTL’ = 27, ‘ALTER SETTINGS’ = 28, ‘ALTER MOVE PARTITION’ = 29, ‘ALTER FETCH PARTITION’ = 30, ‘ALTER FREEZE PARTITION’ = 31, ‘ALTER TABLE’ = 32, ‘ALTER VIEW REFRESH’ = 33, ‘ALTER VIEW MODIFY QUERY’ = 34, ‘ALTER VIEW’ = 35, ‘ALTER’ = 36, ‘CREATE DATABASE’ = 37, ‘CREATE TABLE’ = 38, ‘CREATE VIEW’ = 39, ‘CREATE DICTIONARY’ = 40, ‘CREATE TEMPORARY TABLE’ = 41, ‘CREATE’ = 42, ‘DROP DATABASE’ = 43, ‘DROP TABLE’ = 44, ‘DROP VIEW’ = 45, ‘DROP DICTIONARY’ = 46, ‘DROP’ = 47, ‘TRUNCATE’ = 48, ‘OPTIMIZE’ = 49, ‘KILL QUERY’ = 50, ‘CREATE USER’ = 51, ‘ALTER USER’ = 52, ‘DROP USER’ = 53, ‘CREATE ROLE’ = 54, ‘ALTER ROLE’ = 55, ‘DROP ROLE’ = 56, ‘ROLE ADMIN’ = 57, ‘CREATE ROW POLICY’ = 58, ‘ALTER ROW POLICY’ = 59, ‘DROP ROW POLICY’ = 60, ‘CREATE QUOTA’ = 61, ‘ALTER QUOTA’ = 62, ‘DROP QUOTA’ = 63, ‘CREATE SETTINGS PROFILE’ = 64, ‘ALTER SETTINGS PROFILE’ = 65, ‘DROP SETTINGS PROFILE’ = 66, ‘SHOW USERS’ = 67, ‘SHOW ROLES’ = 68, ‘SHOW ROW POLICIES’ = 69, ‘SHOW QUOTAS’ = 70, ‘SHOW SETTINGS PROFILES’ = 71, ‘SHOW ACCESS’ = 72, ‘ACCESS MANAGEMENT’ = 73, ‘SYSTEM SHUTDOWN’ = 74, ‘SYSTEM DROP DNS CACHE’ = 75, ‘SYSTEM DROP MARK CACHE’ = 76, ‘SYSTEM DROP UNCOMPRESSED CACHE’ = 77, ‘SYSTEM DROP MMAP CACHE’ = 78, ‘SYSTEM DROP COMPILED EXPRESSION CACHE’ = 79, ‘SYSTEM DROP CACHE’ = 80, ‘SYSTEM RELOAD CONFIG’ = 81, ‘SYSTEM RELOAD SYMBOLS’ = 82, ‘SYSTEM RELOAD DICTIONARY’ = 83, ‘SYSTEM RELOAD EMBEDDED DICTIONARIES’ = 84, ‘SYSTEM RELOAD’ = 85, ‘SYSTEM MERGES’ = 86, ‘SYSTEM TTL MERGES’ = 87, ‘SYSTEM FETCHES’ = 88, ‘SYSTEM MOVES’ = 89, ‘SYSTEM DISTRIBUTED SENDS’ = 90, ‘SYSTEM REPLICATED SENDS’ = 91, ‘SYSTEM SENDS’ = 92, ‘SYSTEM REPLICATION QUEUES’ = 93, ‘SYSTEM DROP REPLICA’ = 94, ‘SYSTEM SYNC REPLICA’ = 95, ‘SYSTEM RESTART REPLICA’ = 96, ‘SYSTEM FLUSH DISTRIBUTED’ = 97, ‘SYSTEM FLUSH LOGS’ = 98, ‘SYSTEM FLUSH’ = 99, ‘SYSTEM’ = 100, ‘dictGet’ = 101, ‘addressToLine’ = 102, ‘addressToSymbol’ = 103, ‘demangle’ = 104, ‘INTROSPECTION’ = 105, ‘FILE’ = 106, ‘URL’ = 107, ‘REMOTE’ = 108, ‘MONGO’ = 109, ‘MYSQL’ = 110, ‘POSTGRES’ = 111, ‘ODBC’ = 112, ‘JDBC’ = 113, ‘HDFS’ = 114, ‘S3’ = 115, ‘SOURCES’ = 116, ‘ALL’ = 117, ‘NONE’ = 118 )	用户的访问参数
database	Nullable(String)	库名
table	Nullable(String)	表名
column	Nullable(String)	授予访问权限的列的名称
is_partial_revoke	UInt8	逻辑值。 它显示某些权限是否已被撤销。 0 — 该行描述了部分撤销 1 — 该行描述了一项授权
grant_option	UInt8	使用 WITH GRANT OPTION 授予权限

7. privileges（权限列表）

name	type	comment
privilege	Enum8( ‘SHOW DATABASES’ = 0, ‘SHOW TABLES’ = 1, ‘SHOW COLUMNS’ = 2, ‘SHOW DICTIONARIES’ = 3, ‘SHOW’ = 4, ‘SELECT’ = 5, ‘INSERT’ = 6, ‘ALTER UPDATE’ = 7, ‘ALTER DELETE’ = 8, ‘ALTER ADD COLUMN’ = 9, ‘ALTER MODIFY COLUMN’ = 10, ‘ALTER DROP COLUMN’ = 11, ‘ALTER COMMENT COLUMN’ = 12, ‘ALTER CLEAR COLUMN’ = 13, ‘ALTER RENAME COLUMN’ = 14, ‘ALTER COLUMN’ = 15, ‘ALTER ORDER BY’ = 16, ‘ALTER SAMPLE BY’ = 17, ‘ALTER ADD INDEX’ = 18, ‘ALTER DROP INDEX’ = 19, ‘ALTER MATERIALIZE INDEX’ = 20, ‘ALTER CLEAR INDEX’ = 21, ‘ALTER INDEX’ = 22, ‘ALTER ADD CONSTRAINT’ = 23, ‘ALTER DROP CONSTRAINT’ = 24, ‘ALTER CONSTRAINT’ = 25, ‘ALTER TTL’ = 26, ‘ALTER MATERIALIZE TTL’ = 27, ‘ALTER SETTINGS’ = 28, ‘ALTER MOVE PARTITION’ = 29, ‘ALTER FETCH PARTITION’ = 30, ‘ALTER FREEZE PARTITION’ = 31, ‘ALTER TABLE’ = 32, ‘ALTER VIEW REFRESH’ = 33, ‘ALTER VIEW MODIFY QUERY’ = 34, ‘ALTER VIEW’ = 35, ‘ALTER’ = 36, ‘CREATE DATABASE’ = 37, ‘CREATE TABLE’ = 38, ‘CREATE VIEW’ = 39, ‘CREATE DICTIONARY’ = 40, ‘CREATE TEMPORARY TABLE’ = 41, ‘CREATE’ = 42, ‘DROP DATABASE’ = 43, ‘DROP TABLE’ = 44, ‘DROP VIEW’ = 45, ‘DROP DICTIONARY’ = 46, ‘DROP’ = 47, ‘TRUNCATE’ = 48, ‘OPTIMIZE’ = 49, ‘KILL QUERY’ = 50, ‘CREATE USER’ = 51, ‘ALTER USER’ = 52, ‘DROP USER’ = 53, ‘CREATE ROLE’ = 54, ‘ALTER ROLE’ = 55, ‘DROP ROLE’ = 56, ‘ROLE ADMIN’ = 57, ‘CREATE ROW POLICY’ = 58, ‘ALTER ROW POLICY’ = 59, ‘DROP ROW POLICY’ = 60, ‘CREATE QUOTA’ = 61, ‘ALTER QUOTA’ = 62, ‘DROP QUOTA’ = 63, ‘CREATE SETTINGS PROFILE’ = 64, ‘ALTER SETTINGS PROFILE’ = 65, ‘DROP SETTINGS PROFILE’ = 66, ‘SHOW USERS’ = 67, ‘SHOW ROLES’ = 68, ‘SHOW ROW POLICIES’ = 69, ‘SHOW QUOTAS’ = 70, ‘SHOW SETTINGS PROFILES’ = 71, ‘SHOW ACCESS’ = 72, ‘ACCESS MANAGEMENT’ = 73, ‘SYSTEM SHUTDOWN’ = 74, ‘SYSTEM DROP DNS CACHE’ = 75, ‘SYSTEM DROP MARK CACHE’ = 76, ‘SYSTEM DROP UNCOMPRESSED CACHE’ = 77, ‘SYSTEM DROP MMAP CACHE’ = 78, ‘SYSTEM DROP COMPILED EXPRESSION CACHE’ = 79, ‘SYSTEM DROP CACHE’ = 80, ‘SYSTEM RELOAD CONFIG’ = 81, ‘SYSTEM RELOAD SYMBOLS’ = 82, ‘SYSTEM RELOAD DICTIONARY’ = 83, ‘SYSTEM RELOAD EMBEDDED DICTIONARIES’ = 84, ‘SYSTEM RELOAD’ = 85, ‘SYSTEM MERGES’ = 86, ‘SYSTEM TTL MERGES’ = 87, ‘SYSTEM FETCHES’ = 88, ‘SYSTEM MOVES’ = 89, ‘SYSTEM DISTRIBUTED SENDS’ = 90, ‘SYSTEM REPLICATED SENDS’ = 91, ‘SYSTEM SENDS’ = 92, ‘SYSTEM REPLICATION QUEUES’ = 93, ‘SYSTEM DROP REPLICA’ = 94, ‘SYSTEM SYNC REPLICA’ = 95, ‘SYSTEM RESTART REPLICA’ = 96, ‘SYSTEM FLUSH DISTRIBUTED’ = 97, ‘SYSTEM FLUSH LOGS’ = 98, ‘SYSTEM FLUSH’ = 99, ‘SYSTEM’ = 100, ‘dictGet’ = 101, ‘addressToLine’ = 102, ‘addressToSymbol’ = 103, ‘demangle’ = 104, ‘INTROSPECTION’ = 105, ‘FILE’ = 106, ‘URL’ = 107, ‘REMOTE’ = 108, ‘MONGO’ = 109, ‘MYSQL’ = 110, ‘POSTGRES’ = 111, ‘ODBC’ = 112, ‘JDBC’ = 113, ‘HDFS’ = 114, ‘S3’ = 115, ‘SOURCES’ = 116, ‘ALL’ = 117, ‘NONE’ = 118 )	权限代码
aliases	Array(String)	别名
level	Nullable( Enum8( ‘GLOBAL’ = 0, ‘DATABASE’ = 1, ‘TABLE’ = 2, ‘DICTIONARY’ = 3, ‘VIEW’ = 4, ‘COLUMN’ = 5 ) )	权限范围
parent_group	Nullable( Enum8( ‘SHOW DATABASES’ = 0, ‘SHOW TABLES’ = 1, ‘SHOW COLUMNS’ = 2, ‘SHOW DICTIONARIES’ = 3, ‘SHOW’ = 4, ‘SELECT’ = 5, ‘INSERT’ = 6, ‘ALTER UPDATE’ = 7, ‘ALTER DELETE’ = 8, ‘ALTER ADD COLUMN’ = 9, ‘ALTER MODIFY COLUMN’ = 10, ‘ALTER DROP COLUMN’ = 11, ‘ALTER COMMENT COLUMN’ = 12, ‘ALTER CLEAR COLUMN’ = 13, ‘ALTER RENAME COLUMN’ = 14, ‘ALTER COLUMN’ = 15, ‘ALTER ORDER BY’ = 16, ‘ALTER SAMPLE BY’ = 17, ‘ALTER ADD INDEX’ = 18, ‘ALTER DROP INDEX’ = 19, ‘ALTER MATERIALIZE INDEX’ = 20, ‘ALTER CLEAR INDEX’ = 21, ‘ALTER INDEX’ = 22, ‘ALTER ADD CONSTRAINT’ = 23, ‘ALTER DROP CONSTRAINT’ = 24, ‘ALTER CONSTRAINT’ = 25, ‘ALTER TTL’ = 26, ‘ALTER MATERIALIZE TTL’ = 27, ‘ALTER SETTINGS’ = 28, ‘ALTER MOVE PARTITION’ = 29, ‘ALTER FETCH PARTITION’ = 30, ‘ALTER FREEZE PARTITION’ = 31, ‘ALTER TABLE’ = 32, ‘ALTER VIEW REFRESH’ = 33, ‘ALTER VIEW MODIFY QUERY’ = 34, ‘ALTER VIEW’ = 35, ‘ALTER’ = 36, ‘CREATE DATABASE’ = 37, ‘CREATE TABLE’ = 38, ‘CREATE VIEW’ = 39, ‘CREATE DICTIONARY’ = 40, ‘CREATE TEMPORARY TABLE’ = 41, ‘CREATE’ = 42, ‘DROP DATABASE’ = 43, ‘DROP TABLE’ = 44, ‘DROP VIEW’ = 45, ‘DROP DICTIONARY’ = 46, ‘DROP’ = 47, ‘TRUNCATE’ = 48, ‘OPTIMIZE’ = 49, ‘KILL QUERY’ = 50, ‘CREATE USER’ = 51, ‘ALTER USER’ = 52, ‘DROP USER’ = 53, ‘CREATE ROLE’ = 54, ‘ALTER ROLE’ = 55, ‘DROP ROLE’ = 56, ‘ROLE ADMIN’ = 57, ‘CREATE ROW POLICY’ = 58, ‘ALTER ROW POLICY’ = 59, ‘DROP ROW POLICY’ = 60, ‘CREATE QUOTA’ = 61, ‘ALTER QUOTA’ = 62, ‘DROP QUOTA’ = 63, ‘CREATE SETTINGS PROFILE’ = 64, ‘ALTER SETTINGS PROFILE’ = 65, ‘DROP SETTINGS PROFILE’ = 66, ‘SHOW USERS’ = 67, ‘SHOW ROLES’ = 68, ‘SHOW ROW POLICIES’ = 69, ‘SHOW QUOTAS’ = 70, ‘SHOW SETTINGS PROFILES’ = 71, ‘SHOW ACCESS’ = 72, ‘ACCESS MANAGEMENT’ = 73, ‘SYSTEM SHUTDOWN’ = 74, ‘SYSTEM DROP DNS CACHE’ = 75, ‘SYSTEM DROP MARK CACHE’ = 76, ‘SYSTEM DROP UNCOMPRESSED CACHE’ = 77, ‘SYSTEM DROP MMAP CACHE’ = 78, ‘SYSTEM DROP COMPILED EXPRESSION CACHE’ = 79, ‘SYSTEM DROP CACHE’ = 80, ‘SYSTEM RELOAD CONFIG’ = 81, ‘SYSTEM RELOAD SYMBOLS’ = 82, ‘SYSTEM RELOAD DICTIONARY’ = 83, ‘SYSTEM RELOAD EMBEDDED DICTIONARIES’ = 84, ‘SYSTEM RELOAD’ = 85, ‘SYSTEM MERGES’ = 86, ‘SYSTEM TTL MERGES’ = 87, ‘SYSTEM FETCHES’ = 88, ‘SYSTEM MOVES’ = 89, ‘SYSTEM DISTRIBUTED SENDS’ = 90, ‘SYSTEM REPLICATED SENDS’ = 91, ‘SYSTEM SENDS’ = 92, ‘SYSTEM REPLICATION QUEUES’ = 93, ‘SYSTEM DROP REPLICA’ = 94, ‘SYSTEM SYNC REPLICA’ = 95, ‘SYSTEM RESTART REPLICA’ = 96, ‘SYSTEM FLUSH DISTRIBUTED’ = 97, ‘SYSTEM FLUSH LOGS’ = 98, ‘SYSTEM FLUSH’ = 99, ‘SYSTEM’ = 100, ‘dictGet’ = 101, ‘addressToLine’ = 102, ‘addressToSymbol’ = 103, ‘demangle’ = 104, ‘INTROSPECTION’ = 105, ‘FILE’ = 106, ‘URL’ = 107, ‘REMOTE’ = 108, ‘MONGO’ = 109, ‘MYSQL’ = 110, ‘POSTGRES’ = 111, ‘ODBC’ = 112, ‘JDBC’ = 113, ‘HDFS’ = 114, ‘S3’ = 115, ‘SOURCES’ = 116, ‘ALL’ = 117, ‘NONE’ = 118 ) )	父级权限列表

8. role_grants（用户角色授权信息）

包含为用户和角色授权的信息。使用 GRANT role TO user 往该表添加记录。

name	type	comment
user_name	Nullable(String)	用户名
role_name	Nullable(String)	角色名
granted_role_name	String	授予 role_name 角色的角色名称。 要将一个角色授予另一个角色，请使用 GRANT role1 TO role2
granted_role_is_default	UInt8	显示 grant_role 是否为默认角色的标志 1 grant_role 是默认角色。 0 grant_role 不是默认角色。
with_admin_option	UInt8	显示 grant_role 是否是具有 ADMIN OPTION 权限的角色的标志 1 — 该角色具有 ADMIN OPTION 权限 0 — 没有 ADMIN OPTION 权限的角色