learnNote

主题

第 15 章 权限、配额与多租户 ​

学习目标：能为公司搭一套「用户 / 角色 / Profile / Quota / 行级策略」五件套的安全体系；懂为什么 ClickHouse 的权限体系既「细到列」又「细到行」；能设计一个 SaaS 多租户隔离方案，用 RLS（行级安全）+ Quota（配额）保证「租户 A 不会拖死租户 B」；记住几个关键的 SQL 防御参数（readonly / max_memory_usage / max_execution_time / max_rows_to_read），不让一个手抖的实习生把集群跑挂。

0. 开篇：「OLAP 集群被一条 SQL 跑死」的真实事故 ​

某次故障复盘：
  实习生在 BI 平台写了一句：
    SELECT * FROM events_huge ORDER BY ts DESC;     -- 没 LIMIT
  结果：
    ① 单查询占用 200 GB 内存 → CK 节点 OOM 重启
    ② 重启期间 5 分钟无服务，报警群炸了 30+ 条
    ③ 业务看板全部白屏

  事后总结：
    ✗ 实习生没有 LIMIT 习惯
    ✗ 这个用户没限定 max_memory_usage
    ✗ 这个用户没有 readonly 不能 SELECT * 是 readonly 也防不住
    ✗ 没设 max_rows_to_read，3 亿行的扫描没人挡
  → 三个月后再来一遍，差点又出事

ClickHouse 是个「给力但不会自我保护」的引擎。不像 PG 默认 work_mem=4MB 这种保守内存配置，CK 默认很多参数都开得很大（max_memory_usage = 10GB），是为了性能而非安全。

所以这一章的核心目标是：把 CK 的安全栅栏立起来。

📌 首次术语解释 · RBAC：Role-Based Access Control（基于角色的访问控制）。把权限挂到「角色」上、把角色挂到「用户」上，避免一个个用户单独配权限。CK 自 19 版本起就有完整的 SQL 驱动 RBAC。

1. ClickHouse 的安全五件套总图 ​

┌─────────────────────────────────────────────────────────────────────┐
│                        访问 / 安全控制全栈                              │
├─────────────────────────────────────────────────────────────────────┤
│                                                                      │
│   ① User （用户）   "我是谁"                                            │
│       ↓                                                              │
│   ② Role （角色）   "我能做什么"  ← 权限的载体                           │
│       ↓                                                              │
│   ③ Profile（配置）  "我能用多少资源"  ← max_memory_usage 等参数        │
│       ↓                                                              │
│   ④ Quota（配额）    "我能在 X 时间窗口用多少次"  ← QPS / 行数 / 错误数   │
│       ↓                                                              │
│   ⑤ Row Policy（行级策略） "我能看哪些行"  ← tenant_id = currentUser() │
│                                                                      │
└─────────────────────────────────────────────────────────────────────┘

每一层管一件事，可以叠加使用。下面逐个拆开。

2. 用户体系：XML 配置 vs SQL 驱动 ​

2.1 两种用户管理方式 ​

CK 的用户有两种来源，可以共存：

方式怎么写何时改优势
XML 配置驱动（老）users.xml 文件改完要 SYSTEM RELOAD CONFIG简单、无依赖
SQL 驱动（新，推荐）CREATE USER ... SQL即时生效支持 RBAC、可被 ON CLUSTER 分发

📌 何时各用哪个：

  • 集群 / 多租户 / 频繁增删用户 → 必选 SQL 驱动
  • 单机 / 只有 default 用户 / Docker 镜像启动 → XML 也行

2.2 XML 用户写法（users.xml） ​

xml
<users>
    <default>
        <password></password>                             <!-- 空表示无密码（不要！） -->
        <profile>default</profile>
        <quota>default</quota>
        <networks>
            <ip>::/0</ip>                                 <!-- 0.0.0.0/0 等价 -->
        </networks>
    </default>

    <readonly_user>
        <password_sha256_hex>e3b0c44...</password_sha256_hex>   <!-- SHA-256 hash -->
        <profile>readonly</profile>                       <!-- 挂上 readonly profile -->
        <quota>analytic_quota</quota>
        <networks>
            <ip>10.0.0.0/8</ip>
        </networks>
        <access_management>0</access_management>          <!-- 不允许它管别人的权限 -->
    </readonly_user>
</users>

2.3 SQL 驱动用户（生产推荐） ​

要先在 users.xml 里给 default（或某个管理员）开「access_management」：

xml
<default>
    <access_management>1</access_management>
    <named_collection_control>1</named_collection_control>
    <show_named_collections>1</show_named_collections>
</default>

然后用 SQL 管理：

sql
-- 创建用户
CREATE USER alice IDENTIFIED WITH sha256_password BY 'P@ssw0rd_2026'
    HOST IP '10.0.0.0/8'                            -- 限制网段
    DEFAULT ROLE analytics_reader;                  -- 默认角色

-- 改密码
ALTER USER alice IDENTIFIED WITH sha256_password BY 'NewP@ss';

-- 删用户
DROP USER alice;

-- 查看
SELECT name, host_ip, default_roles_list FROM system.users;

支持的认证方式：

方式安全度用法
IDENTIFIED WITH no_password❌ 危险测试
IDENTIFIED WITH plaintext_password BY '...'⚠ 不推荐文件里看得见
IDENTIFIED WITH sha256_password BY '...'✓ 推荐默认
IDENTIFIED WITH double_sha1_password BY '...'✓MySQL 兼容
IDENTIFIED WITH ldap✓✓LDAP / AD
IDENTIFIED WITH kerberos✓✓企业 Kerberos
IDENTIFIED WITH ssl_certificate CN '...'✓✓mTLS

3. 角色（Role）与 GRANT / REVOKE ​

3.1 一句话 ​

角色 = 一组权限的命名集合。把权限挂角色，把角色挂用户。

权限                           角色                       用户
SELECT ON learn_ck.events  ─┐
SELECT ON learn_ck.users   ─┤  →  analytics_reader  →   alice
                            │                            bob
SHOW DICTIONARIES          ─┘                            charlie

INSERT ON staging.*        ─┐
ALTER ON staging.*         ─┤  →  data_engineer     →   eve
TRUNCATE ON staging.*      ─┘

3.2 创建角色 + 授权 ​

sql
-- 1. 建角色
CREATE ROLE analytics_reader;
CREATE ROLE data_engineer;
CREATE ROLE saas_tenant;        -- 多租户用，下文 RLS 会用到

-- 2. 给角色授权
GRANT SELECT ON learn_ck.* TO analytics_reader;
GRANT SHOW DICTIONARIES TO analytics_reader;

GRANT INSERT, ALTER, OPTIMIZE, TRUNCATE ON staging.* TO data_engineer;
GRANT CREATE TABLE, DROP TABLE ON staging.* TO data_engineer;

-- 3. 把角色挂到用户
GRANT analytics_reader TO alice;
GRANT data_engineer TO eve;

-- 4. 设置默认角色（用户登录时自动激活）
SET DEFAULT ROLE analytics_reader TO alice;

-- 5. 用户登录后切换角色（如果有多个角色）
SET ROLE data_engineer;

3.3 GRANT 的粒度：库 / 表 / 列 ​

CK 的 GRANT 粒度非常细：

sql
-- 库级
GRANT SELECT ON learn_ck.* TO alice;

-- 表级
GRANT SELECT, INSERT ON learn_ck.events TO alice;

-- 列级！⭐
GRANT SELECT(user_id, event_type) ON learn_ck.events TO alice;
-- alice 可以查 user_id、event_type，但 properties / device_id 等敏感列查不了

-- 函数级
GRANT EXPLAIN ON learn_ck.* TO alice;
GRANT SYSTEM RELOAD DICTIONARY ON learn_ck.* TO alice;

-- 系统操作
GRANT SYSTEM RESTART REPLICA TO admin;

3.4 权限层次结构 ​

CK 的权限呈树状结构，ALL 包含所有，SELECT 包含 SELECT(col)：

ALL
├── SELECT
│   ├── SELECT(col1, col2, ...)            -- 列级
│   └── SHOW [TABLES / COLUMNS / DICTS]
├── INSERT
├── ALTER
│   ├── ALTER UPDATE / DELETE
│   ├── ALTER ADD/DROP/MODIFY COLUMN
│   ├── ALTER ATTACH/DETACH PARTITION
│   └── ...
├── CREATE [TABLE / DATABASE / VIEW / DICT]
├── DROP   [TABLE / DATABASE / VIEW / DICT]
├── TRUNCATE
├── OPTIMIZE
├── KILL QUERY
├── SYSTEM
│   ├── SYSTEM RELOAD CONFIG
│   ├── SYSTEM SHUTDOWN
│   └── ...
├── DICT (CREATE/DROP/RELOAD/USE)
└── ACCESS MANAGEMENT
    ├── CREATE/ALTER/DROP USER
    ├── CREATE/ALTER/DROP ROLE
    ├── GRANT/REVOKE
    └── ...

3.5 REVOKE 撤销 ​

sql
REVOKE SELECT ON learn_ck.events FROM alice;
REVOKE analytics_reader FROM alice;

4. Profile：参数集合，挂到用户上 ​

4.1 一句话 ​

Profile = 一组运行时设置（settings）的命名集合。挂到用户后，该用户的每条 SQL 默认带这些参数。

4.2 关键参数全景图 ​

类别参数作用
只读保护readonly0=可写 / 1=只读 / 2=只读+可改 settings
DDL 控制allow_ddl是否能 CREATE / DROP / ALTER
内存控制max_memory_usage单查询最大内存（默认 10G）
max_memory_usage_for_user单用户全部并发查询合计内存
max_memory_usage_for_all_queries整个 server 所有查询合计内存
时间控制max_execution_time单查询最长执行秒数
扫描控制max_rows_to_read单查询最大扫描行数
max_bytes_to_read单查询最大扫描字节数
结果控制max_result_rows返回结果最大行数（防 SELECT * 拉爆网络）
max_result_bytes返回结果最大字节
并发控制max_concurrent_queries_for_user单用户并发查询数
max_concurrent_queries_for_all_users全局并发查询数
JOIN 控制max_bytes_in_joinJOIN 右表最大字节（防内存爆）
join_algorithmhash / partial_merge / grace_hash / direct
GROUP BY 控制max_bytes_before_external_group_by超过这个值用磁盘 spill
max_rows_in_setIN 子句最大基数

4.3 创建 Profile ​

sql
CREATE SETTINGS PROFILE 'analytics_safe' SETTINGS
    readonly = 1,
    max_memory_usage = 5000000000,             -- 5 GB
    max_execution_time = 30,                   -- 30 秒
    max_rows_to_read = 100000000,              -- 1 亿行
    max_result_rows = 100000,                  -- 结果最多 10 万行
    max_concurrent_queries_for_user = 5,
    max_bytes_in_join = 1000000000,            -- JOIN 右表 1 GB 上限
    join_algorithm = 'grace_hash';             -- 大 JOIN 用磁盘 spill

-- 挂到角色（推荐）
ALTER ROLE analytics_reader SETTINGS PROFILE 'analytics_safe';

-- 也可以直接挂到用户
ALTER USER alice SETTINGS PROFILE 'analytics_safe';

4.4 Profile 嵌套（继承） ​

Profile 可以基于其他 Profile，避免重复：

sql
-- 基础 profile
CREATE SETTINGS PROFILE base SETTINGS
    max_memory_usage = 5000000000,
    max_execution_time = 30;

-- 在基础上加只读
CREATE SETTINGS PROFILE readonly_safe SETTINGS
    PROFILE base,
    readonly = 1;

-- 给运维更宽松（更多内存 + 可写）
CREATE SETTINGS PROFILE ops SETTINGS
    PROFILE base,
    max_memory_usage = 20000000000,
    readonly = 0,
    allow_ddl = 1;

4.5 readonly 三个取值 ​

值含义
0可读可写（默认）
1只读 + 不能改 settings
2只读 + 但允许改 settings（让用户能 SET max_threads = 4 之类）

5. Quota：时间窗口配额 ​

5.1 一句话 ​

Quota = 在某个时间窗口（小时 / 天 / 月）内限制用户的查询次数 / 行数 / 错误数 / 执行时间总和。Profile 限制「单个查询」，Quota 限制「单位时间总量」。

5.2 Quota 的 5 个限制维度 ​

维度含义
queries该窗口内允许的查询次数
query_selects仅 SELECT 次数
query_inserts仅 INSERT 次数
errors允许的失败查询次数
result_rows累计返回行数
read_rows累计扫描行数
execution_time累计执行时间（秒）

5.3 创建 Quota ​

sql
-- 给租户：每小时 1000 次查询、累计扫描 1 亿行、累计执行 1800 秒
CREATE QUOTA tenant_quota
KEYED BY user_name             -- 每个用户独立计数（不共用）
FOR INTERVAL 1 HOUR
    MAX queries = 1000,
        result_rows = 10000000,
        read_rows = 100000000,
        execution_time = 1800,
        errors = 100
TO saas_tenant;                -- 挂给某个角色

-- 也可以多个时间窗口（小时 + 天 + 月）
CREATE QUOTA mixed_quota
KEYED BY user_name
FOR INTERVAL 1 MINUTE   MAX queries = 60
FOR INTERVAL 1 HOUR     MAX queries = 1000, read_rows = 100000000
FOR INTERVAL 1 DAY      MAX queries = 10000, read_rows = 1000000000
TO analytics_reader;

5.4 Quota 触发后会怎样？ ​

当用户在窗口内突破限制：

  Code: 201. DB::Exception: Quota for user `alice` for 1 hour has been exceeded:
            queries = 1001/1000.

  → 查询直接被拒绝
  → 等到时间窗口 reset 后才能继续查

5.5 看用量 ​

sql
SELECT
    quota_name,
    quota_key,
    interval_start, interval_end,
    queries,                     -- 已用次数 / 上限
    query_selects, query_inserts,
    errors,
    read_rows, result_rows,
    execution_time
FROM system.quotas_usage
WHERE quota_name = 'tenant_quota'
ORDER BY interval_start DESC
LIMIT 10
FORMAT Vertical;

6. 行级权限（Row Policy）：RLS ​

6.1 一句话 ​

Row Policy = 给某个表加一个 WHERE 表达式，每个用户查这张表时自动叠加这个过滤条件。用来做 SaaS 多租户的「我只能看到我自己租户的数据」。

6.2 一个标准 SaaS 多租户场景 ​

需求：所有租户的事件都存在 events 表里，加个 tenant_id 列；每个租户用自己的 CK 用户登录后，只能看到自己租户的行。

sql
-- 1. 表里加 tenant_id 列
CREATE TABLE learn_ck.events_multitenancy
(
    event_time DateTime,
    tenant_id  String,                 -- ⭐ 租户标识
    user_id    UInt64,
    event_type LowCardinality(String),
    properties String
)
ENGINE = MergeTree
PARTITION BY (tenant_id, toYYYYMM(event_time))
ORDER BY (tenant_id, event_time, user_id);

-- 2. 给每个租户建用户（用户名 = tenant_id 是个好惯例）
CREATE USER tenant_acme    IDENTIFIED WITH sha256_password BY 'pwd_acme';
CREATE USER tenant_globex  IDENTIFIED WITH sha256_password BY 'pwd_globex';

-- 3. 给 events 表加行级策略：每个用户自动过滤 tenant_id = currentUser()
CREATE ROW POLICY tenant_isolation
    ON learn_ck.events_multitenancy
    FOR SELECT
    USING tenant_id = substring(currentUser(), length('tenant_') + 1)
    TO ALL;

-- 4. 给租户用户授权 SELECT
GRANT SELECT ON learn_ck.events_multitenancy TO tenant_acme;
GRANT SELECT ON learn_ck.events_multitenancy TO tenant_globex;

-- 5. 验证：用 tenant_acme 登录后查表，只能看到 tenant_id='acme' 的行
-- clickhouse-client -u tenant_acme --password pwd_acme \
--     --query "SELECT count() FROM learn_ck.events_multitenancy"

6.3 RLS 的几种 USING 写法 ​

sql
-- 1) 用当前用户名做隔离（最常见）
USING tenant_id = currentUser()

-- 2) 用 IP 段
USING country = (SELECT country FROM ip_to_country WHERE ip = currentClientIP())

-- 3) 用一个映射字典（用户 → 多个 tenant）
USING tenant_id IN (
    dictGetMultiple('user_tenants', 'tenant_id', currentUser())
)

-- 4) 时间窗口隔离（只能看最近 30 天的数据）
USING event_time >= now() - INTERVAL 30 DAY

6.4 RLS 的注意事项 ​

  1. 同一表多个 RLS 是 OR 关系：写多条策略，谁都满足就放行（要 AND 的话写在一个 USING 里）。
  2. 不影响其他用户：TO tenant_acme 只对该用户生效，admin 看到的还是全表。
  3. 不影响 INSERT：默认 USING 只过滤 SELECT。要限制 INSERT 用 WITH CHECK 子句（CK 24+）。
  4. 性能不会爆炸：USING 表达式会自动 push down 到 WHERE，能用上索引。
sql
-- 看现有的策略
SELECT name, database, table, select_filter, apply_to_all
FROM system.row_policies;

7. 真实案例：多租户 SaaS 的安全栈 ​

需求：

  • 3 个租户：acme / globex / initech
  • 每租户 ≤ 5 GB 内存 / SQL，≤ 30 秒 / SQL
  • 每租户每小时最多 1000 次查询
  • 每租户只看到自己 tenant_id 的行

完整建模：

sql
-- ===== 1. Profile：资源上限 =====
CREATE SETTINGS PROFILE saas_safe SETTINGS
    readonly = 1,
    max_memory_usage = 5000000000,
    max_execution_time = 30,
    max_rows_to_read = 100000000,
    max_result_rows = 100000,
    max_concurrent_queries_for_user = 5,
    join_algorithm = 'grace_hash';

-- ===== 2. Role：权限载体 =====
CREATE ROLE saas_tenant;
GRANT SELECT ON learn_ck.events_multitenancy TO saas_tenant;
GRANT SHOW DICTIONARIES TO saas_tenant;
ALTER ROLE saas_tenant SETTINGS PROFILE 'saas_safe';

-- ===== 3. Quota：时间配额 =====
CREATE QUOTA saas_quota
KEYED BY user_name
FOR INTERVAL 1 HOUR
    MAX queries = 1000,
        read_rows = 100000000,
        execution_time = 1800
TO saas_tenant;

-- ===== 4. Row Policy：行级隔离 =====
CREATE ROW POLICY saas_tenant_iso
    ON learn_ck.events_multitenancy
    FOR SELECT
    USING tenant_id = substring(currentUser(), length('tenant_') + 1)
    TO saas_tenant;

-- ===== 5. Users：每个租户一个用户 =====
CREATE USER tenant_acme    IDENTIFIED WITH sha256_password BY 'pwd_acme'
    DEFAULT ROLE saas_tenant;
CREATE USER tenant_globex  IDENTIFIED WITH sha256_password BY 'pwd_globex'
    DEFAULT ROLE saas_tenant;
CREATE USER tenant_initech IDENTIFIED WITH sha256_password BY 'pwd_initech'
    DEFAULT ROLE saas_tenant;

-- ===== 6. 验证矩阵 =====
-- ① 租户 acme 不能看到 globex 的数据 → RLS
-- ② 租户 acme 不能写表 → readonly = 1
-- ③ 租户 acme 不能加表 / 删表 → 没 GRANT CREATE / DROP
-- ④ 租户 acme 一条 SQL 用不了 6 GB → max_memory_usage = 5 GB
-- ⑤ 租户 acme 一小时跑不过 1000 次 → Quota
-- ⑥ admin / default 不受任何影响 → Quota / Profile / RLS 都只 TO saas_tenant

8. 关键安全相关参数清单 ​

参数默认推荐生产值说明
readonly0租户 1，运维 0查询权限开关
allow_ddl1租户 0是否允许 DDL
max_memory_usage10 GB租户 5 GB 起单查询内存上限
max_memory_usage_for_user0（无限）租户 8 GB单用户全部并发合计
max_execution_time0（无限）30 ~ 300单查询最长秒数
max_rows_to_read01 亿单查询最大扫描行数
max_bytes_to_read010 GB单查询最大扫描字节数
max_result_rows010 万防 SELECT * 拉爆
max_concurrent_queries_for_user05~10单用户并发
max_concurrent_queries_for_all_users100按 CPU 核数全局并发
max_bytes_in_join01 GBJOIN 右表上限
max_bytes_before_external_group_by02 GB超过则磁盘 spill

9. 📌 与 MySQL / PostgreSQL 权限模型对比 ​

维度ClickHouseMySQLPostgreSQL
粒度库 / 表 / 列库 / 表 / 列（5.7+）库 / schema / 表 / 列
角色✅ CREATE ROLE✅ 8.0+✅ 完整
行级安全✅ ROW POLICY❌ 不原生（要用 view 模拟）✅ ROW LEVEL SECURITY
配额（Quota）✅ 时间窗口配额（独有）⚠ 仅 max_user_connections / max_queries_per_hour⚠ 没有原生
Profile / 参数集✅ Settings Profile⚠ 通过 set_role 间接⚠ 通过 ALTER USER SET
认证方式sha256 / LDAP / Kerberos / mTLSmysql_native / sha256 / LDAPscram-sha-256 / GSSAPI / cert
DCL SQLGRANT / REVOKE / CREATE USER同同

亮点：

  • CK 的 Quota 是独门绝技：MySQL/PG 都没有「按时间窗口限制查询数 + 扫描行数 + 执行时间」的原生能力。
  • CK 的 Profile 也很特别：把运行时参数和用户绑定，PG 要 ALTER USER SET search_path TO ... 一个一个加。

10. 本章小结 ​

┌─────────────────────────────────────────────────────────────────┐
│                       本章核心要点                                │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│  ① 五件套 / 五层栅栏：                                            │
│     User → Role → Profile → Quota → Row Policy                  │
│                                                                  │
│  ② User                                                           │
│     • XML 用户：简单但分发难                                       │
│     • SQL 驱动用户（生产推荐）：CREATE USER ... IDENTIFIED WITH  │
│     • 推荐认证：sha256_password / LDAP / mTLS                     │
│                                                                  │
│  ③ Role + GRANT                                                   │
│     • 库 / 表 / 列三级粒度                                          │
│     • 把权限挂角色，把角色挂用户                                    │
│                                                                  │
│  ④ Profile                                                        │
│     • 限单 SQL 资源（内存、时间、行数）                             │
│     • 关键参数：readonly / max_memory_usage /                     │
│       max_execution_time / max_rows_to_read                     │
│                                                                  │
│  ⑤ Quota                                                          │
│     • 限时间窗口总量（CK 独门绝技）                                 │
│     • 5 个维度：queries / errors / read_rows / result_rows /     │
│       execution_time                                            │
│                                                                  │
│  ⑥ Row Policy                                                     │
│     • SaaS 多租户隔离                                              │
│     • USING tenant_id = currentUser()                            │
│                                                                  │
│  ⑦ 防御性默认                                                       │
│     • 任何业务用户 readonly=1                                       │
│     • 给所有租户挂 max_memory_usage / max_execution_time          │
│     • 给所有租户挂小时级 Quota                                      │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘

11. 面试高频题 ​

Q1：ClickHouse 的权限模型有几层？分别管什么？ ​

考察点：是否真的搞清了 CK 的安全栈各层职责。

标准答案：

  1. User（用户）：身份。CREATE USER alice IDENTIFIED WITH sha256_password BY '...' HOST IP '...'。
  2. Role（角色）：权限载体。CREATE ROLE，把多个 GRANT 集中起来，再挂给用户。
  3. GRANT / REVOKE：实际授权。粒度可以到库 / 表 / 列（GRANT SELECT(col1, col2) ON db.t）。
  4. Settings Profile：运行时参数集合（max_memory_usage、max_execution_time、readonly 等），可挂在用户或角色上，限制单查询资源。
  5. Quota：按时间窗口（小时 / 天 / 月）限制总用量（查询次数 / 扫描行数 / 错误次数 / 累计执行时间）。
  6. Row Policy：行级安全。给表挂 USING <expr> 的过滤表达式，每个用户查表自动叠加 → SaaS 多租户隔离。

加分项：

  • 能说清「Profile 限单 SQL，Quota 限时间窗口总量」这个本质区别。
  • 能讲 access_management 这个开关（让某用户具备「能管别人权限」的能力）。

易错点：

  • 别把 Profile 和 Quota 混在一起说 —— 它们是不同维度的限制。

Q2：CK 怎么做 SaaS 多租户隔离？ ​

考察点：实战能力 + 是否懂 RLS。

标准答案：

  1. 数据层：所有租户的数据放一张表，加 tenant_id 列；建表时把 tenant_id 放到 PARTITION BY 或 ORDER BY 的最前面，让按租户查询能命中分区裁剪 + 主键过滤。
  2. 用户层：每个租户对应一个 CK 用户（用户名常见命名是 tenant_<id>），便于 RLS 按 currentUser() 推断租户。
  3. 权限层：所有租户共用一个角色 saas_tenant，给角色 GRANT SELECT 而不允许 INSERT/DDL。
  4. 行级隔离：CREATE ROW POLICY tenant_iso ON db.events FOR SELECT USING tenant_id = substring(currentUser(), ...) TO saas_tenant。
  5. 资源隔离：saas_safe Profile 限制单 SQL 内存 / 执行时间；saas_quota 按小时限制查询次数和扫描行数。
  6. 网络层：每个租户用户限定来源 IP，避免凭证泄露后被横扫。

加分项：

  • 能提到「用户名 → tenant_id 映射用 Dictionary」（更灵活），能多对多。
  • 能提到 OPTIMIZE TABLE ... FINAL 在多租户下要分租户做，避免锁全表。
  • 能提到「按 tenant 分分区时数量不能太多」（CK 不喜欢上万分区）。

易错点：

  • 别只用 RLS 而不限制资源 —— 一个手抖租户也能拖死集群。

Q3：Profile 和 Quota 有什么区别？ ​

考察点：能否区分「单条 SQL」和「时间窗口」两种限制。

标准答案：

维度ProfileQuota
限制对象单个 SQL用户在一段时间内累计
典型参数max_memory_usage / max_execution_time / readonlyqueries / read_rows / errors
触发后果单 SQL 报错或被 kill配额耗尽，后续查询全部被拒，等窗口 reset
挂载方式ALTER USER/ROLE SETTINGS PROFILECREATE QUOTA ... TO role
作用域每条 SQL 独立判定跨 SQL 累计（同一窗口内）

具体例子：

  • 一个 Profile：「单查询不能超过 5 GB 内存、不能超过 30 秒」 → 用 Profile。
  • 一个 Quota：「每小时不能查超过 1000 次 / 扫描 1 亿行」 → 用 Quota。

加分项：

  • 能讲 Quota 的 KEYED BY user_name：多个用户共享配额还是各自独立。
  • 能讲 Profile 嵌套：CREATE PROFILE child SETTINGS PROFILE parent, ...。

易错点：

  • 别说「Profile 也能限制 QPS」 —— 那是 Quota 的活。

Q4：行级安全（RLS）是什么？性能会不会爆？ ​

考察点：理解 RLS 机制 + 对查询性能的认识。

标准答案：

  1. RLS（Row Level Security）：在表上挂一个 USING <bool_expr> 的过滤策略，每个被该策略覆盖的用户在查表时，CK 自动把 <bool_expr> AND 到查询的 WHERE 子句里。
  2. 建法：CREATE ROW POLICY name ON db.t FOR SELECT USING tenant_id = currentUser() TO some_role;
  3. 性能：RLS 表达式会被推到 WHERE，能用上 ORDER BY / PARTITION BY 索引；只要把 tenant_id 放到 ORDER BY 第一位，性能基本不受影响。
  4. 多策略叠加：同一表多个 RLS 是 OR 关系（任一满足都放行），如果要 AND 就写在一个 USING 里。
  5. 不影响 INSERT 默认：旧版 RLS 只过滤 SELECT；CK 24+ 支持 FOR INSERT WITH CHECK。

加分项：

  • 能给 RLS 失效的反面教材：表设计时没把 tenant_id 放 ORDER BY 第一位 → 全表扫之后再过滤，性能崩。
  • 能提到 EXPLAIN SYNTAX SELECT ... 能看出 CK 把 RLS 表达式合并到了 WHERE 里。

易错点：

  • 别说「RLS 影响 admin 也看不到全表」 —— 没挂 TO 的用户不受影响。

Q5：怎么防止一条糟糕 SQL 把 ClickHouse 整挂？ ​

考察点：生产经验，最实战的一题。

标准答案（5 道防线）：

  1. max_memory_usage：单查询内存上限。生产建议 5~10 GB，超过即报错（不会拖垮整个机器）。
  2. max_execution_time：单查询执行时间上限。建议 30~300 秒，超时自动 kill。
  3. max_rows_to_read / max_bytes_to_read：单查询最大扫描量。挡掉无 LIMIT、无 WHERE 的全表扫。
  4. max_result_rows：返回结果行数上限。挡掉 SELECT * 把几亿行拉到客户端。
  5. max_concurrent_queries_for_user：单用户并发查询数。挡掉「重试风暴」（一个用户疯狂重试）。
  6. readonly = 1：所有业务用户必备。挡掉手抖 INSERT / DROP TABLE。
  7. Quota（小时级）：挡掉「持续高频查询」型攻击。
  8. SYSTEM KILL QUERY：万一有漏网之鱼，能手动 kill。

加分项：

  • 能提到 KILL MUTATION 与 KILL QUERY 的差别（Mutation 是异步重写，要单独 kill）。
  • 能提到 system.query_log 与 system.processes 是排查的两张表。
  • 能讲「测试环境用同样的 Profile」，避免线上才发现限制不够。

易错点：

  • 别只说一两个参数 —— 防御要多层。
  • 别说 max_memory_usage 默认就够 —— 默认 10 GB 在小机器上能直接 OOM。

🎬 可视化演示 ​

演示加载缓慢或样式异常？点此在新标签页打开 ↗

💻 示例代码 ​

python
"""
第 15 章 · 权限、配额与多租户 · 实操 Demo
========================================================
本脚本演示：
  1) 用 default 用户准备：库 / 表 / Profile / Role / Quota / Row Policy / Users
  2) 用 admin 视角看到全表
  3) 用 tenant_acme 身份连接，验证「行级 + 列级」隔离
  4) 用 tenant_globex 身份连接，验证看到的是另一份数据
  5) 用 tenant_acme 跑批量查询，触发 Quota，看到拒绝错误
  6) 清理（可选）

运行：
    pip install clickhouse-connect
    python auth_play.py

环境变量（可选）：
    CK_HOST   默认 127.0.0.1
    CK_PORT   默认 8123
    CK_USER   默认 default
    CK_PASS   默认 ""
"""

import os
import sys
import time
from contextlib import contextmanager

try:
    import clickhouse_connect
    from clickhouse_connect.driver.exceptions import DatabaseError, OperationalError
except ImportError:
    sys.exit("[ERROR] 需要先安装 clickhouse-connect：pip install clickhouse-connect")


CK_HOST = os.getenv("CK_HOST", "127.0.0.1")
CK_PORT = int(os.getenv("CK_PORT", "8123"))
ADMIN_USER = os.getenv("CK_USER", "default")
ADMIN_PASS = os.getenv("CK_PASS", "")

DB = "learn_ck"

# 演示用户与口令（与 init.sql 保持一致）
USERS = {
    "alice":         "alice_pass_2026",
    "bob":           "bob_pass_2026",
    "tenant_acme":   "acme_pass_2026",
    "tenant_globex": "globex_pass_2026",
}


# ---------- 工具 ----------
def banner(title: str, ch: str = "─"):
    print()
    print(ch * 78)
    print(f"  {title}")
    print(ch * 78)


def connect(user: str, password: str = "", database: str | None = DB):
    return clickhouse_connect.get_client(
        host=CK_HOST,
        port=CK_PORT,
        username=user,
        password=password,
        database=database,
        connect_timeout=5,
        send_receive_timeout=10,
    )


@contextmanager
def safe_session(user: str, password: str = "", database: str | None = DB):
    client = None
    try:
        client = connect(user, password, database)
        yield client
    finally:
        if client is not None:
            client.close()


def run_quietly(client, sql: str):
    """执行单条 SQL，吃掉 IF EXISTS 之类的小报错"""
    try:
        client.command(sql)
    except (DatabaseError, OperationalError) as e:
        print(f"  [warn] {sql[:60]}... -> {type(e).__name__}: {e}")


# ---------- Step 1: 准备元数据 ----------
def setup_admin():
    banner("Step 1: 用 admin 准备库 / 表 / 角色 / 用户 / 行策略 / 配额")
    with safe_session(ADMIN_USER, ADMIN_PASS, database=None) as ck:
        ck.command(f"CREATE DATABASE IF NOT EXISTS {DB}")

        ck.command(f"DROP TABLE IF EXISTS {DB}.events")
        ck.command(f"""
            CREATE TABLE {DB}.events
            (
                event_time  DateTime,
                tenant_id   LowCardinality(String),
                user_id     UInt64,
                event_type  LowCardinality(String),
                properties  String,
                amount      Decimal(12, 2) DEFAULT 0
            )
            ENGINE = MergeTree
            ORDER BY (tenant_id, event_time, user_id)
        """)
        ck.insert(
            f"{DB}.events",
            data=[
                ("2026-04-17 09:00:00", "acme",    1001, "view",     '{"src":"app"}',       0),
                ("2026-04-17 09:01:00", "acme",    1002, "click",    '{"id":"b01"}',        0),
                ("2026-04-17 09:02:00", "globex",  2001, "view",     '{"src":"web"}',       0),
                ("2026-04-17 09:03:00", "acme",    1001, "add_cart", '{"sku":"A001"}',      0),
                ("2026-04-17 09:04:00", "globex",  2002, "pay",      '{"order":"ord_001"}', 99.00),
                ("2026-04-17 09:05:00", "initech", 3001, "view",     '{"src":"app"}',       0),
                ("2026-04-17 09:06:00", "acme",    1003, "view",     '{"src":"app"}',       0),
                ("2026-04-17 09:07:00", "globex",  2001, "click",    '{"id":"b02"}',        0),
                ("2026-04-17 09:08:00", "initech", 3002, "pay",      '{"order":"ord_002"}', 150.00),
            ],
            column_names=[
                "event_time", "tenant_id", "user_id",
                "event_type", "properties", "amount",
            ],
        )

        # Profile
        for sql in [
            "DROP SETTINGS PROFILE IF EXISTS saas_safe",
            "DROP SETTINGS PROFILE IF EXISTS dev_full",
        ]:
            run_quietly(ck, sql)

        ck.command("""
            CREATE SETTINGS PROFILE saas_safe SETTINGS
                readonly = 1,
                max_memory_usage = 5000000000,
                max_execution_time = 30,
                max_rows_to_read = 100000000,
                max_threads = 4,
                max_concurrent_queries_for_user = 5
        """)
        ck.command("""
            CREATE SETTINGS PROFILE dev_full SETTINGS
                readonly = 0,
                allow_ddl = 1,
                max_memory_usage = 20000000000,
                max_execution_time = 600
        """)

        # Roles
        for sql in [
            "DROP ROLE IF EXISTS analytics_reader",
            "DROP ROLE IF EXISTS data_engineer",
            "DROP ROLE IF EXISTS saas_tenant",
        ]:
            run_quietly(ck, sql)

        ck.command("CREATE ROLE analytics_reader")
        ck.command("CREATE ROLE data_engineer")
        ck.command("CREATE ROLE saas_tenant")

        ck.command(f"GRANT SELECT ON {DB}.* TO analytics_reader")
        ck.command(f"GRANT SHOW TABLES, SHOW DICTIONARIES, SHOW COLUMNS ON {DB}.* TO analytics_reader")

        ck.command(f"""
            GRANT SELECT, INSERT, ALTER UPDATE, ALTER DELETE,
                  OPTIMIZE, TRUNCATE,
                  CREATE TABLE, DROP TABLE, ALTER TABLE
            ON {DB}.* TO data_engineer
        """)

        # 列级 GRANT：故意不给 properties，等价于「列级隐藏」
        ck.command(f"""
            GRANT SELECT(event_time, tenant_id, user_id, event_type, amount)
                ON {DB}.events TO saas_tenant
        """)

        ck.command("ALTER ROLE saas_tenant SETTINGS PROFILE 'saas_safe'")

        # Row Policy
        run_quietly(ck, f"DROP ROW POLICY IF EXISTS tenant_iso ON {DB}.events")
        run_quietly(ck, f"DROP ROW POLICY IF EXISTS admin_all ON {DB}.events")

        ck.command(f"""
            CREATE ROW POLICY tenant_iso
                ON {DB}.events
                FOR SELECT
                USING tenant_id = substring(currentUser(), length('tenant_') + 1)
                TO saas_tenant
        """)
        ck.command(f"""
            CREATE ROW POLICY admin_all
                ON {DB}.events
                FOR SELECT
                USING 1
                TO data_engineer, analytics_reader
        """)

        # Quota：故意把 acme 的小时上限调小，方便后面触发
        run_quietly(ck, "DROP QUOTA IF EXISTS saas_quota")
        ck.command("""
            CREATE QUOTA saas_quota
                KEYED BY user_name
                FOR INTERVAL 1 HOUR
                    MAX queries = 20,
                        errors = 50,
                        read_rows = 100000,
                        execution_time = 60
                TO saas_tenant
        """)

        # Users
        for u in USERS:
            run_quietly(ck, f"DROP USER IF EXISTS {u}")

        ck.command(f"""
            CREATE USER alice
                IDENTIFIED WITH sha256_password BY '{USERS["alice"]}'
                DEFAULT ROLE analytics_reader
        """)
        ck.command(f"""
            CREATE USER bob
                IDENTIFIED WITH sha256_password BY '{USERS["bob"]}'
                DEFAULT ROLE data_engineer
                SETTINGS PROFILE 'dev_full'
        """)
        ck.command(f"""
            CREATE USER tenant_acme
                IDENTIFIED WITH sha256_password BY '{USERS["tenant_acme"]}'
                DEFAULT ROLE saas_tenant
        """)
        ck.command(f"""
            CREATE USER tenant_globex
                IDENTIFIED WITH sha256_password BY '{USERS["tenant_globex"]}'
                DEFAULT ROLE saas_tenant
        """)

        ck.command("GRANT analytics_reader TO alice")
        ck.command("GRANT data_engineer    TO bob")
        ck.command("GRANT saas_tenant      TO tenant_acme, tenant_globex")

        print("  ✓ 元数据准备完成")
        print(f"  ✓ 表 {DB}.events 共 9 行（acme 4, globex 3, initech 2）")


# ---------- Step 2: admin 视角 ----------
def view_as_admin():
    banner("Step 2: admin 视角 — 应该看到全部 9 行")
    with safe_session(ADMIN_USER, ADMIN_PASS) as ck:
        rows = ck.query(
            f"SELECT tenant_id, count() AS cnt FROM {DB}.events GROUP BY tenant_id ORDER BY tenant_id"
        ).result_rows
        for tenant, cnt in rows:
            print(f"  {tenant:<10s} -> {cnt} 行")


# ---------- Step 3: tenant_acme 视角 ----------
def view_as_tenant(user: str, expected_tenant: str):
    banner(f"Step 3/4: 以 {user} 身份连接 — 期望只看到 tenant_id = '{expected_tenant}' 的行")
    try:
        with safe_session(user, USERS[user]) as ck:
            rows = ck.query(
                f"SELECT tenant_id, count() AS cnt FROM {DB}.events GROUP BY tenant_id"
            ).result_rows
            print("  -- 行级隔离效果（GROUP BY tenant_id）：")
            if not rows:
                print("    （没有任何行可见）")
            for tenant, cnt in rows:
                marker = "  ✓" if tenant == expected_tenant else "  ✗ 不应出现！"
                print(f"    {tenant:<10s} -> {cnt} 行 {marker}")

            print("  -- 试访问被列级 GRANT 拦掉的 properties 列：")
            try:
                ck.query(f"SELECT properties FROM {DB}.events LIMIT 1")
                print("    ✗ 居然查到了 — 列级权限失效")
            except DatabaseError as e:
                print(f"    ✓ 被拒绝：{str(e).splitlines()[0]}")

            print("  -- 试写入：")
            try:
                ck.command(
                    f"INSERT INTO {DB}.events VALUES (now(), '{expected_tenant}', 99, 'hack', '', 0)"
                )
                print("    ✗ 居然写成功了 — readonly 失效")
            except DatabaseError as e:
                print(f"    ✓ 被拒绝：{str(e).splitlines()[0]}")
    except OperationalError as e:
        print(f"  [skip] 连接 {user} 失败：{e}")


# ---------- Step 5: 触发 Quota ----------
def trigger_quota(user: str = "tenant_acme"):
    banner(f"Step 5: 用 {user} 连续跑查询，触发 Quota（per-hour queries = 20）")
    with safe_session(user, USERS[user]) as ck:
        ok, denied = 0, 0
        for i in range(35):
            try:
                ck.query(f"SELECT count() FROM {DB}.events")
                ok += 1
            except DatabaseError as e:
                denied += 1
                msg = str(e).splitlines()[0]
                if "Quota" in msg or "quota" in msg or "QUOTA_EXCEEDED" in msg:
                    print(f"  第 {i+1} 次：⛔ {msg}")
                else:
                    print(f"  第 {i+1} 次：[err] {msg}")
                if denied >= 3:
                    break
            time.sleep(0.05)
        print(f"\n  统计：成功 {ok} 次，被拒 {denied} 次")

    banner("配额实时使用情况（system.quotas_usage）")
    with safe_session(ADMIN_USER, ADMIN_PASS) as ck:
        rows = ck.query("""
            SELECT quota_name, quota_key, duration,
                   queries, max_queries,
                   read_rows, max_read_rows
            FROM system.quotas_usage
            WHERE quota_name = 'saas_quota'
            ORDER BY duration
        """).result_rows
        for r in rows:
            print(
                f"  quota={r[0]} key={r[1]} dur={r[2]}s "
                f"queries={r[3]}/{r[4]}  read_rows={r[5]}/{r[6]}"
            )


# ---------- Step 6: 清理 ----------
def cleanup():
    banner("Step 6: 清理（DROP USER / ROLE / QUOTA / POLICY / PROFILE）", "·")
    with safe_session(ADMIN_USER, ADMIN_PASS, database=None) as ck:
        for sql in [
            "DROP USER IF EXISTS alice, bob, tenant_acme, tenant_globex",
            "DROP ROLE IF EXISTS analytics_reader, data_engineer, saas_tenant",
            "DROP QUOTA IF EXISTS saas_quota",
            f"DROP ROW POLICY IF EXISTS tenant_iso ON {DB}.events",
            f"DROP ROW POLICY IF EXISTS admin_all ON {DB}.events",
            "DROP SETTINGS PROFILE IF EXISTS saas_safe",
            "DROP SETTINGS PROFILE IF EXISTS dev_full",
        ]:
            run_quietly(ck, sql)
    print("  ✓ 清理完成")


# ---------- main ----------
def main():
    print(f"==> ClickHouse @ {CK_HOST}:{CK_PORT}, admin = {ADMIN_USER}")

    try:
        setup_admin()
    except (DatabaseError, OperationalError) as e:
        sys.exit(
            f"\n[FATAL] 初始化失败：{e}\n"
            "  请确保 default 用户具备 access_management = 1，或换一个有该权限的账号。"
        )

    view_as_admin()
    view_as_tenant("tenant_acme",   "acme")
    view_as_tenant("tenant_globex", "globex")
    trigger_quota("tenant_acme")

    if os.getenv("CK_KEEP", "0") != "1":
        cleanup()
    else:
        print("\n  注：检测到 CK_KEEP=1，跳过清理，保留对象供继续探索。")


if __name__ == "__main__":
    main()

auth_play.py ↗