关于MySQL删除或重命名用户对自定义对象的影响
1. 背景介绍
最近在做数据迁移过程中遇到一个MySQL用户相关的迁移问题,涉及到2个机房之间,由于2个机房的网段不一样,但数据库中用户定义对可访问的IP网段做了明确限定,
如,user_a@100.1.%,当迁移到目标网段为200.1开头时,会导致用户无法连接数据库,通常我们的做法可以有以下2种
- 创建一个相同权限及密码的用户,但IP网段限制为目标端的IP段或直接用%不做限制
- 对该用户用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='user_a@100.1.%';
update mysql.event set definer='user_a@%' where definer='user_a@100.1.%';
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='user_a@100.1.%';
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
