各位大佬，想看那种网络设备/操作系统/数据库/中间件的测评命令清单，可在留言区留言！
依据 GB/T 22239-2019《信息安全技术 网络安全等级保护基本要求》第三级”安全计算环境” 条款，结合ClickHouse官方安全指南及现场测评实践。
适用版本：ClickHouse 21.x / 22.x / 23.x / 24.x（LTS版本）
一、身份鉴别
1.1 用户账户与认证
| 控制项 | 测评命令 | 达标判据 |
|---|---|---|
| 默认账户 | SELECT * FROM system.users WHERE name='default' | 修改默认口令或禁用 |
| 密码策略 | SELECT * FROM system.settings WHERE name LIKE '%password%' | 启用复杂度检查 |
| 认证方式 | SELECT name, auth_type FROM system.users | 优先SCRAM-SHA-256 |
| 登录失败 | 查看system.session_log | 记录失败尝试 |
| 连接加密 | SELECT * FROM system.clusters WHERE secure=1 | 启用TLS |
ClickHouse特有配置：
-- 连接ClickHouse（需启用认证）
clickhouse-client -u admin --password -h 192.168.1.100
-- 查看所有用户及认证方式
SELECT
name,
auth_type,
auth_params,
host_ip,
host_names,
default_database,
default_roles
FROM system.users
WHERE name NOTIN('','default');
-- 查看默认用户（高风险）
SELECT
name,
auth_type,
host_ip,
host_names,
is_current
FROM system.users
WHERE name ='default';
-- 查看用户权限
SELECT
user_name,
role_name,
database,
table,
column,
access_type,
grant_option
FROM system.grants
WHERE user_name NOTIN('','default')
ORDERBY user_name,database,table;
-- 查看角色定义
SELECT
name,
id,
storage
FROM system.roles;
-- 查看角色权限
SELECT
role_name,
database,
table,
column,
access_type,
grant_option
FROM system.role_grants;
-- 查看行级安全策略（Row Policy）
SELECT
name,
short_name,
database,
table,
condition,
is_restrictive
FROM system.row_policies;
-- 查看配额限制（资源控制）
SELECT
name,
id,
keys,
durations,
apply_to_all,
apply_to_list,
apply_to_except
FROM system.quotas;
-- 查看配额使用
SELECT
quota_name,
quota_key,
duration,
queries,
query_selects,
query_inserts,
errors,
result_rows,
read_rows,
execution_time
FROM system.quota_usage;
-- 查看设置配置
SELECT
name,
value,
changed,
description
FROM system.settings
WHERE name LIKE'%password%'
OR name LIKE'%auth%'
OR name LIKE'%session%'
OR name LIKE'%timeout%';1.2 认证配置详解
-- 查看配置文件中的用户定义（需访问配置文件）
-- 通常位于 /etc/clickhouse-server/users.d/ 和 /etc/clickhouse-server/users.xml
-- 查看当前配置的用户（内存中）
SELECT
name,
storage,
auth_type,
host_ip,
host_names,
profile,
default_database,
default_roles.allAS default_roles,
grant_roles.allAS grant_roles
FROM system.users
FORMAT Vertical;
-- 查看密码哈希算法
SELECT
name,
auth_type,
if(auth_type ='sha256_password','SHA-256',
if(auth_type ='double_sha1_password','Double SHA-1 (不安全)',
if(auth_type ='bcrypt_password','BCrypt',
if(auth_type ='ldap','LDAP',
if(auth_type ='kerberos','Kerberos', auth_type)))))ASalgorithm,
storage
FROM system.users
WHERE auth_type !='no_password';
-- 查看无密码用户（高风险）
SELECT name, host_ip, host_names
FROM system.users
WHERE auth_type ='no_password';
-- 查看网络限制
SELECT
name,
host_ip,
host_names,
host_regexp,
host_local,
host_any
FROM system.users
WHERE host_any =0;-- 有限制的用户
-- 查看当前会话认证
SELECT
user,
client_name,
client_version,
client_os,
interface,
is_ssl,
client_port,
client_address
FROM system.processes
WHERE is_current =1;二、访问控制
2.1 权限管理
| 控制项 | 测评命令 | 达标判据 |
|---|---|---|
| 最小权限 | SELECT * FROM system.grants | 按需授权 |
| 角色分离 | SELECT * FROM system.roles | 职责分离 |
| 数据库隔离 | SELECT * FROM system.databases | 敏感数据独立库 |
| 表级权限 | SELECT * FROM system.tables WHERE engine='View' | 视图脱敏 |
| 列级权限 | SELECT * FROM system.columns | 敏感列控制 |
ClickHouse特有配置：
-- 查看所有数据库
SELECT
name,
engine,
data_path,
metadata_path,
uuid,
comment
FROM system.databases;
-- 查看所有表及引擎类型
SELECT
database,
name,
engine,
is_temporary,
data_paths,
metadata_path,
uuid,
create_table_query
FROM system.tables
WHEREdatabaseNOTIN('system','INFORMATION_SCHEMA','information_schema');
-- 查看表权限详情
SELECT
database,
table,
name,
type,
position,
default_kind,
default_expression,
comment
FROM system.columns
WHEREdatabaseNOTIN('system','INFORMATION_SCHEMA');
-- 查看视图（用于数据脱敏）
SELECT
database,
name,
engine,
as_select,
create_table_query
FROM system.tables
WHEREengine='View'
ANDdatabaseNOTIN('system','INFORMATION_SCHEMA');
-- 查看物化视图（实时数据转换）
SELECT
database,
name,
engine,
as_select,
is_populated,
create_table_query
FROM system.tables
WHEREengine='MaterializedView';
-- 查看表级访问控制（通过GRANT）
SELECT
user_name,
role_name,
database,
table,
column,
access_type,
is_partial_revoke,
grant_option
FROM system.grants
WHEREtableISNOTNULL
ORDERBY user_name,database,table;
-- 查看数据库级权限
SELECT
user_name,
role_name,
database,
access_type,
grant_option
FROM system.grants
WHEREtableISNULLANDcolumnISNULL
ORDERBY user_name,database;
-- 查看拒绝权限（显式拒绝）
SELECT*FROM system.grants WHERE is_partial_revoke =1;
-- 查看当前用户权限
SHOW GRANTS;
-- 查看当前用户设置
SHOWCREATE SETTINGS PROFILE default;2.2 行级安全（Row-Level Security）
-- 查看行级策略定义
SELECT
name,
short_name,
database,
table,
condition,
if_condition,
is_restrictive,
apply_to_all,
apply_to_list,
apply_to_except
FROM system.row_policies
FORMAT Vertical;
-- 查看策略应用到哪些用户
SELECT
policy_name,
database,
table,
condition,
is_restrictive
FROM system.row_policies
WHERE apply_to_all =0;-- 非全局策略
-- 查看当前策略生效情况
SELECT
user_name,
policy_name,
database,
table,
condition
FROM system.current_roles, system.row_policies
WHERE has(current_roles, row_policies.apply_to_list);
-- 示例：创建行级策略（参考）
-- CREATE ROW POLICY policy_name ON database.table
-- FOR SELECT USING condition TO user_name;三、安全审计
3.1 审计日志配置
| 控制项 | 测评命令 | 达标判据 |
|---|---|---|
| 查询日志 | SELECT * FROM system.query_log LIMIT 10 | 启用并记录 |
| 会话日志 | SELECT * FROM system.session_log LIMIT 10 | 记录连接事件 |
| 文本日志 | SELECT * FROM system.text_log LIMIT 10 | 错误和事件 |
| 审计保留 | 查看配置文件query_log_ttl | ≥6个月 |
| 审计保护 | 操作系统权限640 | 非全局可读 |
ClickHouse特有配置：
-- 查看查询日志（需启用query_log）
SELECT
type,-- QueryStart, QueryFinish, ExceptionWhileProcessing
event_date,
event_time,
user,
query_id,
query,
query_kind,
databases,
tables,
columns,
exception,
is_initial_query,
user_agent,
client_ip,
client_name,
client_revision,
client_version_major,
client_version_minor,
client_version_patch,
http_method,
http_user_agent,
quota_key
FROM system.query_log
WHERE event_date >= today()-7
ORDERBY event_time DESC
LIMIT100;
-- 查看查询线程日志
SELECT
event_date,
event_time,
query_id,
name,
thread_name,
thread_id,
level,
message,
source_file,
source_line
FROM system.query_thread_log
WHERE event_date >= today()-1
LIMIT50;
-- 查看会话日志（连接审计）
SELECT
event_type,-- LoginSuccess, LoginFailure, Logout
event_date,
event_time,
user,
query_id,
address,
port,
quota_key
FROM system.session_log
WHERE event_date >= today()-7
ORDERBY event_time DESC
LIMIT100;
-- 查看文本日志（系统事件）
SELECT
event_time,
event_date,
level,-- Fatal, Critical, Error, Warning, Notice, Information, Debug, Trace
logger_name,
message,
source_file,
source_line
FROM system.text_log
WHERElevelIN('Error','Warning','Critical','Fatal')
AND event_date >= today()-7
ORDERBY event_time DESC
LIMIT100;
-- 查看崩溃日志
SELECT
event_date,
event_time,
signal,
thread_id,
query_id,
trace
FROM system.crash_log
WHERE event_date >= today()-30;
-- 查看metric日志（性能审计）
SELECT
event_date,
event_time,
metric_name,
value
FROM system.metric_log
WHERE event_date >= today()-1
LIMIT50;
-- 查看异步插入日志
SELECT
event_date,
event_time,
database,
table,
format,
query_id,
bytes,
exception
FROM system.asynchronous_insert_log
WHERE event_date >= today()-7
LIMIT50;
-- 查看part日志（数据变更审计）
SELECT
event_type,-- NewPart, MergeParts, DownloadPart, RemovePart, MutatePart
event_date,
event_time,
database,
table,
part_name,
partition_id,
rows,
size_in_bytes,
merged_from,
mutation_version,
source_part_names
FROM system.part_log
WHERE event_date >= today()-7
ORDERBY event_time DESC
LIMIT100;3.2 审计配置检查
-- 查看日志引擎配置（system表底层引擎）
SELECT
database,
name,
engine,
metadata_path,
data_paths,
uuid
FROM system.tables
WHEREdatabase='system'
AND name IN('query_log','session_log','text_log','crash_log','metric_log');
-- 查看日志保留策略（TTL）
SELECT
database,
table,
name,
type,
default_kind,
default_expression,
compression_codec
FROM system.columns
WHEREdatabase='system'
ANDtableIN('query_log','session_log','text_log')
AND default_kind ='TTL';
-- 查看当前设置（日志相关）
SELECT
name,
value,
changed,
description
FROM system.settings
WHERE name LIKE'%log%'
OR name LIKE'%audit%'
OR name LIKE'%trace%'
ORDERBY name;
-- 关键审计设置：
-- log_queries: 是否记录查询（应1）
-- log_query_threads: 是否记录查询线程（应1）
-- log_query_settings: 是否记录查询设置（应1）
-- log_sessions: 是否记录会话（应1）
-- log_processors_profiles: 是否记录处理器性能（可选）
-- query_log_ttl: 查询日志TTL（应≥180天）
-- query_thread_log_ttl: 查询线程日志TTL
-- session_log_ttl: 会话日志TTL
-- 查看日志刷新周期
SELECT name,valueFROM system.settings WHERE name ='flush_interval_milliseconds';
-- 查看日志存储目录
SELECT name,valueFROM system.settings WHERE name LIKE'%log_directory%';四、入侵防范
4.1 系统加固
| 控制项 | 测评命令 | 达标判据 |
|---|---|---|
| 版本补丁 | SELECT version() | 最新LTS版本 |
| 危险函数 | SELECT name FROM system.functions WHERE name LIKE '%url%' | 限制URL函数 |
| 远程表 | SELECT * FROM system.dictionaries | 受控使用 |
| 文件操作 | SELECT name FROM system.functions WHERE name LIKE '%file%' | 限制文件函数 |
| 代码执行 | SELECT name FROM system.functions WHERE name IN ('javascript','python') | 禁用或受控 |
ClickHouse特有配置：
-- 查看版本信息
SELECT version();
SELECT uptime();
-- 查看构建信息
SELECT
name,
value
FROM system.build_options
WHERE name IN('VERSION_FULL','VERSION_DESCRIBE','BUILD_DATE','GIT_HASH');
-- 查看危险函数（需限制）
SELECT
name,
is_aggregate,
case_insensitive,
alias_to,
create_query
FROM system.functions
WHERE name IN(
'url','urlEngine',-- URL访问
'file','fileEngine',-- 文件访问
'remote','remoteSecure',-- 远程查询
'mysql','postgresql','odbc','jdbc',-- 外部数据库
's3','hdfs','azureBlobStorage',-- 云存储
'executable','executablePool',-- 外部程序
'javascript','python'-- 脚本执行（实验性）
)
ORDERBY name;
-- 查看表函数（远程数据访问）
SELECT
name,
description
FROM system.table_functions
WHERE name IN('url','file','remote','s3','hdfs','mysql','postgresql');
-- 查看外部字典（数据集成）
SELECT
name,
uuid,
origin,
type,
key.names,
key.types,
attribute.names,
attribute.types,
bytes_allocated,
query_count,
hit_rate,
found_rate,
element_count,
load_time,
last_successful_update_time,
source
FROM system.dictionaries;
-- 查看当前进程（活动监控）
SELECT
user,
address,
port,
initial_address,
initial_port,
interface,
is_ssl,
os_user,
client_version_major,
client_version_minor,
client_version_patch,
http_method,
http_user_agent,
quota_key,
elapsed,
read_rows,
read_bytes,
total_rows_approx,
written_rows,
written_bytes,
memory_usage,
peak_memory_usage,
query,
query_id,
is_cancelled,
is_all_data_sent
FROM system.processes;
-- 查看累积系统指标（性能基线）
SELECT
metric,
value,
description
FROM system.metrics
WHERE metric LIKE'%Query%'
OR metric LIKE'%Merge%'
OR metric LIKE'%Part%'
OR metric LIKE'%Memory%'
OR metric LIKE'%Error%';
-- 查看事件计数（异常检测）
SELECT
event,
value,
description
FROM system.events
WHERE event LIKE'%Error%'
OR event LIKE'%Fail%'
OR event LIKE'%Reject%'
ORDERBYvalueDESC
LIMIT20;
-- 查看异步指标（后台任务）
SELECT
metric,
value,
description
FROM system.asynchronous_metrics
WHERE metric LIKE'%Replica%'
OR metric LIKE'%Queue%'
OR metric LIKE'%Background%';
-- 查看后台任务队列
SELECT
name,
type,
num_tries,
last_exception,
last_attempt_time
FROM system.replication_queue
LIMIT20;
-- 查看分布式DDL队列
SELECT
entry,
host_name,
status,
error_code,
error_message,
query_create_time,
query_commit_time
FROM system.distributed_ddl_queue
WHEREstatus!='Finished'
LIMIT20;4.2 资源限制与防护
-- 查看当前设置（资源限制）
SELECT
name,
value,
changed,
description
FROM system.settings
WHERE name IN(
'max_concurrent_queries',
'max_concurrent_queries_for_user',
'max_concurrent_queries_for_all_users',
'max_memory_usage',
'max_memory_usage_for_user',
'max_memory_usage_for_all_queries',
'max_execution_time',
'max_execution_speed',
'max_bytes_before_external_group_by',
'max_bytes_before_external_sort',
'max_result_rows',
'max_result_bytes',
'max_rows_to_read',
'max_bytes_to_read',
'max_rows_to_group_by',
'max_bytes_to_transfer',
'max_rows_in_set',
'max_rows_in_distinct',
'max_rows_in_join',
'max_execution_depth',
'max_ast_depth',
'max_ast_elements',
'max_expanded_ast_elements',
'readonly',
'allow_ddl',
'allow_introspection_functions'
)
ORDERBY name;
-- 查看用户配置（资源限制）
SELECT
name,
storage,
num_users,
apply_to_all,
apply_to_list,
apply_to_except
FROM system.settings_profiles;
-- 查看具体配置项
SELECT
profile_name,
name AS setting_name,
value,
min,
max,
readonly,
inherit_profile
FROM system.settings_profile_elements
WHERE profile_name ='default';
-- 查看当前会话限制
SHOW PROCESSLIST;
-- 查看查询限制器（Query Limiter）
SELECT
name,
origin,
type,
keys,
durations,
apply_to_all,
apply_to_list,
apply_to_except
FROM system.quotas;
-- 查看配额限制详情
SELECT
quota_name,
duration,
queries,
errors,
result_rows,
result_bytes,
read_rows,
read_bytes,
execution_time
FROM system.quota_limits;
-- 查看当前配额使用
SELECT
quota_name,
quota_key,
duration,
queries,
query_selects,
query_inserts,
errors,
result_rows,
result_bytes,
read_rows,
read_bytes,
execution_time
FROM system.quota_usage;五、恶意代码防范
-- 查看可疑查询模式（SQL注入特征）
SELECT
user,
query_id,
query,
client_ip,
exception
FROM system.query_log
WHERE query ILIKE'%DROP%TABLE%'
OR query ILIKE'%DELETE%FROM%'
OR query ILIKE'%TRUNCATE%TABLE%'
OR query ILIKE'%ALTER%TABLE%DROP%'
OR query ILIKE'%GRANT%ALL%'
OR query ILIKE'%EXECUTE%FILE%'
OR query ILIKE'%url%http%'
OR query ILIKE'%remote%select%'
AND event_date >= today()-7
ORDERBY event_time DESC
LIMIT50;
-- 查看异常登录尝试
SELECT
event_type,
user,
address,
port,
event_time
FROM system.session_log
WHERE event_type ='LoginFailure'
AND event_date >= today()-1
ORDERBY event_time DESC;
-- 查看来自异常IP的连接
SELECT
user,
client_ip,
client_name,
count()AS connection_count,
min(event_time)AS first_seen,
max(event_time)AS last_seen
FROM system.session_log
WHERE event_date >= today()-7
AND client_ip NOTIN('127.0.0.1','::1','192.168.%','10.%')
GROUPBYuser, client_ip, client_name
HAVINGcount()>100
ORDERBY connection_count DESC;
-- 查看长时间运行的查询（DoS检测）
SELECT
user,
query_id,
query,
elapsed,
read_rows,
read_bytes,
memory_usage,
is_cancelled
FROM system.processes
WHERE elapsed >300-- 超过5分钟
AND is_cancelled =0;
-- 查看高内存使用查询
SELECT
user,
query_id,
query,
memory_usage,
peak_memory_usage,
temp_files_on_disk,
temp_data_on_disk
FROM system.processes
WHERE memory_usage >1000000000-- 超过1GB
ORDERBY memory_usage DESC;
-- 查看错误率异常的用户
SELECT
user,
count()AS total_queries,
sum(if(type='ExceptionWhileProcessing',1,0))AS error_queries,
round(error_queries / total_queries *100,2)AS error_rate
FROM system.query_log
WHERE event_date >= today()-1
GROUPBYuser
HAVING error_rate >50-- 错误率超过50%
ORDERBY error_rate DESC;六、可信验证
| 控制项 | 测评命令 | 达标判据 |
|---|---|---|
| 传输加密 | SELECT is_ssl FROM system.processes | 启用TLS |
| 集群加密 | SELECT secure FROM system.clusters | 节点间TLS |
| 密码哈希 | SELECT auth_type FROM system.users | SCRAM-SHA-256/BCrypt |
| 数据校验 | 查看part_log | 校验和验证 |
| 备份加密 | 配置文件backup_encryption | 启用加密 |
ClickHouse特有配置：
-- 查看TLS/SSL配置
SELECT
name,
value,
changed,
description
FROM system.settings
WHERE name LIKE'%ssl%'
OR name LIKE'%tls%'
OR name LIKE'%openSSL%'
ORDERBY name;
-- 查看当前连接加密状态
SELECT
user,
client_ip,
client_port,
interface,
is_ssl,
transport_protocol
FROM system.processes
WHERE is_current =1;
-- 查看集群安全配置
SELECT
cluster,
shard_num,
replica_num,
host_name,
host_address,
port,
is_local,
user,
default_database,
errors_count,
estimated_recovery_time,
is_up,
secure -- 是否使用TLS连接
FROM system.clusters
FORMAT Vertical;
-- 查看副本连接（分布式安全）
SELECT
database,
table,
engine,
is_leader,
is_readonly,
is_session_expired,
future_parts,
parts_to_check,
queue_size,
inserts_in_queue,
merges_in_queue,
last_queue_update,
absolute_delay,
total_replicas,
active_replicas
FROM system.replicas
WHERE is_readonly =1OR is_session_expired =1OR errors_count >0;
-- 查看分布式表（跨节点查询）
SELECT
database,
name,
engine,
cluster,
create_table_query
FROM system.tables
WHEREengine='Distributed';
-- 查看密码哈希算法分布
SELECT
auth_type,
count()AS user_count,
CASE
WHEN auth_type ='bcrypt_password'THEN'推荐'
WHEN auth_type ='sha256_password'THEN'安全'
WHEN auth_type ='double_sha1_password'THEN'弱（Double SHA-1）'
WHEN auth_type ='no_password'THEN'危险'
ELSE'未知'
ENDAS security_level
FROM system.users
WHERE name !=''
GROUPBY auth_type
ORDERBY user_count DESC;
-- 查看数据校验（校验和）
SELECT
database,
table,
name,
hash_of_all_files,
hash_of_uncompressed_files,
uncompressed_hash_of_compressed_files,
city_hash_of_all_files,
check_hash_of_all_files,
check_hash_of_compressed_files
FROM system.parts
WHERE active =1
LIMIT10;
-- 验证数据完整性
SELECT
database,
table,
count()AS parts_count,
sum(if(check_hash_of_all_files = hash_of_all_files,1,0))AS valid_parts,
round(valid_parts / parts_count *100,2)AS integrity_rate
FROM system.parts
WHERE active =1
GROUPBYdatabase,table
HAVING integrity_rate <100;七、数据备份与恢复
| 控制项 | 测评命令 | 达标判据 |
|---|---|---|
| 备份配置 | SELECT * FROM system.backup_list | 定期执行 |
| 备份加密 | 配置文件backup_encryption | 启用加密 |
| 增量备份 | SELECT * FROM system.backups | 支持增量 |
| 异地备份 | 检查remote表函数 | 配置异地存储 |
| 恢复测试 | 查看restore日志 | 定期演练 |
ClickHouse特有配置：
-- 查看备份列表（BACKUP/RESTORE功能，22.8+）
SELECT
id,
name,
status,
status_changed_time,
error,
num_files,
total_size,
num_entries,
uncompressed_size,
compressed_size,
files_read,
bytes_read
FROM system.backups
ORDERBY status_changed_time DESC
LIMIT20;
-- 查看备份配置（需配置文件支持）
-- 查看备份存储（S3/本地/远程）
SELECT
name,
type,
disk_path,
cache_path,
total_space,
free_space,
unreserved_space,
keep_free_space,
is_encrypted,
is_read_only,
is_write_once,
is_remote
FROM system.disks
WHERE name LIKE'%backup%';
-- 查看表备份状态（使用FREEZE）
SELECT
database,
table,
engine,
data_paths,
metadata_path,
uuid,
create_table_query
FROM system.tables
WHEREengineNOTIN('View','MaterializedView','Dictionary','System');
-- 查看part备份（影子副本）
SELECT
database,
table,
partition_id,
name,
path,
hash_of_all_files,
bytes_on_disk
FROM system.detached_parts;
-- 查看复制状态（高可用备份）
SELECT
database,
table,
is_leader,
total_replicas,
active_replicas,
absolute_delay,
last_queue_update,
zookeeper_path,
replica_name,
replica_path,
is_readonly,
is_session_expired,
future_parts,
parts_to_check,
queue_size,
inserts_in_queue,
merges_in_queue,
part_mutations_in_queue,
last_queue_update_exception
FROM system.replicas
WHERE total_replicas >1;
-- 查看ZooKeeper协调（集群一致性）
SELECT
name,
value,
path,
czxid,
mzxid,
ctime,
mtime,
version,
cversion,
aversion,
ephemeralOwner
FROM system.zookeeper
WHERE path ='/clickhouse'
LIMIT20;
-- 查看复制延迟
SELECT
database,
table,
type,
source_replica,
parts_to_merge,
new_part_name,
create_time,
last_attempt_time,
last_exception,
num_tries,
num_postponed,
postpone_reason,
last_postpone_time
FROM system.replication_queue
WHERE create_time <now()-INTERVAL1HOUR
LIMIT20;八、ClickHouse特有安全功能
8.1 高级安全特性
-- 查看约束（数据完整性）
SELECT
database,
table,
name,
type,
expr,
is_active
FROM system.constraints;
-- 查看投影（查询优化+安全）
SELECT
database,
table,
name,
type,
sort_keys,
primary_keys,
has_aggregate_columns,
columns,
query,
bytes,
rows
FROM system.projections;
-- 查看数据跳过索引（性能+安全）
SELECT
database,
table,
name,
type,
expr,
granularity
FROM system.data_skipping_indices;
-- 查看物化视图（实时数据转换）
SELECT
database,
table,
target_table,
select_query,
is_populated
FROM system.view_refreshes;
-- 查看实时视图（Live View，实验性）
SELECT
database,
name,
engine,
as_select
FROM system.tables
WHEREengine='LiveView';
-- 查看窗口视图（Window View，实验性）
SELECT
database,
name,
engine,
as_select,
target_table
FROM system.tables
WHEREengine='WindowView';8.2 性能与安全监控
-- 生成等保安全报告
CREATETABLEIFNOTEXISTS security_audit_report (
check_item String,
status String,
details String,
check_time DateTimeDEFAULTnow()
)ENGINE= MergeTree()
ORDERBY check_time;
INSERTINTO security_audit_report
SELECT
'身份鉴别-空口令检查'AS check_item,
if(count()=0,'合规','不合规')ASstatus,
if(count()=0,'未发现空口令用户', concat('发现', toString(count()),'个空口令用户:', groupArray(name)))AS details,
now()AS check_time
FROM system.users
WHERE auth_type ='no_password';
INSERTINTO security_audit_report
SELECT
'身份鉴别-弱哈希算法'AS check_item,
if(count()=0,'合规','不合规')ASstatus,
if(count()=0,'未使用弱哈希', concat('发现', toString(count()),'个用户使用Double SHA-1:', groupArray(name)))AS details,
now()AS check_time
FROM system.users
WHERE auth_type ='double_sha1_password';
INSERTINTO security_audit_report
SELECT
'访问控制-默认用户'AS check_item,
if(count()=0,'合规','不合规')ASstatus,
if(count()=0,'默认用户已禁用', concat('默认用户存在:', groupArray(name)))AS details,
now()AS check_time
FROM system.users
WHERE name IN('default','')AND auth_type !='no_password';
INSERTINTO security_audit_report
SELECT
'安全审计-查询日志'AS check_item,
if(value='1','合规','不合规')ASstatus,
if(value='1','查询日志已启用','查询日志未启用')AS details,
now()AS check_time
FROM system.settings
WHERE name ='log_queries';
INSERTINTO security_audit_report
SELECT
'安全审计-会话日志'AS check_item,
if(value='1','合规','不合规')ASstatus,
if(value='1','会话日志已启用','会话日志未启用')AS details,
now()AS check_time
FROM system.settings
WHERE name ='log_sessions';
-- 查看安全报告
SELECT*FROM security_audit_report ORDERBY check_time DESC;九、一键巡检脚本（ClickHouse）
#!/bin/bash
# ClickHouse 等保三级一键巡检脚本
# 适用：ClickHouse 21.x / 22.x / 23.x / 24.x
CH_HOST=${1:-localhost}
CH_PORT=${2:-9000}
CH_USER=${3:-default}
CH_PASSWORD=${4:-}
echo"===== ClickHouse 等保三级巡检 ====="
echo"巡检时间：$(date)"
echo"服务器：$CH_HOST:$CH_PORT"
echo"用户：$CH_USER"
echo""
# 连接函数
ch_query(){
localquery="$1"
if[-n"$CH_PASSWORD"];then
clickhouse-client -h"$CH_HOST"-p"$CH_PORT"-u"$CH_USER"--password"$CH_PASSWORD"-q"$query"2>/dev/null
else
clickhouse-client -h"$CH_HOST"-p"$CH_PORT"-u"$CH_USER"-q"$query"2>/dev/null
fi
}
echo"===== 1 身份鉴别 ====="
echo"--- 版本信息 ---"
ch_query "SELECT version()"
echo"--- 用户列表 ---"
ch_query "SELECT name, auth_type, host_ip, host_names FROM system.users WHERE name != '' FORMAT PrettyCompact"
echo"--- 空口令检查 ---"
EMPTY_USERS=$(ch_query "SELECT count() FROM system.users WHERE auth_type = 'no_password'")
if["$EMPTY_USERS"-gt0];then
echo"[警告] 发现 $EMPTY_USERS 个无密码用户"
ch_query "SELECT name FROM system.users WHERE auth_type = 'no_password'"
else
echo"[合规] 未发现无密码用户"
fi
echo"--- 弱哈希检查 ---"
WEAK_HASH=$(ch_query "SELECT count() FROM system.users WHERE auth_type = 'double_sha1_password'")
if["$WEAK_HASH"-gt0];then
echo"[警告] 发现 $WEAK_HASH 个用户使用弱哈希算法(Double SHA-1)"
else
echo"[合规] 未使用弱哈希算法"
fi
echo"--- 默认用户检查 ---"
DEFAULT_USER=$(ch_query "SELECT count() FROM system.users WHERE name IN ('default', '') AND auth_type != 'no_password'")
if["$DEFAULT_USER"-gt0];then
echo"[警告] 默认用户未禁用"
else
echo"[合规] 默认用户已禁用或无认证"
fi
echo""
echo"===== 2 访问控制 ====="
echo"--- 角色列表 ---"
ch_query "SELECT name FROM system.roles FORMAT PrettyCompact"
echo"--- 行级策略 ---"
ch_query "SELECT name, database, table, condition FROM system.row_policies FORMAT PrettyCompact"
echo"--- 配额限制 ---"
ch_query "SELECT name, keys, durations FROM system.quotas FORMAT PrettyCompact"
echo""
echo"===== 3 安全审计 ====="
echo"--- 审计设置 ---"
ch_query "SELECT name, value FROM system.settings WHERE name LIKE '%log%' AND changed = 1 FORMAT PrettyCompact"
echo"--- 查询日志状态 ---"
LOG_QUERIES=$(ch_query "SELECT value FROM system.settings WHERE name = 'log_queries'")
if["$LOG_QUERIES"="1"];then
echo"[合规] 查询日志已启用"
else
echo"[警告] 查询日志未启用"
fi
echo"--- 会话日志状态 ---"
LOG_SESSIONS=$(ch_query "SELECT value FROM system.settings WHERE name = 'log_sessions'")
if["$LOG_SESSIONS"="1"];then
echo"[合规] 会话日志已启用"
else
echo"[警告] 会话日志未启用"
fi
echo"--- 最近查询日志 ---"
ch_query "SELECT type, user, query_id, substring(query, 1, 50) AS query_preview FROM system.query_log WHERE event_date >= today() ORDER BY event_time DESC LIMIT 5 FORMAT PrettyCompact"
echo"--- 最近会话日志 ---"
ch_query "SELECT event_type, user, address, event_time FROM system.session_log WHERE event_date >= today() ORDER BY event_time DESC LIMIT 5 FORMAT PrettyCompact"
echo""
echo"===== 4 入侵防范 ====="
echo"--- 危险函数检查 ---"
ch_query "SELECT name FROM system.functions WHERE name IN ('url', 'file', 'remote', 'executable') FORMAT PrettyCompact"
echo"--- 外部字典检查 ---"
ch_query "SELECT name, type, source FROM system.dictionaries FORMAT PrettyCompact"
echo"--- 当前进程 ---"
ch_query "SELECT user, client_ip, elapsed, read_rows, memory_usage, substring(query, 1, 50) FROM system.processes ORDER BY elapsed DESC LIMIT 5 FORMAT PrettyCompact"
echo"--- 错误事件 ---"
ch_query "SELECT event, value FROM system.events WHERE event LIKE '%Error%' OR event LIKE '%Fail%' ORDER BY value DESC LIMIT 10 FORMAT PrettyCompact"
echo""
echo"===== 5 可信验证 ====="
echo"--- 连接加密 ---"
ch_query "SELECT is_ssl, count() FROM system.processes GROUP BY is_ssl FORMAT PrettyCompact"
echo"--- 集群安全 ---"
ch_query "SELECT cluster, shard_num, replica_num, host_name, secure FROM system.clusters FORMAT PrettyCompact"
echo"--- 密码算法分布 ---"
ch_query "SELECT auth_type, count() AS user_count FROM system.users WHERE name != '' GROUP BY auth_type FORMAT PrettyCompact"
echo""
echo"===== 6 数据备份 ====="
echo"--- 备份列表 ---"
ch_query "SELECT name, status, total_size, status_changed_time FROM system.backups ORDER BY status_changed_time DESC LIMIT 5 FORMAT PrettyCompact"2>/dev/null ||echo"未启用BACKUP功能或版本不支持"
echo"--- 副本状态 ---"
ch_query "SELECT database, table, is_leader, total_replicas, active_replicas, is_readonly, is_session_expired FROM system.replicas WHERE total_replicas > 0 LIMIT 10 FORMAT PrettyCompact"
echo"--- 复制队列 ---"
ch_query "SELECT database, table, type, status, num_tries FROM system.replication_queue WHERE status != 'Finished' LIMIT 5 FORMAT PrettyCompact"
echo""
echo"===== 通用安全检查 ====="
echo"--- 配置文件权限 ---"
ls-la /etc/clickhouse-server/users.xml /etc/clickhouse-server/config.xml 2>/dev/null |awk'{print $1, $3, $4, $9}'
echo"--- 数据目录权限 ---"
ls-ld /var/lib/clickhouse/ 2>/dev/null |awk'{print $1, $3, $4, $9}'
echo"--- 日志目录权限 ---"
ls-ld /var/log/clickhouse-server/ 2>/dev/null |awk'{print $1, $3, $4, $9}'
echo"--- 端口监听 ---"
ss -tulnp|grep-E'8123|9000|9009|9440|9281'|awk'{print $1, $5, $7}'
echo""
echo"===== 巡检完成 ====="
echo"重点关注以下高风险项："
echo"1. 未启用认证或存在无密码用户"
echo"2. 使用弱哈希算法(double_sha1_password)"
echo"3. 未启用查询日志或会话日志"
echo"4. 存在危险的URL/FILE/REMOTE函数"
echo"5. 未启用TLS/SSL加密"
echo"6. 副本不同步或复制队列堆积"
echo"7. 配置文件权限过于开放"
echo"8. 未配置资源配额限制"十、高风险项重点核查清单
| 检查项 | 验证命令 | 不合规判定 | 整改建议 |
|---|---|---|---|
| 未启用认证 | SELECT count() FROM system.users WHERE auth_type = 'no_password' | >0 | 为用户设置密码 |
| 使用弱哈希 | SELECT count() FROM system.users WHERE auth_type = 'double_sha1_password' | >0 | 改为bcrypt_password |
| 默认用户未禁用 | SELECT name FROM system.users WHERE name IN ('default','') | 存在且启用 | 禁用或删除默认用户 |
| 未启用查询日志 | SELECT value FROM system.settings WHERE name='log_queries' | 0 | 启用log_queries |
| 未启用会话日志 | SELECT value FROM system.settings WHERE name='log_sessions' | 0 | 启用log_sessions |
| 存在危险函数 | SELECT name FROM system.functions WHERE name IN ('url','file','remote') | 存在且未限制 | 限制使用或禁用 |
| 未配置行级策略 | SELECT count() FROM system.row_policies | 0（敏感表） | 为敏感表创建策略 |
| 未启用TLS | SELECT is_ssl FROM system.processes WHERE is_current=1 | 0 | 配置SSL证书 |
| 集群未加密 | SELECT secure FROM system.clusters | 0 | 启用secure模式 |
| 副本不同步 | SELECT is_readonly FROM system.replicas | 存在1 | 检查复制延迟 |
十一、ClickHouse与其他数据库对比
| 对比项 | ClickHouse | MySQL | PostgreSQL | Elasticsearch |
|---|---|---|---|---|
| 数据模型 | 列式 | 行式 | 行式 | 文档 |
| 默认认证 | 弱（允许空用户） | 较安全 | 较安全 | 较安全 |
| 原生RBAC | 支持（较新） | 支持 | 支持 | 支持 |
| 行级安全 | 支持 | 企业版 | 支持 | 不支持 |
| 传输加密 | TLS | TLS | TLS | TLS |
| 审计功能 | 内置（需启用） | 企业版/插件 | 内置/插件 | 内置 |
| 数据压缩 | 原生支持 | 需配置 | 需配置 | 原生支持 |
| 等保合规难度 | 中 | 低 | 低 | 中 |
| 国密支持 | 需配置 | 需配置 | 需配置 | 需配置 |
十二、等保测评执行要点
1. 认证加固优先级
<!-- /etc/clickhouse-server/users.xml 关键配置 -->
<users>
<!-- 删除或禁用默认用户 -->
<default>
<passwordremove="1"/>
<networks>
<ipremove="1"/>
<hostremove="1"/>
</networks>
<profile>restricted</profile>
<quota>restricted</quota>
</default>
<!-- 强认证用户 -->
<admin>
<password_double_sha1_hexremove="1"/>
<password_sha256_hexremove="1"/>
<!-- 使用bcrypt（推荐） -->
<password_bcrypt>$2y$10$...</password_bcrypt>
<networks>
<ip>192.168.1.0/24</ip>
<host>localhost</host>
</networks>
<profile>default</profile>
<quota>default</quota>
</admin>
</users>2. 审计配置建议
<!-- /etc/clickhouse-server/config.xml 审计配置 -->
<query_log>
<database>system</database>
<table>query_log</table>
<partition_by>toYYYYMM(event_date)</partition_by>
<flush_interval_milliseconds>7500</flush_interval_milliseconds>
<ttl>event_date + INTERVAL 180 DAY</ttl><!-- 保留6个月 -->
</query_log>
<session_log>
<database>system</database>
<table>session_log</table>
<partition_by>toYYYYMM(event_date)</partition_by>
<flush_interval_milliseconds>7500</flush_interval_milliseconds>
<ttl>event_date + INTERVAL 180 DAY</ttl>
</session_log>3. 国密合规（如需）
- ClickHouse原生不支持国密算法，需通过以下方式实现：
- 使用国密SSL代理（如Tongsuo/BabaSSL）
- 在应用层实现SM2/SM3/SM4加密
- 使用国密硬件密码机存储密钥
4. 现场访谈要点
- 是否定期更换管理员口令（≤90天）
- 是否启用查询日志和会话日志并定期分析
- 是否对敏感数据表配置行级安全策略
- 是否配置资源配额防止DoS攻击
- 是否定期验证备份可恢复性
- 集群节点间通信是否启用TLS加密
5. 版本差异
| 功能项 | 21.x | 22.x | 23.x | 24.x |
|---|---|---|---|---|
| RBAC增强 | 基础 | 角色继承 | 更细粒度 | 完整 |
| 行级策略 | 支持 | 增强 | 增强 | 增强 |
| BACKUP/RESTORE | 实验性 | 生产可用 | 增强 | 完整 |
| 密码算法 | SHA-1/SHA-256 | +BCrypt | +SCRAM | 推荐BCrypt |
| 审计日志 | 基础 | 会话日志 | 增强 | 完整 |
参考标准：GB/T 22239-2019、GB/T 28448-2019、ClickHouse官方安全指南、CIS Benchmark（参考）
适用版本：ClickHouse 21.x LTS / 22.x LTS / 23.x LTS / 24.x LTS
验证环境：x86_64 / ARM64 / 国产化芯片（飞腾/鲲鹏/龙芯/海光/兆芯/申威）
声明：来自汪汪虚拟空间，仅代表创作者观点。链接：https://eyangzhen.com/7781.html
