mysql通配符授权的坑

背景

朋友业务上有一个需求:

业务库 fanderdb 上有 10 张表，表名有规律，叫 fander_tb1、fander_tb2、fander_tb3... fander_tb10。他需要实现这些已存在的表对用户 fander@'192.168.199.%' 只读，并且允许此用户在库上新建其他表，能对新建表增删改查。

如何实施

这个需求看起来容易实施

# 1. 授权已存在的表
grant select on `fanderdb`.`fander\_tb%` to fander@'192.168.199.%';

# 2. 授权新建的表
grant select,insert,update,delete,create on `fanderdb`.`new\_%` to fander@'192.168.199.%'

这里涉及两个细节

1. 通配符

实施的细节之一就是，通过 MySQL 的通配符实现表名的模糊匹配授权。fander_tb1、fander_tb2、fander_tb3... fander_tb10 抽象成 fander_tb%。

在 MySQL 里通配符有两种

• %
    匹配所有字符
• _
    匹配一个任意字符

对于通配符%
，我想说的是，*
在某些语言中是通配符，但在 MySQL 里正确的是%
，所以我们用fander\_tb%
而不是 fander\_tb*
，后者语法不正确。*
在 MySQL 代表的是匹配所有对象，而不是%
的匹配所有字符，例如所有字段、所有库、所有表:

# 查询 fander_tb1 表的所有字段
select * from fanderdb.fander_tb1;

# 授权所有库、所有表 all 权限
grant all on *.* to root;

后者是匹配所有字符，例如

# 网段授权例子，匹配所有字符
grant select on fanderdb.fander_tb1 to fander@'192.168.199.%'

# like 查询例子
mysql> select * from fanderdb.fander_tb1 where name like 'fander%';
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | fander            |
|    3 | fanderchan  |
|    4 | fander_chan |
+------+-------------+
3 rows in set (0.00 sec)

# show variables 例子
mysql> show variables like '%repl%';
+-----------------------------------------------+-------+
| Variable_name                                 | Value |
+-----------------------------------------------+-------+
| innodb_replication_delay                      | 0     |
| replication_optimize_for_static_plugin_config | OFF   |
| replication_sender_observe_commit_only        | OFF   |
+-----------------------------------------------+-------+
3 rows in set (0.00 sec)

对于通配符_
，我们要注意很多时候我们没有使用它的需求，我们更常用的是_
原本的意思，也就是下划线，而不是通配符的代表匹配一个任意字符的意思，转义后，\_
才代表下划线。

举个例子:

grant select on `fander_1`.* to fander@'192.168.199.%';

我们原意是让用户 fander 只能对 fander_1 库上所有表可以查询，但结果是

mysql> select * from fander_1.test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from fander11.test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

他还能查询 fander11.test，这就是因为 MySQL 认为_
是通配符，正确授权方法应该是:

grant select on `fander\_1`.* to fander@'192.168.199.%';

也就是要转义一下。

mysql> select * from fander_1.test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from fander11.test;
ERROR 1142 (42000): SELECT command denied to user 'fander'@'192.168.199.132' for table 'test'

2. 新授权表的权限

实施的细节之二是，由于我们需求fanderdb`.`fander\_tb%
表只读，所以其他新建的表不能用fanderdb`.`*
表示，因为fanderdb`.`*
是包含了 fanderdb`.`fander\_tb%
的，如果我授权了

grant select,insert,update,delete,create on `fanderdb`.`*` to fander@'192.168.199.%'

那么fanderdb`.`fander\_tb%
也肯定能 select,insert,update,delete,create
了。所以要实现这个需求，对新建表的表名需要约束，例如我们约定就用表名 new_
打头。

grant select,insert,update,delete,create on `fanderdb`.`new\_%` to fander@'192.168.199.%'

方案很完美，实施原地爆炸

mysql> grant select on `fanderdb`.`fander\_tb%` to fander@'192.168.199.%';
ERROR 1146 (42S02): Table 'fanderdb.fander\_tb%' doesn't exist
mysql> grant select,insert,update,delete,create on `fanderdb`.`new\_%` to fander@'192.168.199.%';
Query OK, 0 rows affected (0.02 sec)

第一个授权，报错意思是，我要赋这个授权的前提是，这个表得存在。好吧，表级授权还有这种要求！一个新的问题，第二个授权，我也是没有表的，为什么授权就成功呢？

我们可以看出两个授权语句一个关键的区别是，第一个授权没有带 create，第二个带 create 权限。

这个表不存在，如果你给它建表权限，能授权成功，测试确实如此。

mysql> grant create,select on `fanderdb`.`fander\_tb%` to fander@'192.168.199.%';
Query OK, 0 rows affected (0.01 sec)

但！这就完了？我们看看我们都弄出了些什么破玩意！（见红字）

mysql> select * from mysql.tables_priv;
+-----------------+----------+---------------+-------------------+----------------------+---------------------+------------------------------------+-------------+
| Host            | Db       | User          | Table_name        | Grantor              | Timestamp           | Table_priv                         | Column_priv |
+-----------------+----------+---------------+-------------------+----------------------+---------------------+------------------------------------+-------------+
| localhost       | mysql    | mysql.session | user              | boot@connecting host | 0000-00-00 00:00:00 | Select                             |             |
| localhost       | sys      | mysql.sys     | sys_config        | root@localhost       | 2022-08-23 19:41:07 | Select                             |             |
| 192.168.199.10% | mysql    | orchestrator  | slave_master_info | admin@127.0.0.1      | 0000-00-00 00:00:00 | Select                             |             |
| 192.168.199.%   | fanderdb | fander        | new\_%            | admin@127.0.0.1      | 0000-00-00 00:00:00 | Select,Insert,Update,Delete,Create |             |
| 192.168.199.%   | fanderdb | fander        | fander\_tb%       | admin@127.0.0.1      | 0000-00-00 00:00:00 | Select,Create                      |             |
+-----------------+----------+---------------+-------------------+----------------------+---------------------+------------------------------------+-------------+
6 rows in set (0.00 sec)

mysql 元数据表 tables_priv 负责记录表级授权明细，里面记录的表名是一个通配符名字，不知道有没有问题，我们登录数据库验证一下

mysql -h192.168.199.132 -ufander -pmypasswd -P3308
mysql> show grants for current_user;
+-------------------------------------------------------------------------------------------------+
| Grants for fander@192.168.199.%                                                                 |
+-------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'fander'@'192.168.199.%'                                                  |
| GRANT SELECT, CREATE ON `fanderdb`.`fander\_tb%` TO 'fander'@'192.168.199.%'                    |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `fanderdb`.`new\_%` TO 'fander'@'192.168.199.%' |
+-------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> create table new_1 (id int);
ERROR 1142 (42000): CREATE command denied to user 'fander'@'192.168.199.132' for table 'new_1'
mysql> create table `new\_%` (id int);
Query OK, 0 rows affected (0.10 sec)

呜呼！有问题啊！想建的表建不了，只能建一张就叫通配符名字的表，这不是我们想要的。官方文档是这么说的

以下是，谷歌中文机器翻译

简单说，就是库级支持通配符，但表级不支持，原来如此！

需求满足不了，寻找替代方案

需求满足不了，那么我们就调整需求吧。。

我们再重新看一下需求

业务库 fanderdb 上有 10 张表，表名有规律，叫 fander_tb1、fander_tb2、fander_tb3... fander_tb10。他需要实现这些已存在的表对用户 fander@'192.168.199.%' 只读，并且允许此用户在库上新建其他表，能对新建表增删改查。

这个需求其实就两部分:

• 对 10 张表的只读查询
• 对新增库的写入

对于第一部分，因为前面实验和查询官方文档发现 MySQL 不支持对表级做通配符处理，那么我们就把这 10 张表全列出来吧，不用通配符了。

grant select on `fanderdb`.`fander_tb1` to fander@'192.168.199.%';
grant select on `fanderdb`.`fander_tb2` to fander@'192.168.199.%';
grant select on `fanderdb`.`fander_tb3` to fander@'192.168.199.%';
...
grant select on `fanderdb`.`fander_tb10` to fander@'192.168.199.%';

对于第二部分，我们把这些新建表放到另外一个 database，叫 fanderdb2，那么表名我们就可以使用*
了，不要用到%
通配符。

grant select,insert,update,delete,create on `fanderdb2`.`*` to fander@'192.168.199.%';

也就是我调整了需求，原需求是要求在同库上，现在我要做到跨库，只读的表在一个库，读写的表在另外一个库。

如果这些表有跨库关联查询，MySQL 是能支持的，没问题。

mysql> select a.id,b.id from fanderdb.test a join fanderdb2.test2 b;
+------+------+
| id   | id   |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

总结

使用 MySQL 通配符授权时，要知道两个坑，第一个坑是只能对库使用通配符，不能对表使用通配符，第二个坑是_
通配符我们很少使用，遇到含_
的库名，应当先确认一下我们是使用通配符，还是使用\_
转义回普通下划线。

参考:
https://dev.mysql.com/doc/refman/8.0/en/grant.html