关于MySQL删除或重命名用户对自定义对象的影响

目录

• ​​1. 背景介绍​​
• ​​2. 数据库自定义对象​​
• ​​3. 自定义对象的SECURITY_TYPE属性​​
• ​​4. 修改用户定义后如何确保对象有效性​​
• ​​5. 数据库自定义对象的最佳实践​​
• ​​6. 8.0.22对rename和drop user的增加校验​​
• ​​7. 参考链接​​

1. 背景介绍

最近在做数据迁移过程中遇到一个MySQL用户相关的迁移问题,涉及到2个机房之间,由于2个机房的网段不一样,但数据库中用户定义对可访问的IP网段做了明确限定,​

​如,[email protected].%,当迁移到目标网段为200.1开头时,会导致用户无法连接数据库​

​,通常我们的做法可以有以下2种

1. 创建一个相同权限及密码的用户,但IP网段限制为目标端的IP段或直接用%不做限制
2. 对该用户用rename user的方式做重命名,将用户改为目标端的IP段或改为%不做限制

以上2种方式的都可以,个人会更倾向于使用第2种方式,减少冗余用户,但其中还有一些情况需要做考虑,那就是数据库中的自定义对象,这里我们把由用户创建的存储过程,触发器,视图,事件统称为数据库自定义对象,我们知道,​

​在通过drop user删除一个用户时,该用户的自定义对象并不会一起删除,依旧保留在数据库中,或者使用rename user对用户重命名时,自定义对象也不会一并修改​

​,这对于我们需要调用自定义对象时,很容易触发权限不足或自定义对象创建者不存在的错误.

下面我们具体讨论一下,MySQL中自定义对象涉及的​

​创建者,调用者,以及权限之间的关系​

​,以便我们在做数据迁移或对用户做变更时,能一并对相关的数据库对象做调整.

2. 数据库自定义对象

首先我们来看看数据库自定义对象(​

​存储过程,函数,事件,触发器,视图​

​)的定义可以通过哪些表查看到,以下是对应的5种数据库自定义对象的元数据对应表,其中部分定义在mysql库和information_schema有重叠

• information_schema.EVENTS

  - mysql.event

• information_schema.ROUTINES

  - mysql.proc

• information_schema.TRIGGERS
• information_schema.VIEWS

3. 自定义对象的SECURITY_TYPE属性

在我们创建自定义对象时都有一个属性SECURITY_TYPE可定义,从字面意思我们可以猜到其目的是安全相关,也就是做鉴权的处理,该属性有2个可选值,分别为​

​DEFINER和INVOKER​

​,下面我们分别介绍其各自值的作用

​

​设置为DEFINER​

​​表示的是,当某用户对该存储过程有execute权限,在调用时,我们判断的是该存储过程​

​创建者​

​是否有execute及相关的库表的访问权限,如果没有则报错,缺一不可

• 也就是在SECURITY_TYPE为DEFINER时,如果该存储过程的所属用户被删除了,那这个存储过程即被标识为失效/孤儿的存储对象(数据库中也叫做Orphan Stored Objects)

​

​设置为INVOKER​

​​表示的是,当某用户对该存储过程有execute权限,在调用时,我们判断的是​

​调用者​

​是否对该存储过程中涉及的相关库表的访问权限,如果没有则报错,缺一不可

• 也就是在SECURITY_TYPE为INVOKER的情况下,即使该存储过程的所属用户被删除了,只要调用者有该存储过程的执行权限及对应库表的权限,依旧可以正常使用

4. 修改用户定义后如何确保对象有效性

对于​

​存储过程,函数,事件​

​这3种,由于这类定义在mysql库中存在

• 对definer属性我们可以直接更新definer字段的值来修改
• 对SECURITY_TYPE属性,我们则可以直接用​

  ​alter procedure​

  ​语法来修改

update mysql.proc set definer='user_a@%' where definer='[email protected].%';
update mysql.event set definer='user_a@%' where definer='[email protected].%';

alter procedure employees.p_select SQL SECURITY INVOKER;      

对于​

​触发器,视图​

​​,由于mysql库中没存储,而information_schema库为特殊的内存临时数据库,无法做DDL操作,虽然MySQL提供了​

​ALTER VIEW​

​语法,实际就是对其做重建处理,我们可以通过cancat将需要修改的视图的定义拼接出来,修改其DEFINER重新执行即可,触发器由于涉及的定义较多,可考虑拼接,也可以直接查询触发器定义后手工执行

select concat("alter DEFINER=`user_a`@`%` SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") from information_schema.VIEWS where definer='[email protected].%';      

5. 数据库自定义对象的最佳实践

• 能不用就不用
• 在创建时​

  ​明确对DEFINER属性做定义​

  ​,尽量保证DEFINER定义的用户不会被修改或删除
• 在创建时​

  ​优先选择SQL SECURITY为INVOKER​

  ​​,以便DEFINER定义的用户被删除也不会影响对象的调用(可能会报权限不足,但不会报用户不存在),且能​

  ​更明确的控制​

  ​调用者是否有权限对数据库中的表操作

​

​ps:假设root用户创建一个存储过程包含drop database/drop table的操作,且SQL SECURITY设置为DEFINER,只要普通用户有该存储过程的执行权限,即使没有drop database和drop table的权限,则也可触发相关操作,带来潜在的风险​

​

6. 8.0.22对rename和drop user的增加校验

在对用户做rename或drop user时,如果该用户存在存储过程等自定义对象,会在执行完成后提示warning

mysql> rename user user_a to user_b;
Query OK, 0 rows affected, 1 warning (0.0060 sec)
Warning (code 4005): User 'user_a'@'%' is referenced as a definer account in a stored routine      

7. 参考链接

• ​​https://dev.mysql.com/doc/refman/8.0/en/stored-objects-security.html#stored-objects-security-sql-security​​
• ​​http://blog.itpub.net/27067062/viewspace-2130598/​​
• ​​https://www.bbsmax.com/A/QV5ZEGqwJy/​​