如何排查mysql存储过程的问题(Mysql修改存储过程相关权限问题)
如何排查mysql存储过程的问题
Mysql修改存储过程相关权限问题在使用mysql数据库经常都会遇到这么一个问题,其它用户定义的存储过程,现在使用另一个用户却无法修改或者删除等;正常情况下存储过程的定义者对它有修改、删除的权限;但是其它的用户就要相于的授权,不然无法查看、调用;
mysql 中使用用户A创建一个存储过程,现在想通过另一个用户B来修改A创建的存储过程;以下记录就是基于这样的情况产生的;
用户A对OTO3库的权限:
|
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的权限:
|
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操作;
|
[root@mysql ~]# mysql -h10.0.10.110 -uswper -p123456 |
查存储过程列表时就提示没有权限了:
|
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用户添加一个查看存储过程的权限:
|
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用户里查看存储过程列表:
|
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有查询权限:
|
mysql> show databases; + --------------------+ | Database | + --------------------+ | information_schema | | OTO3 | | mysql | + --------------------+ 3 rows in set (0.00 sec) |
mysql库中只有一个表:proc
|
mysql> use mysql mysql> show tables; + -----------------+ | Tables_in_mysql | + -----------------+ | proc | + -----------------+ 1 row in set (0.00 sec) |
同样也可以看到存储过程的详细信息:
|
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 |
尝试修改存储过程的配置:
|
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用户连调用存储过程的权限都没有,这里先加入执行权限:
接下来添加一个执行存储过程的权限:
|
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
那再添加一下创建添加存储过程的权限:
|
mysql> grant CREATE ROUTINE 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 , CREATE ROUTINE ON `OTO3`.* TO 'b' @ '%' | | GRANT SELECT ON `mysql`.`proc` TO 'b' @ '%' | + -----------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) |
上面添加权限后就可以创建存储过程了;
|
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'
接下来再添加一个修改的权限,也可以删除的哦;
|
mysql> grant alter ROUTINE on OTO3.* to 'b' @ '%' ; Query OK, 0 rows affected (0.01 sec) |
查看用户权限
|
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权限在哪里?通过查看用户权限原来在这里:
|
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权限看看:
|
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的权限;
|
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 变化了;再修改一下别人定义的存储过程;
|
mysql> select * from mysql. user where user = 'b' \G |
查看所有数据库,发现mysql库只有一张proc表有读取的权限,SUPER并非我所想象中那么强大;
|
mysql> show databases; + --------------------+ | Database | + --------------------+ | information_schema | | OTO3 | | mysql | + --------------------+ 3 rows in set (0.00 sec) |
仔细观看会发现执行语句:
|
mysql> select * from mysql. user where user = 'b' \G |
可以看到有 Create_routine_priv: N和 Alter_routine_priv: N 这两个明显就是对存储过程的权限嘛,能不能不用SUPER而使用这两个权限呢?
回收一下这个SUPER权限;
|
mysql> revoke super on *.* from 'b' @ '%' ; Query OK, 0 rows affected (0.01 sec) |
再添加Alter_routine_priv,Create_routine_priv
|
mysql> grant alter routine, create routine on *.* to 'b' @ '%' ; Query OK, 0 rows affected (0.00 sec) |
查看用户b权限
|
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库也有存储过程,由于这两个权限是全局的]
|
mysql> show databases; + --------------------+ | Database | + --------------------+ | information_schema | | OTO3 | | mysql | | performance_schema | | sys | | test | + --------------------+ 6 rows in set (0.00 sec) |
这两个权限更大,连系统库sys中的存储过程都能看到,甚至修改删除,非常危险;决定再次回收权限
|
create routine, alter routine; mysql> revoke create routine, alter routine on *.* from 'b' @ '%' ; |
还是使用SUPER权限比较安全;
通过上面的测试得出以下结论:
- 查看存储过程权限:SELECT #是对mysql.proc表的权限;
- 执行存储过程权限:EXECUTE #是对指定数据库的权限;
- 创建存储过程权限:CREATE ROUTINE #是对指定数据库的权限;
- 修改存储过程权限:ALTER ROUTINE #是对指定数据库的中自己定义的存储过程;
- 修改别人定义的存储过程权限:SUPER #是对全局整个mysql的权限;
简来说用户A在数据库OTO3中定义了一个存储过程,现在想用用户B来执行、修改存储过程,需要对用户B添加以下权限:
|
GRANT SELECT ON MYSQL.PROC TO 'B' ; GRANT EXECUTE , CREATE ROUTINE, ALTER ROUTINE ON `OTO3`.* TO 'B' ; GRANT SUPER ON *.* TO 'B' ; |
所以用户B的最基本的权限:
|
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里的所有者权限变更了;
|
update mysql.proc set DEFINER= 'b' @ '%' WHERE NAME = 'proc_cs' AND db= 'OTO3' ; |
到此这篇关于Mysql修改存储过程相关权限问题的文章就介绍到这了,更多相关Mysql 存储过程权限内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
原文链接:https://blog.csdn.net/qq1353424111/article/details/108719985
- laravel设置读写权限(解决laravel上传图片之后,目录有图片,但是访问不到404的问题)
- mysql权限管理详解(Mysql 用户权限管理实现)
- sql server数据库权限(SQL Server中通用数据库角色权限的处理详解)
- dedecms注册怎么给权限(织梦DedeCms系统未审核文档禁止动态浏览修改方法view.php)
- thinkphp权限认证怎么用(ThinkPHP框架结合Ajax实现用户名校验功能示例)
- dockervolume文件权限(docker volumes 文件映射方式)
- dedecmsv5.7后台路径查找(dedecms 取消服务器/主机空间目录脚本的执行权限方法[图文])
- docker 挂载目录权限(docker windows10 共享目录挂载失败的解决方案)
- sql server 管理日志可以删除吗(SQL Server查看login所授予的具体权限问题)
- 如何排查mysql存储过程的问题(Mysql修改存储过程相关权限问题)
- dedecms权限调整(dedecms当前位置导航去掉链接的实现方法)
- servu权限设置(Serv-U防溢出提权攻击解决设置方法)
- iis虚拟目录访问权限(win2008 iis7如何取消目录的可执行权限)
- django整合前端流程日志权限(使用Django开发简单接口实现文章增删改查)
- docker容器访问权限(docker 容器自定义 hosts 网络访问操作)
- sql server创建的表在哪(浅析SQL Server授予了CREATE TABLE权限但是无法创建表)
- ()
- ()
- 800壮士拼死拖住30万日军 八佰 的真实历史,誓与阵地共存亡(800壮士拼死拖住30万日军)
- 演员陈创,火于 哮天犬 ,颠峰于 福贵 ,现状却令人唏嘘(演员陈创火于哮天犬)
- 幼小衔接-20以内看图读数 写数 数的组成练习题(幼小衔接-20以内看图读数)
- 你只要花上20天记单词,英语成绩就能从57提到100(你只要花上20天记单词)
热门推荐
- docker的一些使用方法(使用Docker 安装 Jenkins的方法)
- 用js编写tab栏切换(JavaScript实现简易tab栏切换内容栏)
- sql根据条件查出两条一样的数据(SQL中遇到多条相同内容只取一条的最简单实现方法)
- angularjs使用指令(详解Angular路由动画及高阶动画函数)
- php redis配置(php+redis实现消息队列功能示例)
- 网站页面导航怎么设置css(纯CSS + 媒体查询实现网页导航效果)
- JavaScript脚本数据类型
- 腾讯云服务器控制台搭建(手把手教你搭建腾讯云服务器入门图文教程)
- 链式编程jquery(实例详解jQuery的链式编程风格)
- mysql几种连接方式(简单谈谈mysql左连接内连接)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9