[TOC]
一、前言
最近在配合系统开发商进行上线的过程中，遇到了一个问题，从这个问题上，可以看到mysql数据库中虽然尽可能的想要为我们提供一些更加安全、便捷的功能，但是貌似却仍然存在一些不合理之处。
环境如下：
| 名称 | 意义 |
|---|---|
| A | 用户1 |
| B | 用户2 |
| DB1 | 数据库1 |
| DB2 | 数据库2 |
| VIEW1 | 视图1 |
| TABLE1 | 表1 |
二、问题
根据业务需求，我对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作为底座的数据库。因为用的比较少，所以不知道这样的校验机制缺失是否在这些数据库中有所缺失。但是，完善这样的数据库对象校验机制，其实对于使用着、维护者来说还是可以避免很多的坑。
