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