首页 » MySQL/TiDB/GoldenDB » How to alter a Procedure Created by Another User in MySQL?

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 —

打赏

,

对不起,这篇文章暂时关闭评论。