一、前言

最近在配合系统开发商进行上线的过程中，遇到了一个问题，从这个问题上，可以看到mysql数据库中虽然尽可能的想要为我们提供一些更加安全、便捷的功能，但是貌似却仍然存在一些不合理之处。

环境如下：

二、问题

根据业务需求，我对A做了如下的权限分配：

>show grants for A;

| Grants for A@%                                    |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'A'@'%'                             |
| GRANT SELECT ON `DB1`.* TO 'A'@'%'                        |
| GRANT ALL PRIVILEGES ON `DB2`.* TO 'A'@'%'                |
| GRANT SELECT ON `DB3`.* TO 'A'@'%'                        |
+-----------------------------------------------------------+
6 rows in set (0.00 sec)

在整个环境中，DB1中存在部分业务表以及业务视图；当我通过用户A查询DB1中的表时，数据库中的表可以正常查询：

>select count(*) from A.TABLE1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

但是当我查询DB1中的视图时，却发现报如下错误：

>select count(*) from DB1.VIEW1;
ERROR 1045 (28000): Access denied for user 'A'@'%' (using password: YES)

可以发现，虽然已经成功登陆了A用户，但是当查询视图的时候，却提示一个看起来是登陆失败的问题。

虽然，已经给了用户DB1的所有对象读权限，但是为什么在查询视图的时候会有这样的报错提示呢？难道视图在MYSQL中被设计成单独的权限控制么？

带上面的两个疑问，进行进一步测试和分析。

三、问题排查

对于这个问题的排查，首先怀疑是权限没有设置正确。因此，再次给A用户单独分配DB1的VIEW1查询权限：

grant select on DB1.VIEW1 to 'A'@'%';

经过测试，发现问题依旧存在；

进一步，赋予用户A更大权限进行测试：

grant all on *.* to 'A'@'%';

发现虽然权限已经最大了，但是还是报同样的错误；

于是，直接以root用户登陆数据库，执行测试：

>mysql -uroot -p
>select count(*) from DB1.VIEW1;

ERROR 1449 (HY000): The user specified as a definer ('B'@'%') does not exist

发现此时的报错变了，错误信息非常明确，即指定definer(‘B’@’%’)不存在。

因此，我对VIEW的定义进行查询：

>show create view DB2.VIEW1;
.....
CREATE ALGORITHM=UNDEFINED DEFINER=`B`@`%` SQL SECURITY DEFINER VIEW `A`.`VIEW1` 
AS 
select `A`.`TABLE1`.`datatimestamp` AS `datatimestamp`,
`A`.`TABLE1`.`sessionid` AS `sessionid`,
`A`.`TABLE1` .`agentid` AS `agentid`,
`A`.`TABLE1` .`ringdialtimestamp` AS `ringdialtimestamp`,
`A`.`TABLE1` .`establishedtimestamp` AS `establishedtimestamp`,
`A`.`TABLE1` .`releasedtimestamp` AS `releasedtimestamp` 
from `A`.`TABLE1`
where 
((`A`.`TABLE1` .`userdata` is not null) 
and (`A`.`TABLE1` .`userdata` <> '')) 
order by `A`.`TABLE1`.`dbid` 
desc limit 0,60
...

可以发现,这个视图里面的DEFINER为B@%，而我们对数据库中的用户表进行查询，确实发现没有B@%:

select * from performance_schema.user\G

后续，经过对系统的初始化脚本检查发现，在数据初始化脚本中发现有如下的信息：

-- Final view structure for view `VIEW1`
-- Final view structure for view `VIEW1`
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`B`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `A`.`VIEW1` AS 
select `A`.`TABLE1`.`datatimestamp` AS `datatimestamp`,
`A`.`TABLE1`.`sessionid` AS `sessionid`,
`A`.`TABLE1` .`agentid` AS `agentid`,
`A`.`TABLE1` .`ringdialtimestamp` AS `ringdialtimestamp`,
`A`.`TABLE1` .`establishedtimestamp` AS `establishedtimestamp`,
`A`.`TABLE1` .`releasedtimestamp` AS `releasedtimestamp` 
from `A`.`TABLE1`
where 
((`A`.`TABLE1` .`userdata` is not null) 
and (`A`.`TABLE1` .`userdata` <> '')) 
order by `A`.`TABLE1`.`dbid` 
desc limit 0,60*/;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

这个脚本是系统开发商从开发测试上直接导出的sqlfile，在开发测试环境中B用户是存在的，但是上生产前提出的需求并没有创建这个用户。

从上面的脚本中看，起初第一眼看到了/* */这样的标注，下意识的就认为，这个只是个注释，不会被执行，但后续了解到/*!50013*/表示的意思是在5.0.0.13版本后，这部分内容就会被执行。因此，会出现这些视图虽然在A用户下，但是DEFINER则是B用户，最终导致我们出现上述的情况。

四、解决方式

产生这种情况后，我们需要修改definier，而在mysql中，对于不同的对象的definer修改具有不同的方式

1.存储过程和函数的definer：

select definer from mysql.proc;
update mysql.proc set definer=`A`@`%`;

2.修改event的definer:

select DEFINER from mysql.EVENT;
update mysql.EVENT set definer=`A`@`%`;

3.修改view的definer：

select DEFINER from information_schema.VIEWS;
alter DEFINER=`A`@`%` SQL SECURITY DEFINER VIEW mydb.myview as select * from mytable;

如果有很多，需要批量修改，则需要拼接sql：

select concat("alter DEFINER=`A`@`%` SQL SECURITY DEFINER VIEW ", TABLE_SCHEMA, ".",TABLE_NAME, " as ", VIEW_DEFINITION, ";") from information_schema.VIEWS where DEFINER = 'B@%';

五、结束语

在Mysql中，缺失了一些对数据库对象校验的机制，比如上述的definer对象的问题、赋权的问题(我可以将一个不存在的对象成功赋予用户)，从而导致一些不可预期的错误，而且普通用户下相应的报错日志也并不明确，排查起来具有一定的迷惑性。由于这些错误只有在使用的时候才可能会被触发。因此，我们在进行部署、迁移时候还是需要对这些点做一些细致的检查，或者充分的功能测试。

目前国产化中也有很多基于Mysql作为底座的数据库。因为用的比较少，所以不知道这样的校验机制缺失是否在这些数据库中有所缺失。但是，完善这样的数据库对象校验机制，其实对于使用着、维护者来说还是可以避免很多的坑。