Mysql数据库“消失”的对象校验

[ 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作为底座的数据库。因为用的比较少，所以不知道这样的校验机制缺失是否在这些数据库中有所缺失。但是，完善这样的数据库对象校验机制，其实对于使用着、维护者来说还是可以避免很多的坑。