溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這期內容當中小編將會給大家帶來有關怎么對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 -h20.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數據庫中存儲過程的相關權限進行修改了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關知識，歡迎關注億速云行業資訊頻道。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
