波仔
🦏 第16章 安全管理
📄 一句话概括

数据库的安全分三层：谁能连上来（账号、密码、主机名）、 连上来能干什么（库级／表级／列级权限）、数据本身怎么保护（加密、脱敏、审计）。 MySQL 的账号是「用户名 + 主机名」一对： 'zhang'@'localhost' 和 'zhang'@'%' 是两个账号。 本沙箱没有账号体系 （create user／grant／revoke／show／drop user 全报 syntax error）， 权限退化成三样：文件系统权限、只读连接、语句级白名单。

📝 本章和前面几章的关系

前面十五章都在讲「怎么把数据管好」，这一章讲「怎么不让不该动的人动」。 这一章在本沙箱里会有点「空」——因为 sqlite3 压根没有账号这个概念。 但这个「空」本身很有信息量： 它说明「数据库权限」不是数据库的固有属性，是服务端模型带来的。

一、MySQL 的安全模型：三层

第一层是「谁能连上来」：账号、密码、以及「从哪台机器连」。 这一层由 mysql.user 那张表管。 第二层是「连上来能干什么」： 库级、表级、列级、甚至「能不能执行某个存储过程」。 这些权限存在 mysql 库的几张表里： db（库级）、tables_priv（表级）、columns_priv（列级）。 第三层是「数据本身」：加密、脱敏、审计日志。 这一层 MySQL 只提供一部分， 多数要靠应用和外部工具补。 比如「手机号只显示后四位」这件事，数据库管不了，得在查询或渲染时做。

二、账号是「用户名 + 主机名」一对

这是 MySQL 里最容易踩的一个坑： 'zhang'@'localhost' 和 'zhang'@'%' 是两个不同的账号。 建了后者，前者还是不存在。 于是会出现这些怪现象：本地能连、远程连不上（或反过来）、明明给了权限却说没权限、 改了一个账号的密码另一个还能用旧密码进。 十有八九是 host 那一列对不上。 三个常见写法还不完全等价： localhost 走本机 socket、127.0.0.1 走本机 TCP、% 通配任何主机。 所以在 Linux 上 'u'@'localhost' 和 'u'@'127.0.0.1' 是两个账号，这一条坑过很多人。

三、本沙箱没有账号体系

把教材那五条语句逐条试一遍：create user、grant、revoke、show grants、drop user， 五条全是 syntax error，报错都指向第一个动词：USER、GRANT、REVOKE、SHOW、USER。 原因是结构性的： sqlite3 不是一个「服务端」，它是「一个文件格式 + 一个库」。 你的程序直接把库文件打开，中间没有「连上来」这个动作，所以也就没有「谁能连」这个问题。 所以「权限」在这里退化成了三样：文件系统权限（谁能读写那个 .db 文件）、 只读连接（打开时声明「只查不改」）、语句级白名单（set_authorizer 拦语句）。 后两样是 16.2 和 16.3 的内容，都能真跑。

四、一个库文件就是一个数据库

这里没有「实例」、没有「服务」、也没有「连接」—— sqlite3.connect('a.db') 打开的是一个文件。 文件不存在就建一个，存在就直接用。 这件事有两面：好处是部署极简（拷一个文件就是备份，删一个文件就是删库）； 坏处是没有隔离——同一个文件，任何能读到它的程序都能读到全部数据。 MySQL 那种「这个人只能看这张表」的能力，这里没有。 所以用它做多用户系统时，常见做法是「一个用户一个库文件」， 靠「谁能打开哪个文件」来隔离。 也就是说：隔离的边界是文件系统，不是数据库。

五、最小权限：这一条跟操作系统无关

「给多大权限」这个问题，正确答案永远是「刚好够用的那个」： 一个只读报表账号，就给 select，不要图省事给 all。 理由很实在： 程序有 bug 的时候，权限就是最后一道闸门。 一个只有 select 的账号，delete 写错了也删不掉数据； 一个 all 的账号，一个 where 漏写就是全表清空。 本沙箱里这条原则一样适用：只查的模块用只读连接打开库，别图省事全用读写连接。 这一句声明能挡住一整套误写，代价只是一行 URL 参数。

六、动手 Demo：把五条账号语句逐条试一遍

Demo 就一段：一个循环把 create user、grant、revoke、show grants、drop user 送进去， 看哪条能成。 五条全失败—— 这不是「语法写错了」，而是这五个动词在这里不存在。 后面两篇的「替身」都是从这个结论出发的。

import _q46 as Q
c = Q.blank()

# MySQL 的账号与授权语句，逐条试一遍
for s in ("CREATE USER 'zhang'@'%' IDENTIFIED BY 'pwd'",
          "GRANT SELECT ON mydb.* TO 'zhang'@'%'",
          "REVOKE SELECT ON mydb.* FROM 'zhang'@'%'",
          "SHOW GRANTS FOR 'zhang'@'%'",
          "DROP USER 'zhang'@'%'"):
    try:
        c.execute(s)
        print("可以：", s)
    except Exception as e:
        print("不行：", s.split()[0], "->", e)

# 一个连接打开的就是一个文件，没有「连上来」这个动作
print(type(c))

实跑输出：

--------------------------------------------------------------------
第七十五课：安全管理——账号、权限、以及「谁能连上来」
--------------------------------------------------------------------

  MySQL 的安全模型：三层
  **第一层是「谁能连上来」**：**账号、密码、以及「从哪台机器连」**。
  **MySQL 的账号是「用户名 + 主机名」一对**，
  **`'zhang'@'localhost'` 和 `'zhang'@'%'` 是两个不同的账号。**
  **第二层是「连上来能干什么」**：
  **库级权限、表级权限、列级权限、甚至「能不能执行某个存储过程」。**
  **这些权限存在 `mysql` 库里的几张表**：
  **`user`（账号与全局权限）、`db`（库级）、`tables_priv`（表级）、`columns_priv`（列级）。**
  **第三层是「数据本身」**：**加密、脱敏、审计日志。** **这一层 MySQL 只提供一部分**，
  **多数要靠应用和外部工具。**

  本沙箱没有账号体系
  SQL> CREATE USER 'zhang'@'%' IDENTIFIED BY 'pwd'
  报错：near "USER": syntax error
  SQL> GRANT SELECT ON mydb.* TO 'zhang'@'%'
  报错：near "GRANT": syntax error
  SQL> REVOKE SELECT ON mydb.* FROM 'zhang'@'%'
  报错：near "REVOKE": syntax error
  SQL> SHOW GRANTS FOR 'zhang'@'%'
  报错：near "SHOW": syntax error
  SQL> DROP USER 'zhang'@'%'
  报错：near "USER": syntax error
  **五条全是 `syntax error`**，报错都指向第一个动词：`USER`、`GRANT`、`REVOKE`、`SHOW`、`USER`。
  **这五个词这里一个都不认。**
  **原因是结构性的**：**`sqlite3` 不是一个「服务端」，它是一个「文件格式 + 一个库」**。
  **你的程序直接把库文件打开，没有「连上来」这个动作，所以也就没有「谁能连」这个问题。**
  **所以「权限」在这里退化成了三样东西**：**文件系统权限**（谁能读写那个 `.db` 文件）、
  **只读连接**（打开时声明「只查不改」）、**语句级白名单**（`set_authorizer` 拦语句）。
  **这三样下面两篇逐个演示。**

  一个库文件就是一个数据库
  **这里没有「实例」，没有「服务」，也没有「连接」**——
  **`sqlite3.connect('a.db')` 打开的是一个文件。** **文件不存在就建一个，存在就直接用。**
  **这件事有两面**：**好处是部署极简**（拷一个文件就是备份，删一个文件就是删库）；
  **坏处是没有「隔离」**——
  **同一个文件，任何能读到它的程序都能读到全部数据。**
  **MySQL 那种「这个人只能看这张表」的能力，这里没有。**
  **所以用它做多用户系统时，常见的做法是「一个用户一个库文件」**：
  **A 的数据在 `a.db`、B 的数据在 `b.db`**，
  **靠「谁能打开哪个文件」来隔离。** **这也意味着隔离的边界是文件系统，不是数据库。**

  MySQL 那边的账号是怎么存的
  MySQL 原码（本沙箱不执行）：
  | SELECT user, host, authentication_string FROM mysql.user;
  | 
  | -- 8.0 之前密码列叫 password，且是 mysql_native_password 的哈希
  | -- 8.0 默认改成 caching_sha2_password
  **`mysql.user` 这张表就是账号清单**，**`user` + `host` 两列合起来才是「账号」**。
  **所以你 `create user 'zhang'@'%'` 之后，`'zhang'@'localhost'` 还是不存在**——
  **从本机连也连不上。**
  **这个设计经常让人踩坑**：**本地能连、远程连不上**（或者反过来），
  **十有八九是 `host` 那一列对不上。**
  **`%` 是通配「任何主机」，`localhost` 是「本机 socket」，`127.0.0.1` 又是「本机 TCP」**——
  **三个还不完全等价。**

  安全管理最要紧的一条：最小权限
  **「给多大权限」这个问题，正确答案永远是「刚好够用的那个」**：
  **一个只读报表账号，就给 `select`**，**不要图省事给它 `all`。**
  **理由很实在**：
  **程序有 bug 的时候，权限就是最后一道闸门。**
  **一个只有 `select` 的账号，`delete` 写错了也删不掉数据**；
  **一个 `all` 的账号，一个 `where` 漏写就是全表清空。**
  **本沙箱里这条原则一样适用**：**只查的模块用只读连接打开库**，
  **别图省事全用读写连接**——**下一篇会看到，这一句声明能挡住一整套误写。**

五条全报 syntax error，报错分别指向 USER、GRANT、REVOKE、SHOW、USER。 最后一行打印出连接对象的类型： <class 'sqlite3.Connection'>——它就是「打开的一个文件」，不是一个会话。

30 秒实验

  1. 为什么 'u'@'localhost' 和 'u'@'127.0.0.1' 在 Linux 上是两个账号？
  2. 「一个用户一个库文件」这种隔离方式，最大的麻烦在哪？
  3. 如果只读模块用了读写连接打开了库，一个写错的 delete 会怎样？

常见误区

  1. 给应用配 root 账号。 一个 where 漏写就是全表清空，权限是最后一道闸门。
  2. 以为建了 'u'@'%' 本机就能连。 localhost 是另一个账号，三个 host 写法不通用。
  3. 把 .db 文件放在网站根目录。 一行 GET 就把整个库下载走了。
自测三问（点开对答案） 1. MySQL 的安全分哪三层？ —— 谁能连上来（账号、密码、主机名）、连上来能干什么（库级／表级／列级权限）、 数据本身怎么保护（加密、脱敏、审计）。 2. 为什么本沙箱没有账号体系？ —— sqlite3 不是服务端，它是「文件格式 + 一个库」，程序直接打开文件，没有「连上来」这个动作。 3. 本沙箱里「权限」退化成了哪三样？ —— 文件系统权限、只读连接（`?mode=ro`）、语句级白名单（`set_authorizer`）。

知道安全分几层了，下一篇具体看「账号」这件事：16.2 用户管理——建账号、改密码、删账号。

相关笔记