在使用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 to database ‘oto3′
这里表示b用户没有对oto3有授权存储过程的修改权限；
以b用户尝试调用一下存储过程：
procedure execution failed
1370 – execute command denied to user for routine ‘oto3.proc_cs’
这里很明显连运行权限也没有；
尝试删除原有的a用户定义的存储过程，也会报权限信息，如下：
1370 – alter routine command denied to user 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 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 存储过程权限内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！