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 权限的角色 |
