代码区项目交易流程

MySQL存储过程的权限问题


mysql的存储过程,没错,看起来好生僻的使用场景。问题源于一个开发同学提交了权限申请的工单,需要开通一些权限。

本来是一个很正常的操作,但在我来看是比较着急且紧迫的,说来惭愧,忙着方向规划和开发的事情,这个基础的操作功能竟然给忽略了,所以看到目前的一些实现方式,还是希望能够做一些细小的事情把这些重复性的工作给解放了。

当然我决定把一些基础性的工作接过来,一方面是给同事减压,另一方面是在做一个完整的体验,因为很多需求和痛点通过实践是能够很容易捕捉到重点的,如果我觉得不合理,那么这个过程中势必会有一些改进的地方。比如部署安装,比如权限开通。数据库的权限开通就是一个相对典型的案例,而存储过程的权限开通甚至都有点让人怀疑人生了。

问题的场景还是很基础的,开发同学需要开通一些基础的权限,在标记权限的时候声明需要增删改查的权限,还有DDL的权限,比如drop,alter,create等等。看到这里,我就感觉不太妥了,什么样的操作竟然需要这么大的权限呢。


MySQL存储过程的权限问题

简单声明了下立场,开发同学的想法是能够方便管理,于是乎我就直接招过去了,简单沟通下,其实发现他们的需求场景还是很常规的,他们需要动态创建一些日表,那么需要create权限在评估之后是可以给与的,而对于一般的用户而言,create的权限是不建议开放的,主要的出发点就是能够对SQL进行一些基本的审核,哪怕是人工审核还是平台审核都是一个需要的过程。所以沟通了一圈发现,开通的权限就可以迅速裁剪,对他们而言,修改存储过程的逻辑也是需要的,因为在一些特定的场景下,他们对逻辑的控制希望能够更加灵活。

好了,基础的背景介绍完了。赋予基本的表的权限,赋予存储过程的权限,存储过程的这个地方需要注意一个重要的点是SQL SECURITY,默认创建是definer,如果需要开放给其他的用户调用,则建议是设置为invoker.

所以很简单的一句:

grant execute,alter procedure on xxx.xxx to xxx@'xxxx';

但是很不幸的,开发同学反馈,他们通过SQLyog或者是Navicator打开的时候,竟然看不到存储过程的内容。

因为我们没有select procedure或者view procedure的权限,所以我们几乎再无从干预了。

使用命令行的方式能够复现出这个问题:


MySQL存储过程的权限问题

没有存储过程的实质性内容。在那儿折腾了好一会,发现是个老问题了,10多年前的老问题了。

https://bugs.mysql.com/bug.php?id=20235

问题的解决其实很简单,就是需要这样一句:

grant select on mysql.proc to xxxx@'xxxx'即可

所以细粒度的权限控制就是这么纠结,但是确实有效。

比如我们举一反三一下,我们知道MySQL里的all privileges算是一个很大的权限,但是里面包含多少种权限,可能我们没有清晰的概念。

我们就完全可以通过细粒度的权限控制来反推。

比如创建一个用户,赋予all privileges的权限。

mysql> grant all privileges on test.* to 'jeanron'@'%' identified by 'jeanron100';

Query OK, 0 rows affected, 1 warning (0.00 sec)

包含的权限如下:

mysql> show grants for jeanron;

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

| Grants for jeanron@% |

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

| GRANT USAGE ON *.* TO 'jeanron'@'%' |

| GRANT ALL PRIVILEGES ON `test`.* TO 'jeanron'@'%' |

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

2 rows in set (0.00 sec)

我们做一下收缩。

mysql> revoke insert on test.* from jeanron@'%';

Query OK, 0 rows affected (0.00 sec)

喏,all privileges的权限就现出原形了。

mysql> show grants for jeanron;

| Grants for jeanron@%

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

| GRANT USAGE ON *.* TO 'jeanron'@'%'

| 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 `test`.* TO 'jeanron'@'%' |

所以在上面的问题中,其实如果select on *.*其实已经包含了我们需要的细粒度权限mysql.proc,如果要抽丝剥茧,基本就是这样的套路。

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

点击收藏

LAST Dew Drop January 4, 2019 (#2872) Linux dream machine under 900 euro NEXT