How to alter a Procedure Created by Another User in MySQL?
在企业级运维中，通常建议将应用程序用户和维护用户分开，并通过权限控制来确保维护人员只能修改他们负责的应用对象。对于MySQL中的存储过程，确实没有像Oracle数据库中的”ALTER ANY PROCEDURE”系统权限那样的直接授权方式。但是，可以通过一些解决在DDL中带有”DEFINER=”其他用户时报错的问题， 比如当user1修改user2创建的存储过程时，在DDL中带有“DEFINER=”其他用户时会报错如下:
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation
下面演示一下该问题。
MYSQL_root@127.0.0.1 [anbob]> select version(); +-------------------+ | version() | +-------------------+ | 8.0.20-commercial | +-------------------+ 1 row in set (0.00 sec)
创建用户 u1,u2
MYSQL_root@127.0.0.1 [anbob]> CREATE USER 'u1'@'localhost' IDENTIFIED BY 'anbob. com'; Query OK, 0 rows affected (0.02 sec) MYSQL_root@127.0.0.1 [anbob]> CREATE USER 'u2'@'localhost' IDENTIFIED BY 'anbob.com'; Query OK, 0 rows affected (0.03 sec) MYSQL_root@127.0.0.1 [anbob]> GRANT CREATE, SELECT ON anbob.* TO 'u1'@'localhost'; Query OK, 0 rows affected (0.03 sec) MYSQL_root@127.0.0.1 [anbob]> GRANT CREATE, SELECT ON anbob.* TO 'u2'@'localhost'; Query OK, 0 rows affected (0.02 sec) MYSQL_root@127.0.0.1 [anbob]> GRANT insert ON anbob.* TO 'u1'@'localhost'; Query OK, 0 rows affected (0.03 sec) MYSQL_root@127.0.0.1 [anbob]> GRANT CREATE ROUTINE,ALTER ROUTINE,execute ON anbob.* TO 'u2'@'localhost'; Query OK, 0 rows affected (0.03 sec) MYSQL_root@127.0.0.1 [anbob]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) MYSQL_root@127.0.0.1 [anbob]> SHOW GRANTS FOR 'u1'@'localhost'; +---------------------------------------------------------------+ | Grants for u1@localhost | +---------------------------------------------------------------+ | GRANT USAGE ON *.* TO `u1`@`localhost` | | GRANT SELECT, INSERT, CREATE ON `anbob`.* TO `u1`@`localhost` | +---------------------------------------------------------------+ 2 rows in set (0.01 sec)
u2用户创建表和存储过程
MYSQL_u1@127.0.0.1 [anbob]> create table testproc(
-> id mediumint(8) unsigned not null primary key auto_increment,
-> name char(15) not null default '',
-> pass char(32) not null default '',
-> note text not null
-> );
Query OK, 0 rows affected, 1 warning (0.13 sec)
MYSQL_root@127.0.0.1 [anbob]> insert into testproc values(1,'anbob','anbob.com','xxxxxx');
Query OK, 1 row affected (0.02 sec)
MYSQL_u1@127.0.0.1 [anbob]> insert into testproc values(2,'anbob','anbob.com','xxxxxx');
Query OK, 1 row affected (0.03 sec)
MYSQL_u2@127.0.0.1 [anbob]> create procedure proc_name (in parameter integer)
-> begin
-> if parameter=0 then
-> select * from testproc order by id asc;
-> else
-> select * from testproc order by id desc;
-> end if;
-> end;
-> //
Query OK, 0 rows affected (0.08 sec)
MYSQL_u2@127.0.0.1 [anbob]> call proc_name(0);
+----+-------+-----------+--------+
| id | name | pass | note |
+----+-------+-----------+--------+
| 1 | anbob | anbob.com | xxxxxx |
| 2 | anbob | anbob.com | xxxxxx |
+----+-------+-----------+--------+
2 rows in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
现在需要u1用户修改u2用户创建的存储过程proc_name
查看当前存储过程的DDL
MYSQL_root@127.0.0.1 [anbob]> SHOW PROCEDURE CODE proc_name;
ERROR 1289 (HY000): The 'SHOW PROCEDURE|FUNCTION CODE' feature is disabled; you need MySQL built with '--with-debug' to have it working
MYSQL_root@127.0.0.1 [anbob]> SHOW CREATE PROCEDURE proc_name \G
*************************** 1. row ***************************
Procedure: proc_name
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`u2`@`localhost` PROCEDURE `proc_name`(in parameter integer)
COMMENT 'my test'
begin
if parameter=0 then
select * from testproc order by id asc;
else
select * from testproc order by id desc;
end if;
end
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
给u1用户赋权
MYSQL_root@127.0.0.1 [anbob]> GRANT CREATE ROUTINE,ALTER ROUTINE,execute ON anbob.* TO 'u1'@'localhost'; Query OK, 0 rows affected (0.02 sec) MYSQL_root@127.0.0.1 [anbob]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.02 sec)
u1用户尝试修改存储过程
MYSQL_u1@127.0.0.1 [anbob]> ALTER procedure proc_name COMMENT 'my test'; Query OK, 0 rows affected (0.04 sec) MYSQL_u1@127.0.0.1 [anbob]> drop procedure if exists proc_name; Query OK, 0 rows affected (0.04 sec) MYSQL_u1@127.0.0.1 [anbob]> delimiter // MYSQL_u1@127.0.0.1 [anbob]> CREATE DEFINER=`u2`@`localhost` PROCEDURE `proc_name`(in parameter integer) -> COMMENT 'my test' -> begin -> if parameter=0 then -> select * from testproc order by id asc; -> else -> select * from testproc order by id desc; -> end if; -> end; -> // ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation
Note:
在mysql中修改存储过程body内容就是drop ，create. 上面的alter procuere增加注释comment正常，但是修改存储过程的内容时，提示没有权限，注意的DDL中增加了创建用户的DEFINER=`u2`@`localhost` , 报错是需要super或set_user_id权限(含root)，要么就是原u2用户。
super或set_user_id权限
online MySQL DOC
The DEFINER Attribute
A stored object definition can include a DEFINER attribute that names a MySQL account. If a definition omits the DEFINER attribute, the default object definer is the user who creates it.The following rules determine which accounts you can specify as the DEFINER attribute for a stored object:
If you have the SET_USER_ID privilege (or the deprecated SUPER privilege), you can specify any account as the DEFINER attribute. If the account does not exist, a warning is generated. Additionally, to set a stored object DEFINER attribute to an account that has the SYSTEM_USER privilege, you must have the SYSTEM_USER privilege.
Otherwise, the only permitted account is your own, specified either literally or as CURRENT_USER or CURRENT_USER(). You cannot set the definer to any other account.
Creating a stored object with a nonexistent DEFINER account creates an orphan object, which may have negative consequences; see Orphan Stored Objects.
MYSQL_root@127.0.0.1 [anbob]> grant SET_USER_ID ON anbob.* TO 'u1'@'localhost';
ERROR 3619 (HY000): Illegal privilege level specified for SET_USER_ID
MYSQL_root@127.0.0.1 [anbob]> grant SET_USER_ID ON *.* TO 'u1'@'localhost';
Query OK, 0 rows affected (0.02 sec)
Note:
set_user_id权限只能是系统级，不能指定在单个数据库，所以这权限也不小， 另外对于RDS环境的不太可能授权给这类权限，也不可能给root用户，所以有些人干脆就授权all，但也有可能安全检查不允许使用all。
GRANT ALL PRIVILEGES ON db.* TO another_user@'localhost';
给U1授权SET_USER_ID后
MYSQL_u1@127.0.0.1 [anbob]> CREATE DEFINER=`u2`@`localhost` PROCEDURE `proc_name`(in parameter integer) -> COMMENT 'my test' -> begin -> if parameter=0 then -> select * from testproc order by id asc; -> else -> select * from testproc order by id desc; -> end if; -> end; -> // Query OK, 0 rows affected (0.03 sec) MYSQL_u1@127.0.0.1 [anbob]> call proc_name(1); -> // +----+-------+-----------+--------+ | id | name | pass | note | +----+-------+-----------+--------+ | 2 | anbob | anbob.com | xxxxxx | | 1 | anbob | anbob.com | xxxxxx | +----+-------+-----------+--------+ 2 rows in set (0.00 sec)
如果以上授权不允许，那就需要用procedure的definer用户去修改存储过程内容。 当然如果维护用户权限和definer用户一样，也可以考虑，重建procedure到u1用户下，那清理掉DDL 的definer语句， 以U1身份创建，U2有执行权限，后期有其它用户也采用这类方式，但注意的是新definer用户的权限是否包含原用户权限。 或修改Procedure以执行者身份执行，但同样也要手动清楚definer语句。
CREATE DEFINER=`u2`@`localhost` PROCEDURE改为 CREATE PROCEDURE -- 如果是语句文件可以批量 -- sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i oldfile.sql
在oracle数据库中对于存储过程的执行权限验证可以分为创建者或调用者authid definer 或authid current_user, 在MySQL中存储过程创建中叫做SOL SECURITY 项definer或INVOKER 。
MYSQL_u1@127.0.0.1 [anbob]> CREATE DEFINER=`u2`@`localhost` PROCEDURE `proc_name`(in parameter integer) -> COMMENT 'my test' -> SQL SECURITY INVOKER -> begin -> if parameter=0 then -> select * from testproc order by id asc; -> else -> select * from testproc order by id desc; -> end if; -> end; -> // ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation MYSQL_u1@127.0.0.1 [anbob]> CREATE PROCEDURE `proc_name`(in parameter integer) -> COMMENT 'my test' -> begin -> if parameter=0 then -> select * from testproc order by id asc; -> else -> select * from testproc order by id desc; -> end if; -> end; -> // Query OK, 0 rows affected (0.05 sec) MYSQL_u2@127.0.0.1 [anbob]> call proc_name(1); -> // +----+-------+-----------+--------+ | id | name | pass | note | +----+-------+-----------+--------+ | 2 | anbob | anbob.com | xxxxxx | | 1 | anbob | anbob.com | xxxxxx | +----+-------+-----------+--------+ 2 rows in set (0.00 sec)
那无论哪个用户创建都会有definer用户，如果该用户删除，是否会影响存储过程呢？ 比如当前存储过程proc_name的definer还是u2.
MYSQL_root@127.0.0.1 [anbob]> drop user 'u2'@'localhost';
Query OK, 0 rows affected (0.01 sec)
MYSQL_u1@127.0.0.1 [anbob]> call proc_name(1);
ERROR 1449 (HY000): The user specified as a definer ('u2'@'localhost') does not exist
Note:
如果definer用户删除后，存储过程执行可能会提示用户已不存在。此时可以用其它用户创建变更definer用户，如果当前用户还存在，但不是localhost主机如%（从mysql.user表验证），存储过程依旧可以执行。
或者再创建一个同名用户
MYSQL_root@127.0.0.1 [anbob]> sHOW CREATE PROCEDURE proc_name \G
*************************** 1. row ***************************
Procedure: proc_name
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`u1`@`localhost` PROCEDURE `proc_name`(in parameter integer)
COMMENT 'my test'
begin
if parameter=0 then
select * from testproc order by id asc;
else
select * from testproc order by id desc;
end if;
end
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
MYSQL_root@127.0.0.1 [anbob]> call proc_name(1);
+----+-------+-----------+--------+
| id | name | pass | note |
+----+-------+-----------+--------+
| 2 | anbob | anbob.com | xxxxxx |
| 1 | anbob | anbob.com | xxxxxx |
+----+-------+-----------+--------+
MYSQL_root@127.0.0.1 [anbob]> select host,user from mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | root |
| % | u1 |
| localhost | anbob |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+------------------+
6 rows in set (0.00 sec)
MYSQL_root@127.0.0.1 [anbob]> drop user `u1`@`%`;
Query OK, 0 rows affected (0.03 sec)
MYSQL_root@127.0.0.1 [anbob]> select host,user from mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | root |
| localhost | anbob |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+------------------+
5 rows in set (0.00 sec)
MYSQL_root@127.0.0.1 [anbob]> call proc_name(1);
ERROR 1449 (HY000): The user specified as a definer ('u1'@'localhost') does not exist
MYSQL_root@127.0.0.1 [anbob]> CREATE USER 'u1'@'localhost' IDENTIFIED BY 'anbob.com';
Query OK, 0 rows affected (0.04 sec)
MYSQL_root@127.0.0.1 [anbob]> call proc_name(1);
ERROR 1370 (42000): execute command denied to user 'u1'@'localhost' for routine 'anbob.proc_name'
MYSQL_root@127.0.0.1 [anbob]> GRANT CREATE, SELECT ON anbob.* TO 'u1'@'localhost';
Query OK, 0 rows affected (0.04 sec)
MYSQL_root@127.0.0.1 [anbob]> GRANT CREATE ROUTINE,ALTER ROUTINE,execute ON anbob.* TO 'u1'@'localhost';
Query OK, 0 rows affected (0.03 sec)
MYSQL_root@127.0.0.1 [anbob]> call proc_name(1);
+----+-------+-----------+--------+
| id | name | pass | note |
+----+-------+-----------+--------+
| 2 | anbob | anbob.com | xxxxxx |
| 1 | anbob | anbob.com | xxxxxx |
+----+-------+-----------+--------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
对应的数据字典
MYSQL_root@127.0.0.1 [information_schema]> select ROUTINE_SCHEMA,SPECIFIC_NAME,ROUTINE_NAME,ROUTINE_TYPE,IS_DETERMINISTIC,DEFINER,ROUTINE_COMMENT from ROUTINES WHERE SPECIFIC_NAME='proc_name'; +----------------+---------------+--------------+--------------+------------------+--------------+-----------------+ | ROUTINE_SCHEMA | SPECIFIC_NAME | ROUTINE_NAME | ROUTINE_TYPE | IS_DETERMINISTIC | DEFINER | ROUTINE_COMMENT | +----------------+---------------+--------------+--------------+------------------+--------------+-----------------+ | anbob | proc_name | proc_name | PROCEDURE | NO | u2@localhost | my test | +----------------+---------------+--------------+--------------+------------------+--------------+-----------------+ 1 row in set (0.01 sec)
在V8.0之前的版本存储过程的definer在mysql.proc字典表，当definer用户不存在是可以update mysql.proc set definer=‘’ 更新为存在的用户，但是从v8.0开始 MySQL在实现DDL的原子化增强，使用字典表从MyISAM转向Innodb存储引擎，重写了一些数据字典，去掉了mysql.proc，并出于安全考虑隐藏了一些internal 的字典表，存储过程的definer查询MYSQL8是在information_schema的view ROUTINES，只读，授权普通用户访问在8.0.20后可以给全局show_routine权限，之前要给Select权限。如果查看information_schema.ROUTINES 的DDL 会发现是多个表的join ,其中有mysql.routines， 但是访问会拒绝。
MYSQL_root@127.0.0.1 [information_schema]> select * from mysql.routines
-> ;
ERROR 3554 (HY000): Access to data dictionary table 'mysql.routines' is rejected.
当然可以以debug模式取消限制，但生产不建议, 更多参考这里。
总结：
可以通过以下方法解决在DDL中带有”DEFINER=”其他用户时报错的问题：
一种是以definer用户登录，或使用root或给授权SET_USER_ID权限的用户创建。
另一种是去除definer DDL中的语句重建:
1. 以具有管理员权限的用户登录到MySQL服务器。
2. 使用以下命令修改存储过程的定义：
USE 数据库名;
DROP PROCEDURE IF EXISTS 存储过程名;
SHOW CREATE PROCEDURE 用户名.存储过程名\G
这将显示存储过程的定义，包括”DEFINER=”语句。
3. 将显示的存储过程定义复制到一个文本编辑器中，并删除”DEFINER=”语句。
4. 重新创建存储过程
— over —
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。