MySQL存储过程的权限问题分析

这是学习笔记的第 2296 篇文章

前几天和同事快速分析了一个小案例，是关于MySQL存储过程权限相关的，有一些技巧和思路可以借鉴。

问题的背景是有个开发同学提交了一个存储过程，但是反馈执行的时候报了access denied的报错，提示权限不足，想让DBA同学来做下确认和检查。

我们查看了相关存储过程代码，好几百行的感觉，整体扫了一遍逻辑，没有看起来很生僻的操作，都是一些看起来常规的数据处理操作。

对于这个问题，我们首先需要确认的就是存储过程的基础权限，比如SQL SECURITY，默认创建是definer，如果需要开放给其他的用户调用，则建议是设置为invoker，但是经过确认，这个部分的配置是正常的。

第二个排查点是外键，因为我们的环境中基本上是没有外键的，但是不排除历史遗留业务还是存在这种情况，外键是需要references相关的权限的，这个权限在我们标准化的管理配置中是没有的，检查了数据字典information_schema.TABLE_CONSTRAINTS和information_schema.REFERENTIAL_CONSTRAINTS没有得到任何收获。

所以我们有点怀疑是否是存储过程的逻辑有些问题，和业务同学确认了存储过程可以进行测试执行，我们使用默认的统一的管理员账号做了测试，执行失败，还是提示access denied, 于是使用root用户做了一次测试，当超级管理员root的执行时成功的，也就排除了逻辑层和权限不清晰的可能，所以排查目标进一步确认，还是需要确认权限。

这个时候问题来了，超级管理员root的权限是最大的，那么和默认的管理员账号还存在哪些差异呢，我们简单对比了下，貌似细小的权限项还有不少差异的。怎么能够快速进行测试呢。

这里我们可以使用反证法来做，首先我们不能对已有的管理员账号做任何变更，假设管理员账号是dba_admin,那么我们可以创建一个临时账号test_proc@'127.0.0.1'，将dba_admin的权限都复制过来，如果得到的报错是相同的，我们可以正式开始。

>>create user test_proc@'127.0.0.1' identified by 'xxxxx';

Query OK, 0 rows affected (0.01 sec)

>>grant all privileges on xxxx.* to test_proc@'127.0.0.1';

Query OK, 0 rows affected (0.00 sec)

>>show grants for test_proc@'127.0.0.1';

+---------------------------------------------------------------------------+

| Grants for [email protected] |

+---------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'test_proc'@'127.0.0.1' |

| GRANT ALL PRIVILEGES ON `xxxx`.* TO 'test_proc'@'127.0.0.1' |

+---------------------------

怎么得到ALL Privileges的权限明细呢，我们可以通过revoke来实现，比如我们就回收常见的insert权限。

>>revoke insert on xxxx.* from test_proc@'127.0.0.1';

Query OK, 0 rows affected (0.00 sec)

>>show grants for test_proc@'127.0.0.1';

GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `xxxx`.* TO 'test_proc'@'127.0.0.1'

可以明确，除了insert的权限，这些是组成ALL Privilegs的权限集合了。

接下来要做的是横向对比，根据整体存储过程的逻辑进行比对，我们很快发现CREATE TEMPORARY TABLES是一个潜在的权限，进行了多次验证，很快锁定了这个权限细节。问题就得到了妥善解决。

通过这个小问题的分析，也让自己对于一些不明确问题的分析有了一些思想上的提升，当然也需要做很多快捷的改进工作。

1）对于数据字典的总结和分析还不够，需要进一步完善mysql_lite这个项目的使用

2）自己最开始也意识到了是temporary tables相关的操作，但是通过搜索没有很快定位到这个问题，导致线索最开始断了之后就逐步偏离了方向

3）线上环境之前也碰到过这个问题，但是没有直接进行比对和分析，在分析方向上耽误了一些时间。

4）对于基础权限的管理还是不够规范，比如统一的管理员账号其实是应该统一接入create temporary tables这个权限的

5）应该引导业务尽可能放弃存储过程