权限与安全加固

<users>
  <default>
    <access_management>1</access_management>   <!-- 允许 default 管理权限 -->
  </default>
</users>

-- 查看现有对象
SELECT * FROM system.users;
SELECT * FROM system.roles;
SELECT * FROM system.grants;
SELECT * FROM system.quotas;
SELECT * FROM system.row_policies;

-- 推荐：SHA-256 口令
CREATE USER analyst IDENTIFIED WITH sha256_password BY 'StrongPassw0rd!';

-- 明文口令（仅测试）
CREATE USER dev IDENTIFIED WITH plaintext_password BY 'dev123';

-- 带默认设置
CREATE USER readonly_user
IDENTIFIED WITH sha256_password BY 'read123'
SETTINGS max_memory_usage = 4_000_000_000, readonly = 1;

-- 建角色
CREATE ROLE read_only;
CREATE ROLE etl_writer;

-- 授权到角色
GRANT SELECT ON default.* TO read_only;
GRANT SELECT, INSERT ON default.events TO etl_writer;

-- 角色授予用户
GRANT read_only TO analyst;
GRANT read_only, etl_writer TO dev;

-- 查看用户拥有的角色
SELECT user_name, granted_role_name
FROM system.role_grants WHERE user_name = 'analyst';

ALTER USER analyst SETTINGS max_memory_usage = 8_000_000_000;
ALTER ROLE read_only SETTINGS readonly = 1;
DROP USER analyst;
DROP ROLE read_only;

-- 表级
GRANT SELECT ON default.events TO analyst;
GRANT SELECT, INSERT, ALTER UPDATE ON default.events TO etl_writer;

-- 库级通配
GRANT SELECT ON default.* TO analyst;
GRANT ALL ON *.* TO admin WITH GRANT OPTION;

-- 列级（24.x 起支持）
GRANT SELECT(user_id, event_type) ON default.events TO analyst;

-- 回收权限
REVOKE INSERT ON default.events FROM etl_writer;

-- 校验权限
SHOW GRANTS FOR analyst;
SELECT * FROM system.grants WHERE user_name = 'analyst';

-- 每小时最多 100 次查询
CREATE QUOTA q_analyst FOR INTERVAL 1 HOUR
MAX QUERIES 100
TO analyst;

-- 每日最多读 1 亿行
CREATE QUOTA q_api FOR INTERVAL 1 DAY
MAX READ ROWS 100_000_000
TO api_user;

-- 可以叠加多个区间
CREATE QUOTA q_strict FOR INTERVAL 1 MINUTE MAX QUERIES 30,
                          INTERVAL 1 HOUR MAX QUERIES 300,
                          INTERVAL 1 DAY  MAX EXECUTION TIME 3600
TO dev;

-- 按用户/IP 分组独立计数
CREATE QUOTA q_by_ip FOR INTERVAL 1 HOUR
KEYED BY ip_address
MAX QUERIES 100
TO analyst;

-- 查看当前配额用量
SELECT * FROM system.quotas_usage;
SELECT * FROM system.query_log WHERE quota_key != '' LIMIT 5;

-- 建表：带租户字段
CREATE TABLE tenant_orders (
    tenant_id UInt64,
    order_id UInt64,
    amount Float64,
    order_time DateTime
) ENGINE = MergeTree() ORDER BY order_time;

-- 行策略：每个用户只能看自己租户的数据
CREATE ROW POLICY rp_tenant
ON tenant_orders
FOR SELECT
USING tenant_id = toUInt64(currentUser())
TO analyst;

-- 允许某角色看全部（豁免策略）
CREATE ROW POLICY rp_all ON tenant_orders
FOR SELECT USING 1
TO admin;

-- 查看与删除
SELECT * FROM system.row_policies;
DROP ROW POLICY rp_tenant ON tenant_orders;

<openSSL>
  <server>
    <certificateFile>/etc/clickhouse-server/server.crt</certificateFile>
    <privateKeyFile>/etc/clickhouse-server/server.key</privateKeyFile>
    <verificationMode>none</verificationMode>
  </server>
</openSSL>

# 原生协议安全端口 9440
clickhouse-client --secure --port 9440
# HTTP 安全端口 8443
curl -s https://ch.example.com:8443/?query=SELECT%201

<listen_host>127.0.0.1</listen_host>     <!-- 只监听本机，配合反代 -->
<listen_host>::</listen_host>            <!-- 按需放开 -->

<!-- 用户级网络限制（users.xml） -->
<users><analyst>
  <networks><ip>10.0.0.0/8</ip><ip>172.16.3.10</ip></networks>
</analyst></users>

-- 会话级强制记录
SET log_queries = 1;

-- 用户默认开启
CREATE USER audited_user IDENTIFIED WITH sha256_password BY '***'
SETTINGS log_queries = 1;

-- 审计查询：谁在何时执行了什么
SELECT
    user, query_kind, query, query_start_time,
    query_duration_ms, read_rows, exception_code
FROM system.query_log
WHERE query_start_time >= now() - INTERVAL 1 DAY
  AND query NOT ILIKE '%system.query_log%'
ORDER BY query_start_time DESC
LIMIT 50;

-- 会话日志
SELECT user, client_name, client_version, session_start_time
FROM system.session_log
ORDER BY session_start_time DESC LIMIT 20;

-- 只读账号：禁止一切写与 DDL
CREATE USER query_only
IDENTIFIED WITH sha256_password BY '***'
SETTINGS readonly = 1;

<!-- config.xml 中配置 LDAP 服务器 -->
<ldap_servers>
  <ldap1>
    <host>ldap.company.com</host>
    <port>389</port>
    <bind_dn>cn=admin,dc=company,dc=com</bind_dn>
    <verification_cooldown_period>30</verification_cooldown_period>
  </ldap1>
</ldap_servers>

-- 用户绑定 LDAP 认证
CREATE USER analyst IDENTIFIED WITH ldap SERVER 'ldap1';

-- Kerberos 域认证
CREATE USER analyst IDENTIFIED WITH kerberos REALM 'COMPANY.COM';