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)??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權限比較安全；
?
通過上面的測試得出以下結論：
1、查看存儲過程權限：SELECT? #是對mysql.proc表的權限；
2、執行存儲過程權限：EXECUTE ? #是對指定數據庫的權限；
3、創建存儲過程權限：CREATE ROUTINE #是對指定數據庫的權限；
4、修改存儲過程權限：ALTER ROUTINE ?#是對指定數據庫的中自己定義的存儲過程；
5、修改別人定義的存儲過程權限：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';?
本文轉自yunlielai51CTO博客，原文鏈接：http://blog.51cto.com/4925054/1979313，如需轉載請自行聯系原作者
總結
以上是生活随笔為你收集整理的Mysql修改存储过程相关权限问题的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 远程管理，无需在机房来回穿梭
- 下一篇： MySQL 多实例详解