TiDB数据库权限管理

刺骨的言语ヽ痛彻心扉 2023-06-19 02:51 60阅读 0赞

# TiDB数据库权限管理 #

TiDB 的权限管理系统按照 MySQL 的权限管理进行实现，TiDB 支持大部分的 MySQL 的语法和权限类型。

本文主要介绍 TiDB 权限相关操作、各项操作需要的权限以及权限系统的实现。

## 权限相关操作 ##

### 授予权限 ###

授予 `xxx` 用户对数据库 `test` 的读权限：

GRANT SELECT ON test.* TO 'xxx'@'%';   ## test.*表示test数据库下所有的表

为 `xxx` 用户授予所有数据库，全部权限：

GRANT ALL PRIVILEGES ON *.* TO 'xxx'@'%';

`GRANT` 为一个不存在的用户授予权限时，默认并不会自动创建用户。该行为受 SQL Mode 中的 `NO_AUTO_CREATE_USER` 控制。 如果从 SQL Mode 中去掉 `NO_AUTO_CREATE_USER`，当 `GRANT` 的目标用户不存在时，TiDB 会自动创建用户。

mysql> select @@sql_mode;

+-------------------------------------------------------------------------------------------------------------------------------------------+
    | @@sql_mode                                                                                                                                |
    +-------------------------------------------------------------------------------------------------------------------------------------------+
    | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
    +-------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set

set @@sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Query OK, 0 rows affected

SELECT * FROM mysql.user WHERE user='haohan';

Empty set

GRANT ALL PRIVILEGES ON test.* TO 'haohan'@'%' IDENTIFIED BY 'haohan';

Query OK, 1 row affected

SELECT user,host,password FROM mysql.user WHERE user='haohan';

上述示例中，`haohan@%` 即自动添加的用户。

`GRANT` 对于数据库或者表的授权，不检查数据库或表是否存在。

SELECT * FROM test.xxxx;

1146 - Table 'test.xxxx' doesn't exist

GRANT ALL PRIVILEGES ON test.xxxx TO haohan;

Query OK, 0 rows affected

SELECT user,host FROM mysql.tables_priv WHERE user='haohan';

+--------+------+
    | user   | host |
    +--------+------+
    | haohan | %    |
    +--------+------+
    1 row in set

`GRANT` 可以模糊匹配地授予数据库和表：

GRANT ALL PRIVILEGES ON `samp%`.* TO haohan;

Query OK, 0 rows affected

SELECT user,host,db FROM mysql.db WHERE user='haohan';

这个例子中通过 `%` 模糊匹配，所有 `samp` 开头的数据库，都被授予了权限。

### 收回权限 ###

`REVOKE` 语句与 `GRANT` 对应：

REVOKE ALL PRIVILEGES ON `test`.* FROM 'haohan'@'%';

### `注意：` ###

`REVOKE` 收回权限时只做精确匹配，若找不到记录则报错。而 `GRANT` 授予权限时可以使用模糊匹配。

REVOKE ALL PRIVILEGES ON `samp`.* FROM 'haohan'@'%';

1105 - There is no such grant defined for user 'haohan' on host '%' on database samp

关于模糊匹配和转义，字符串和 identifier：

GRANT ALL PRIVILEGES ON `samp\%`.* TO 'haohan'@'%';

Query OK, 0 rows affected

上述例子是精确匹配名为 `samp%</.code> 的数据库，注意使用 \ 转义字符。`

以单引号包含的部分，是一个字符串。以反引号包含的部分，是一个 identifier。注意下面的区别：

GRANT ALL PRIVILEGES ON 'test'.* TO 'haohan'@'%';

1064 - You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 30 near "'test'.* TO 'haohan'@'%';"

GRANT ALL PRIVILEGES ON `test`.* TO 'haohan'@'%';

Query OK, 0 rows affected

如果想将一些特殊的关键字做为表名，可以用反引号包含起来。比如：

CREATE TABLE `select` (id int);

Query OK, 0 rows affected

### 查看为用户分配的权限 ###

`SHOW GRANTS` 语句可以查看为用户分配了哪些权限。例如：

查看当前用户的权限：

SHOW GRANTS;

+-------------------------------------------------------------+
    | Grants for User                                             |
    +-------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
    +-------------------------------------------------------------+
    1 row in set

查看某个特定用户的权限：

SHOW GRANTS for 'haohan'@'%';

+---------------------------------------------------+
    | Grants for haohan@%                               |
    +---------------------------------------------------+
    | GRANT USAGE ON *.* TO 'haohan'@'%' | | GRANT ALL PRIVILEGES ON samp\%.* TO 'haohan'@'%' | | GRANT ALL PRIVILEGES ON test.* TO 'haohan'@'%' | | GRANT ALL PRIVILEGES ON test.xxxx TO 'haohan'@'%' |
    +---------------------------------------------------+
    4 rows in set

更精确的方式，可以通过直接查看授权表的数据实现。比如想知道，名为 hanhan@% 的用户是否拥有对 `number_pool.user` 的 `Insert` 权限：

1.  先查看该用户是否拥有全局 Insert 权限：

SELECT Insert_priv FROM mysql.user WHERE user='haohan' AND host='%';

1.  如果没有，再查看该用户是否拥有 number\_pool 数据库级别的 Insert 权限：

SELECT Insert_priv FROM mysql.db WHERE user='haohan' AND host='%';

1.  如果仍然没有，则继续判断是否拥有 number\_pool.user这张表的 Insert 权限：

SELECT table_priv FROM mysql.tables_priv WHERE user='haohan' AND host='%' AND db='number_pool';

## TiDB 各操作需要的权限 ##

TiDB 用户目前拥有的权限可以在 `INFORMATION_SCHEMA.USER_PRIVILEGES` 表中查找到。

<table> 
 <thead> 
  <tr> 
   <th align="center">权限类型</th> 
   <th align="center">权限变量名</th> 
   <th align="center">权限简述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">ALL</td> 
   <td align="center">AllPriv</td> 
   <td align="center">所有权限</td> 
  </tr> 
  <tr> 
   <td align="center">Drop</td> 
   <td align="center">DropPriv</td> 
   <td align="center">删除 schema/table</td> 
  </tr> 
  <tr> 
   <td align="center">Index</td> 
   <td align="center">IndexPriv</td> 
   <td align="center">创建/删除 index</td> 
  </tr> 
  <tr> 
   <td align="center">Alter</td> 
   <td align="center">AlterPriv</td> 
   <td align="center">执行 <code>ALTER</code> 语句</td> 
  </tr> 
  <tr> 
   <td align="center">Super</td> 
   <td align="center">SuperPriv</td> 
   <td align="center">所有权限</td> 
  </tr> 
  <tr> 
   <td align="center">Grant</td> 
   <td align="center">GrantPriv</td> 
   <td align="center">授予其他用户权限</td> 
  </tr> 
  <tr> 
   <td align="center">Create</td> 
   <td align="center">CreatePriv</td> 
   <td align="center">创建 schema/table</td> 
  </tr> 
  <tr> 
   <td align="center">Select</td> 
   <td align="center">SelectPriv</td> 
   <td align="center">读取表内容</td> 
  </tr> 
  <tr> 
   <td align="center">Insert</td> 
   <td align="center">InsertPriv</td> 
   <td align="center">插入数据到表</td> 
  </tr> 
  <tr> 
   <td align="center">Update</td> 
   <td align="center">UpdatePriv</td> 
   <td align="center">更新表中数据</td> 
  </tr> 
  <tr> 
   <td align="center">Delete</td> 
   <td align="center">DeletePriv</td> 
   <td align="center">删除表中数据</td> 
  </tr> 
  <tr> 
   <td align="center">Trigger</td> 
   <td align="center">TriggerPriv</td> 
   <td align="center">尚未使用</td> 
  </tr> 
  <tr> 
   <td align="center">Process</td> 
   <td align="center">ProcessPriv</td> 
   <td align="center">显示正在运行的任务</td> 
  </tr> 
  <tr> 
   <td align="center">Execute</td> 
   <td align="center">ExecutePriv</td> 
   <td align="center">执行 execute 语句</td> 
  </tr> 
  <tr> 
   <td align="center">Drop Role</td> 
   <td align="center">DropRolePriv</td> 
   <td align="center">执行 drop role</td> 
  </tr> 
  <tr> 
   <td align="center">Show View</td> 
   <td align="center">ShowViewPriv</td> 
   <td align="center">执行 show create view</td> 
  </tr> 
  <tr> 
   <td align="center">References</td> 
   <td align="center">ReferencesPriv</td> 
   <td align="center">尚未使用</td> 
  </tr> 
  <tr> 
   <td align="center">Create View</td> 
   <td align="center">CreateViewPriv</td> 
   <td align="center">创建视图</td> 
  </tr> 
  <tr> 
   <td align="center">Create User</td> 
   <td align="center">CreateUserPriv</td> 
   <td align="center">创建用户</td> 
  </tr> 
  <tr> 
   <td align="center">Create Role</td> 
   <td align="center">CreateRolePriv</td> 
   <td align="center">执行 create role</td> 
  </tr> 
  <tr> 
   <td align="center">Show Databases</td> 
   <td align="center">ShowDBPriv</td> 
   <td align="center">显示 database 内的表情况</td> 
  </tr> 
 </tbody> 
</table>

### ALTER ###

*  对于所有的 `ALTER` 语句，均需要用户对所操作的表拥有 `ALTER` 权限。
 *  除 `ALTER...DROP` 和 `ALTER...RENAME TO` 外，均需要对所操作表拥有 `INSERT` 和 `CREATE` 权限。
 *  对于 `ALTER...DROP` 语句，需要对表拥有 `DROP` 权限。
 *  对于 `ALTER...RENAME TO` 语句，需要对重命名前的表拥有 `DROP` 权限，对重命名后的表拥有 `CREATE` 和 `INSERT` 权限。

> **注意：**
> 
> 根据 MySQL 5.7 文档中的说明，对表进行 `ALTER` 操作需要 `INSERT` 和 `CREATE` 权限，但在 MySQL 5.7.25 版本实际情况中，该操作仅需要 `ALTER` 权限。目前，TiDB 中的 `ALTER` 权限与 MySQL 实际行为保持一致。

\#\#\# CREATE DATABASE 需要对数据库拥有 `CREATE` 权限。

### CREATE INDEX ###

需要对所操作的表拥有`INDEX` 权限。

### CREATE TABLE ###

需要对所操作的表拥有 `CREATE` 权限；若使用 `CREATE TABLE…LIKE…`需要对相关的表拥有 `SELECT` 权限。

### CREATE VIEW ###

需要拥有 `CREATE VIEW` 权限。

> **注意：**
> 
> 如果当前登录用户与创建视图的用户不同，除需要 `CREATE VIEW` 权限外，还需要 `SUPER` 权限。

### DROP DATABASE ###

需要对数据库拥有 `DROP` 权限。

### DROP INDEX ###

需要对所操作的表拥有`INDEX` 权限。

### DROP TABLES ###

需要对所操作的表拥有 `DROP` 权限。

### TRUNCATE TABLE ###

需要对所操作的表拥有 `DROP` 权限。

### RENAME TABLE ###

需要对重命名前的表拥有 `ALTER` 和 `DROP` 权限，对重命名后的表拥有 `CREATE` 和 `INSERT` 权限。

### ANALYZE TABLE ###

需要对所操作的表拥有 `INSERT` 和 `SELECT` 权限。

### SHOW ###

`SHOW CREATE TABLE` 需要任意一种权限。

`SHOW CREATE VIEW` 需要 `SHOW VIEW` 权限。

### CREATE ROLE/USER ###

`CREATE ROLE` 需要 `CREATE ROLE` 权限。

`CREATE USER` 需要 `CREATE USER` 权限

### DROP ROLE/USER ###

`DROP ROLE` 需要 `DROPROLE` 权限。

`DROP USER` 需要 `CREATEUSER` 权限

### ALTER USER ###

`ALTER USER` 需要 `CREATEUSER` 权限。

### GRANT ###

`GRANT` 需要 `GRANT` 权限并且拥有 `GRANT` 所赋予的权限。

### REVOKE ###

`REVOKE` 需要 `SUPER` 权限。

## 权限系统的实现 ##

### 授权表 ###

以下几张系统表是非常特殊的表，权限相关的数据全部存储在这几张表内。

*  `mysql.user`：用户账户，全局权限
 *  `mysql.db`：数据库级别的权限
 *  `mysql.tables_priv`：表级别的权限
 *  `mysql.columns_priv`：列级别的权限，当前暂不支持

这几张表包含了数据的生效范围和权限信息。例如，`mysql.user` 表的部分数据：

SELECT User,Host,Select_priv,Insert_priv FROM mysql.user LIMIT 1;

这条记录中，`Host` 和 `User` 决定了 root 用户从任意主机 (%) 发送过来的连接请求可以被接受，而 `Select_priv` 和 `Insert_priv` 表示用户拥有全局的 `Select` 和 `Insert` 权限。`mysql.user` 这张表里面的生效范围是全局的。

`mysql.db` 表里面包含的 `Host` 和 `User` 决定了用户可以访问哪些数据库，权限列的生效范围是数据库。

理论上，所有权限管理相关的操作，都可以通过直接对授权表的 CRUD 操作完成。

实现层面其实也只是包装了一层语法糖。例如删除用户会执行：

DELETE FROM mysql.user WHERE user='haohan';

但是，不推荐手动修改授权表，建议使用 DROP USER 语句：

DROP USER 'heohan';   #这种可以删除允许所有号段的IP地址登陆的用户

或：

DROP USER 'haohan'@'192.168.100.%';  #这种可以删除固定IP地址或者一个范围的IP地址的用户

### 连接验证 ###

当客户端发送连接请求时，TiDB 服务器会对登录操作进行验证。验证过程先检查 `mysql.user` 表，当某条记录的 `User` 和 `Host` 和连接请求匹配上了，再去验证 Password。用户身份基于两部分信息，发起连接的客户端的 `Host` ，以及用户名 `User` 。如果 `User` 不为空，则用户名必须精确匹配。

User+Host 可能会匹配 `user` 表里面多行，为了处理这种情况， `user` 表的行是排序过的，客户端连接时会依次去匹配，并使用首次匹配到的那一行做权限验证。排序是按 `Host` 在前， `User` 在后。

### 请求验证 ###

连接成功之后，请求验证会检测执行操作是否拥有足够的权限。

对于数据库相关请求 (`INSERT`，`UPDATE`)，先检查 `mysql.user` 表里面的用户全局权限，如果权限够，则直接可以访问。如果全局权限不足，则再检查 `mysql.db` 表。

`user` 表的权限是全局的，并且不管默认数据库是哪一个。比如 `user` 里面有 `DELETE` 权限，任何一行，任何的表，任何的数据库。

`db`表里面，User 为空是匹配匿名用户，User 里面不能有通配符。Host 和 Db 列里面可以有 `%` 和`_`，可以模式匹配。

`user`和 `db`读到内存也是排序的。

`tables_priv` 和 `columns_priv` 中使用 `%`是类似的，但是在`Db`, `Table_name`, `Column_name` 这些列不能包含 `%`。加载进来时排序也是类似的。

### 生效时机 ###

TiDB 启动时，将一些权限检查的表加载到内存，之后使用缓存的数据来验证权限。系统会周期性的将授权表从数据库同步到缓存，生效则是由同步的周期决定，目前这个值设定的是 5 分钟。

修改了授权表，如果需要立即生效，可以手动调用：

FLUSH PRIVILEGES;