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';
+--------+------+-------------------------------------------+
| user | host | password |
+--------+------+-------------------------------------------+
| haohan | % | *DC593F27E34ADDEEBEBDF4B9FAC887373B642336 |
+--------+------+-------------------------------------------+
1 row in set
上述示例中,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';
+--------+------+-------+
| user | host | db |
+--------+------+-------+
| haohan | % | samp% | ##刚刚添加进去的
| haohan | % | test |
+--------+------+-------+
2 rows in set
这个例子中通过 %
模糊匹配,所有 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
权限:
- 先查看该用户是否拥有全局 Insert 权限:
SELECT Insert_priv FROM mysql.user WHERE user='haohan' AND host='%';
- 如果没有,再查看该用户是否拥有 number_pool 数据库级别的 Insert 权限:
SELECT Insert_priv FROM mysql.db WHERE user='haohan' AND host='%';
- 如果仍然没有,则继续判断是否拥有 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
表中查找到。
权限类型 | 权限变量名 | 权限简述 |
---|---|---|
ALL | AllPriv | 所有权限 |
Drop | DropPriv | 删除 schema/table |
Index | IndexPriv | 创建/删除 index |
Alter | AlterPriv | 执行 ALTER 语句 |
Super | SuperPriv | 所有权限 |
Grant | GrantPriv | 授予其他用户权限 |
Create | CreatePriv | 创建 schema/table |
Select | SelectPriv | 读取表内容 |
Insert | InsertPriv | 插入数据到表 |
Update | UpdatePriv | 更新表中数据 |
Delete | DeletePriv | 删除表中数据 |
Trigger | TriggerPriv | 尚未使用 |
Process | ProcessPriv | 显示正在运行的任务 |
Execute | ExecutePriv | 执行 execute 语句 |
Drop Role | DropRolePriv | 执行 drop role |
Show View | ShowViewPriv | 执行 show create view |
References | ReferencesPriv | 尚未使用 |
Create View | CreateViewPriv | 创建视图 |
Create User | CreateUserPriv | 创建用户 |
Create Role | CreateRolePriv | 执行 create role |
Show Databases | ShowDBPriv | 显示 database 内的表情况 |
ALTER
- 对于所有的
ALTER
语句,均需要用户对所操作的表拥有ALTER
权限。 - 除
ALTER...DROP
和ALTER...RENAME TO
外,均需要对所操作表拥有INSERT
和CREATE
权限。 - 对于
ALTER...DROP
语句,需要对表拥有DROP
权限。 - 对于
ALTER...RENAME TO
语句,需要对重命名前的表拥有DROP
权限,对重命名后的表拥有CREATE
和INSERT
权限。
### CREATE DATABASE 需要对数据库拥有注意:
根据 MySQL 5.7 文档中的说明,对表进行
ALTER
操作需要INSERT
和CREATE
权限,但在 MySQL 5.7.25 版本实际情况中,该操作仅需要ALTER
权限。目前,TiDB 中的ALTER
权限与 MySQL 实际行为保持一致。
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;
+------+------+-------------+-------------+
| User | Host | Select_priv | Insert_priv |
+------+------+-------------+-------------+
| root | % | | |
+------+------+-------------+-------------+
1 row in set
这条记录中,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;
限制和约束
一些使用频率偏低的权限当前版本的实现中还未做检查,比如FILE
/USAGE
/SHUTDOWN
/EXECUTE
/PROCESS
/INDEX
等等,未来会陆续完善。
现阶段对权限的支持还没有做到 column 级别。