开心学习
当前位置：数据库 > > 正文

如何排查mysql存储过程的问题（Mysql修改存储过程相关权限问题）

时间：2022-01-20 00:32:30类别：数据库

如何排查mysql存储过程的问题

Mysql修改存储过程相关权限问题

在使用mysql数据库经常都会遇到这么一个问题，其它用户定义的存储过程，现在使用另一个用户却无法修改或者删除等；正常情况下存储过程的定义者对它有修改、删除的权限；但是其它的用户就要相于的授权，不然无法查看、调用；

mysql 中使用用户A创建一个存储过程,现在想通过另一个用户B来修改A创建的存储过程；以下记录就是基于这样的情况产生的；

如何排查mysql存储过程的问题（Mysql修改存储过程相关权限问题）

用户A对OTO3库的权限：

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
    		•
  • mysql> show grants for 'a'@'%';
  • +---------------------------------------------------+
  • | Grants for a@%               |
  • +---------------------------------------------------+
  • | GRANT USAGE ON *.* TO 'a'@'%'        |
  • | GRANT ALL PRIVILEGES ON `OTO3`.* TO 'a'@'%' |
  • +---------------------------------------------------+
  • 2 rows in set (0.00 sec)
    		•

    • 用户B的权限：

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
    		•
  • mysql> show grants for 'swper'@'%';
  • +----------------------------------------------------------------------+
  • | Grants for swper@%                          |
  • +----------------------------------------------------------------------+
  • | GRANT USAGE ON *.* TO 'swper'@'%'                  |
  • | GRANT SELECT, UPDATE, DELETE, DROP, ALTER ON `OTO3`.* TO 'swper'@'%' |
  • +----------------------------------------------------------------------+
  • 2 rows in set (0.00 sec)
    		•

    • 以用户B的身份登陆Mysql操作；

  • ?
  • 1
    		•
  • [root@mysql ~]# mysql -h10.0.10.110 -uswper -p123456
    		•

    • 查存储过程列表时就提示没有权限了：

  • ?
  • 1
  • 2
    		•
  • mysql> select `name` from mysql.proc where db = 'OTO3' and `type` = 'PROCEDURE';
  • ERROR 1142 (42000): SELECT command denied to user 'swper'@'mysql' for table 'proc'
    		•

    • 以root身份给B用户添加一个查看存储过程的权限：

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
    		•
  • mysql> grant select on mysql.proc to 'swper'@'%';
  • Query OK, 0 rows affected (0.00 sec)
  •  
  • mysql> show grants for 'swper'@'%';
  • +----------------------------------------------------------------------+
  • | Grants for swper@%                          |
  • +----------------------------------------------------------------------+
  • | GRANT USAGE ON *.* TO 'swper'@'%'                  |
  • | GRANT SELECT, UPDATE, DELETE, DROP, ALTER ON `OTO3`.* TO 'swper'@'%' |
  • | GRANT SELECT ON `mysql`.`proc` TO 'swper'@'%'            |
  • +----------------------------------------------------------------------+
  • 3 rows in set (0.00 sec)
    		•

    • 再回到B用户里查看存储过程列表：

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
    		•
  • mysql> select `name` from mysql.proc where db = 'OTO3' and `type` = 'PROCEDURE';
  • +------------------------+
  • | name          |
  • +------------------------+
  • | proc_cs        |
  • +------------------------+
  • 1 rows in set (0.00 sec)
    		•

    • 此时发现多了一个mysql库，但只有对mysql.proc有查询权限：

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
    		•
  • mysql> show databases;
  • +--------------------+
  • | Database      |
  • +--------------------+
  • | information_schema |
  • | OTO3        |
  • | mysql       |
  • +--------------------+
  • 3 rows in set (0.00 sec)
    		•

    • mysql库中只有一个表：proc

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
    		•
  • mysql> use mysql
  • mysql> show tables;
  • +-----------------+
  • | Tables_in_mysql |
  • +-----------------+
  • | proc      |
  • +-----------------+
  • 1 row in set (0.00 sec)
    		•

    • 同样也可以看到存储过程的详细信息：

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
    		•
  • mysql> show create procedure proc_cs\G
  • *************************** 1. row ***************************
  •       Procedure: proc_cs
  •       sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  •   Create Procedure: CREATE DEFINER=`a`@`%` PROCEDURE `proc_cs`()
  • BEGIN
    		•

    • 尝试修改存储过程的配置：

  • ?
  • 1
  • 2
    		•
  • mysql> ALTER PROCEDURE proc_cs  MODIFIES SQL DATA SQL SECURITY INVOKER;
  • ERROR 1370 (42000): alter routine command denied to user 'b'@'%' for routine 'OTO3.proc_cs'
    		•

    • 为了方便查看在Navicat工具上尝试修改存储过程，在保存的时候报如下权限问题：

    1227 -Access denied;you need(at least one of)the SUPER privilege(s) for this operation

    尝试添加一个存储过程，报权限信息：

    1044 - Access denied for user ‘b'@'%' to database ‘OTO3'

    这里表示b用户没有对OTO3有授权存储过程的修改权限；

    以B用户尝试调用一下存储过程：

    Procedure execution failed
    1370 - execute command denied to user ‘b'@'%' for routine ‘OTO3.proc_cs'

    这里很明显连运行权限也没有；

    尝试删除原有的a用户定义的存储过程，也会报权限信息，如下：

    1370 - alter routine command denied to user ‘b'@'%' for routine ‘OTO3.proc_cs'

    可以看出B用户连调用存储过程的权限都没有，这里先加入执行权限：

    接下来添加一个执行存储过程的权限：

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
    		•
  • mysql> grant execute on OTO3.* to 'b'@'%';
  • Query OK, 0 rows affected (0.00 sec)
  •  
  • mysql> show grants for 'b'@'%';
  • +-------------------------------------------------------------------------------+
  • | Grants for b@%                              |
  • +-------------------------------------------------------------------------------+
  • | GRANT USAGE ON *.* TO 'b'@'%'                       |
  • | GRANT SELECT, UPDATE, DELETE, DROP, ALTER, EXECUTE ON `OTO3`.* TO 'b'@'%' |
  • | GRANT SELECT ON `mysql`.`proc` TO 'b'@'%'                 |
  • +-------------------------------------------------------------------------------+
  • 3 rows in set (0.00 sec)
    		•

    • 再次执行一下存储过程，发现成功了；

    时间: 0.080ms
    Procedure executed successfully
    受影响的行: 0

    那再添加一下创建添加存储过程的权限：

  • ?
  • 1
  • 2
    		•
  • mysql> grant CREATE ROUTINE on OTO3.* to 'b'@'%';
  • Query OK, 0 rows affected (0.00 sec)
    		•
  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
    		•
  • mysql> show grants for 'b'@'%';
  • +-----------------------------------------------------------------------------------------------+
  • | Grants for b@%                                      |
  • +-----------------------------------------------------------------------------------------------+
  • | GRANT USAGE ON *.* TO 'b'@'%'                              |
  • | GRANT SELECT, UPDATE, DELETE, DROP, ALTER, EXECUTE, CREATE ROUTINE ON `OTO3`.* TO 'b'@'%' |
  • | GRANT SELECT ON `mysql`.`proc` TO 'b'@'%'                         |
  • +-----------------------------------------------------------------------------------------------+
  • 3 rows in set (0.00 sec)
    		•

    • 上面添加权限后就可以创建存储过程了;

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
    		•
  • CREATE DEFINER=`b`@`%` PROCEDURE `aaaa`()
  • BEGIN
  •   #Routine body goes here...
  • SELECT * from mysql.user;
  • END
    		•

    • 但是自己创建的都无法删除；

    1370 - alter routine command denied to user ‘b'@'%' for routine ‘OTO3.aaaa'

    接下来再添加一个修改的权限，也可以删除的哦；

  • ?
  • 1
  • 2
    		•
  • mysql> grant alter ROUTINE on OTO3.* to 'b'@'%';
  • Query OK, 0 rows affected (0.01 sec)
    		•

    • 查看用户权限

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
    		•
  • mysql> show grants for 'b'@'%';
  • +--------------------------------------------------------------------------------------------------------------+
  • | Grants for b@%                                              |
  • +--------------------------------------------------------------------------------------------------------------+
  • | GRANT USAGE ON *.* TO 'b'@'%'                                      |
  • | GRANT SELECT, UPDATE, DELETE, DROP, ALTER, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `OTO3`.* TO 'b'@'%' |
  • | GRANT SELECT ON `mysql`.`proc` TO 'b'@'%'                                |
  • +--------------------------------------------------------------------------------------------------------------+
  • 3 rows in set (0.00 sec)
    		•

    • 上面添加完alter ROUTINE权限后就可以对OTO3所有的存储过程有删除权限[自己定义的增、删、改]，别人定义的可以删除，但是还不能修改；修改别人定义的存储过程会有如下提示：

    1227 - Access denied; you need (at least one of)the SUPER privilege(s) for this operation

    这里说明一下这个SUPER权限在哪里？通过查看用户权限原来在这里：

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
    		•
  • mysql> select * from mysql.user where user='b'\G
  • *************************** 1. row ***************************
  •          Host: %
  •          User: b
  •       Select_priv: N
  •       Insert_priv: N
  •       Update_priv: N
  •       Delete_priv: N
  •       Create_priv: N
  •        Drop_priv: N
  •       Reload_priv: N
  •      Shutdown_priv: N
  •      Process_priv: N
  •        File_priv: N
  •       Grant_priv: N
  •     References_priv: N
  •       Index_priv: N
  •       Alter_priv: N
  •      Show_db_priv: N
  •       Super_priv: N
  •  Create_tmp_table_priv: N
  •    Lock_tables_priv: N
  •      Execute_priv: N
  •     Repl_slave_priv: N
  •    Repl_client_priv: N
  •    Create_view_priv: N
  •     Show_view_priv: N
  •   Create_routine_priv: N
  •   Alter_routine_priv: N
  •    Create_user_priv: N
  •       Event_priv: N
  •      Trigger_priv: N
  • Create_tablespace_priv: N
  •        ssl_type:
  •       ssl_cipher:
  •       x509_issuer:
  •      x509_subject:
  •      max_questions: 0
  •       max_updates: 0
  •     max_connections: 0
  •  max_user_connections: 0
  •         plugin: mysql_native_password
  •  authentication_string: *CCB4F88E945E0E14F9BEB093EB797BB0BDBFA175
  •    password_expired: N
  •  password_last_changed: 2017-03-06 11:37:35
  •    password_lifetime: NULL
  •     account_locked: N
  • 1 row in set (0.00 sec)
    		•

    • 尝试添加一下这个SUPER权限看看：

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
    		•
  • mysql> grant SUPER on OTO3.* to 'b'@'%';
  • ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
  •  
  • mysql> grant SUPER on *.* to 'b'@'%';
  • Query OK, 0 rows affected (0.00 sec)
    		•

    • 不能对指定的库执行这个权限，因为SUPER为全局的就是整个mysql的权限；

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
    		•
  • mysql> show grants for 'swper'@'%';
  • +--------------------------------------------------------------------------------------------------------------+
  • | Grants for swper@%                                              |
  • +--------------------------------------------------------------------------------------------------------------+
  • | GRANT SUPER ON *.* TO 'swper'@'%'                                      |
  • | GRANT SELECT, UPDATE, DELETE, DROP, ALTER, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `OTO3`.* TO 'swper'@'%' |
  • | GRANT SELECT ON `mysql`.`proc` TO 'swper'@'%'                                |
  • +--------------------------------------------------------------------------------------------------------------+
  • 3 rows in set (0.00 sec)
    		•

    • 再次检查时会发现 Super_priv: Y 变化了；再修改一下别人定义的存储过程；

  • ?
  • 1
    		•
  • mysql> select * from mysql.user where user='b'\G
    		•

    • 查看所有数据库，发现mysql库只有一张proc表有读取的权限，SUPER并非我所想象中那么强大；

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
    		•
  • mysql> show databases;
  • +--------------------+
  • | Database      |
  • +--------------------+
  • | information_schema |
  • | OTO3        |
  • | mysql       |
  • +--------------------+
  • 3 rows in set (0.00 sec)
    		•

    • 仔细观看会发现执行语句：

  • ?
  • 1
    		•
  • mysql> select * from mysql.user where user='b'\G
    		•

    • 可以看到有 Create_routine_priv: N和 Alter_routine_priv: N 这两个明显就是对存储过程的权限嘛，能不能不用SUPER而使用这两个权限呢？

    回收一下这个SUPER权限；

  • ?
  • 1
  • 2
    		•
  • mysql> revoke super on *.* from 'b'@'%';
  • Query OK, 0 rows affected (0.01 sec)
    		•

    • 再添加Alter_routine_priv，Create_routine_priv

  • ?
  • 1
  • 2
    		•
  • mysql> grant alter routine,create routine on *.* to 'b'@'%';
  • Query OK, 0 rows affected (0.00 sec)
    		•

    • 查看用户b权限

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
    		•
  • mysql> show grants for 'b'@'%';
  • +--------------------------------------------------------------------------------------------------------------+
  • | Grants for b@%                                              |
  • +--------------------------------------------------------------------------------------------------------------+
  • | GRANT CREATE ROUTINE, ALTER ROUTINE ON *.* TO 'b'@'%'                          |
  • | GRANT SELECT, UPDATE, DELETE, DROP, ALTER, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `OTO3`.* TO 'b'@'%' |
  • | GRANT SELECT ON `mysql`.`proc` TO 'b'@'%'                                |
  • +--------------------------------------------------------------------------------------------------------------+
  • 3 rows in set (0.00 sec)
    		•

    • 发现还是报相同的权限问题：

    1227 - Access denied; you need (at least one of)the SUPER privilege(s) for this operation

    执行上面权限后发现，可以看到其它的系统库：[例如sys库也有存储过程，由于这两个权限是全局的]

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
    		•
  • mysql> show databases;
  • +--------------------+
  • | Database      |
  • +--------------------+
  • | information_schema |
  • | OTO3        |
  • | mysql       |
  • | performance_schema |
  • | sys        |
  • | test        |
  • +--------------------+
  • 6 rows in set (0.00 sec)
    		•

    • 这两个权限更大，连系统库sys中的存储过程都能看到，甚至修改删除，非常危险；决定再次回收权限

  • ?
  • 1
  • 2
    		•
  • create routine,alter routine；
  • mysql> revoke create routine,alter routine on *.* from 'b'@'%';
    		•

    • 还是使用SUPER权限比较安全；

    通过上面的测试得出以下结论：

    简来说用户A在数据库OTO3中定义了一个存储过程，现在想用用户B来执行、修改存储过程,需要对用户B添加以下权限：

  • ?
  • 1
  • 2
  • 3
    		•
  • GRANT SELECT ON MYSQL.PROC TO 'B';
  • GRANT EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `OTO3`.* TO 'B';
  • GRANT SUPER ON *.* TO 'B';
    		•

    • 所以用户B的最基本的权限：

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
    		•
  • mysql> show grants for 'b'@'%';
  • +----------------------------------------------------------------------------------------+
  • | Grants for b@%                                   |
  • +----------------------------------------------------------------------------------------+
  • | GRANT SUPER ON *.* TO 'b'@'%'                           |
  • | GRANT SELECT, ALTER, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `OTO3`.* TO 'b'@'%' |
  • | GRANT SELECT ON `mysql`.`proc` TO 'b'@'%'                     |
  • +----------------------------------------------------------------------------------------+
  • 3 rows in set (0.00 sec)
    		•

    • 至此，对于Mysql中以另的用户修改其它人定义的存储过程权限也就非常的显白了；

    如果不是以另一个用户身份调用存储过程，可以使用root权限修改存储过程的定义者; 这样就等于linux里的所有者权限变更了；

  • ?
  • 1
    		•
  • update mysql.proc set DEFINER='b'@'%' WHERE NAME='proc_cs' AND db='OTO3';
    		•

    • 到此这篇关于Mysql修改存储过程相关权限问题的文章就介绍到这了,更多相关Mysql 存储过程权限内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网！

    原文链接：https://blog.csdn.net/qq1353424111/article/details/108719985

    标签：
    权限 mysql 存储过程
    上一篇下一篇

    猜您喜欢

    热门推荐

    © 2021 开心学习