Mysql修改存儲(chǔ)過(guò)程相關(guān)權(quán)限問(wèn)題
在使用mysql數(shù)據(jù)庫(kù)經(jīng)常都會(huì)遇到這么一個(gè)問(wèn)題，其它用戶定義的存儲(chǔ)過(guò)程，現(xiàn)在使用另一個(gè)用戶卻無(wú)法修改或者刪除等；正常情況下存儲(chǔ)過(guò)程的定義者對(duì)它有修改、刪除的權(quán)限；但是其它的用戶就要相于的授權(quán)，不然無(wú)法查看、調(diào)用；
mysql 中使用用戶A創(chuàng)建一個(gè)存儲(chǔ)過(guò)程,現(xiàn)在想通過(guò)另一個(gè)用戶B來(lái)修改A創(chuàng)建的存儲(chǔ)過(guò)程；以下記錄就是基于這樣的情況產(chǎn)生的；
用戶A對(duì)OTO3庫(kù)的權(quán)限：
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的權(quán)限：
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
查存儲(chǔ)過(guò)程列表時(shí)就提示沒(méi)有權(quán)限了：
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用戶添加一個(gè)查看存儲(chǔ)過(guò)程的權(quán)限：
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用戶里查看存儲(chǔ)過(guò)程列表：
mysql> select `name` from mysql.proc where db = 'OTO3' and `type` = 'PROCEDURE'; +------------------------+ | name | +------------------------+ | proc_cs | +------------------------+ 1 rows in set (0.00 sec)
此時(shí)發(fā)現(xiàn)多了一個(gè)mysql庫(kù)，但只有對(duì)mysql.proc有查詢權(quán)限：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | OTO3 | | mysql | +--------------------+ 3 rows in set (0.00 sec)
mysql庫(kù)中只有一個(gè)表：proc
mysql> use mysql mysql> show tables; +-----------------+ | Tables_in_mysql | +-----------------+ | proc | +-----------------+ 1 row in set (0.00 sec)
同樣也可以看到存儲(chǔ)過(guò)程的詳細(xì)信息：
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
嘗試修改存儲(chǔ)過(guò)程的配置：
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工具上嘗試修改存儲(chǔ)過(guò)程，在保存的時(shí)候報(bào)如下權(quán)限問(wèn)題：
1227 -Access denied;you need(at least one of)the SUPER privilege(s) for this operation
嘗試添加一個(gè)存儲(chǔ)過(guò)程，報(bào)權(quán)限信息：
1044 - Access denied for user ‘b'@'%' to database ‘OTO3'
這里表示b用戶沒(méi)有對(duì)OTO3有授權(quán)存儲(chǔ)過(guò)程的修改權(quán)限；
以B用戶嘗試調(diào)用一下存儲(chǔ)過(guò)程：
Procedure execution failed
1370 - execute command denied to user ‘b'@'%' for routine ‘OTO3.proc_cs'
這里很明顯連運(yùn)行權(quán)限也沒(méi)有；
嘗試刪除原有的a用戶定義的存儲(chǔ)過(guò)程，也會(huì)報(bào)權(quán)限信息，如下：
1370 - alter routine command denied to user ‘b'@'%' for routine ‘OTO3.proc_cs'
可以看出B用戶連調(diào)用存儲(chǔ)過(guò)程的權(quán)限都沒(méi)有，這里先加入執(zhí)行權(quán)限：
接下來(lái)添加一個(gè)執(zhí)行存儲(chǔ)過(guò)程的權(quán)限：
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)
再次執(zhí)行一下存儲(chǔ)過(guò)程，發(fā)現(xiàn)成功了；
時(shí)間: 0.080ms
Procedure executed successfully
受影響的行: 0
那再添加一下創(chuàng)建添加存儲(chǔ)過(guò)程的權(quán)限：
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)
上面添加權(quán)限后就可以創(chuàng)建存儲(chǔ)過(guò)程了;
CREATE DEFINER=`b`@`%` PROCEDURE `aaaa`() BEGIN #Routine body goes here... SELECT * from mysql.user; END
但是自己創(chuàng)建的都無(wú)法刪除；
1370 - alter routine command denied to user ‘b'@'%' for routine ‘OTO3.aaaa'
接下來(lái)再添加一個(gè)修改的權(quán)限，也可以刪除的哦；
mysql> grant alter ROUTINE on OTO3.* to 'b'@'%'; Query OK, 0 rows affected (0.01 sec)
查看用戶權(quán)限
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權(quán)限后就可以對(duì)OTO3所有的存儲(chǔ)過(guò)程有刪除權(quán)限[自己定義的增、刪、改]，別人定義的可以刪除，但是還不能修改；修改別人定義的存儲(chǔ)過(guò)程會(huì)有如下提示：
1227 - Access denied; you need (at least one of)the SUPER privilege(s) for this operation
這里說(shuō)明一下這個(gè)SUPER權(quán)限在哪里？通過(guò)查看用戶權(quán)限原來(lái)在這里：
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)
嘗試添加一下這個(gè)SUPER權(quán)限看看：
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)
不能對(duì)指定的庫(kù)執(zhí)行這個(gè)權(quán)限，因?yàn)镾UPER為全局的就是整個(gè)mysql的權(quán)限；
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)
再次檢查時(shí)會(huì)發(fā)現(xiàn) Super_priv: Y 變化了；再修改一下別人定義的存儲(chǔ)過(guò)程；
mysql> select * from mysql.user where user='b'\G
查看所有數(shù)據(jù)庫(kù)，發(fā)現(xiàn)mysql庫(kù)只有一張proc表有讀取的權(quán)限，SUPER并非我所想象中那么強(qiáng)大；
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | OTO3 | | mysql | +--------------------+ 3 rows in set (0.00 sec)
仔細(xì)觀看會(huì)發(fā)現(xiàn)執(zhí)行語(yǔ)句：
mysql> select * from mysql.user where user='b'\G
可以看到有 Create_routine_priv: N和 Alter_routine_priv: N 這兩個(gè)明顯就是對(duì)存儲(chǔ)過(guò)程的權(quán)限嘛，能不能不用SUPER而使用這兩個(gè)權(quán)限呢？
回收一下這個(gè)SUPER權(quán)限；
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權(quán)限
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)
發(fā)現(xiàn)還是報(bào)相同的權(quán)限問(wèn)題：
1227 - Access denied; you need (at least one of)the SUPER privilege(s) for this operation
執(zhí)行上面權(quán)限后發(fā)現(xiàn)，可以看到其它的系統(tǒng)庫(kù)：[例如sys庫(kù)也有存儲(chǔ)過(guò)程，由于這兩個(gè)權(quán)限是全局的]
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | OTO3 | | mysql | | performance_schema | | sys | | test | +--------------------+ 6 rows in set (0.00 sec)
這兩個(gè)權(quán)限更大，連系統(tǒng)庫(kù)sys中的存儲(chǔ)過(guò)程都能看到，甚至修改刪除，非常危險(xiǎn)；決定再次回收權(quán)限
create routine,alter routine； mysql> revoke create routine,alter routine on *.* from 'b'@'%';
還是使用SUPER權(quán)限比較安全；
通過(guò)上面的測(cè)試得出以下結(jié)論：
- 查看存儲(chǔ)過(guò)程權(quán)限：SELECT #是對(duì)mysql.proc表的權(quán)限；
- 執(zhí)行存儲(chǔ)過(guò)程權(quán)限：EXECUTE #是對(duì)指定數(shù)據(jù)庫(kù)的權(quán)限；
- 創(chuàng)建存儲(chǔ)過(guò)程權(quán)限：CREATE ROUTINE #是對(duì)指定數(shù)據(jù)庫(kù)的權(quán)限；
- 修改存儲(chǔ)過(guò)程權(quán)限：ALTER ROUTINE #是對(duì)指定數(shù)據(jù)庫(kù)的中自己定義的存儲(chǔ)過(guò)程；
- 修改別人定義的存儲(chǔ)過(guò)程權(quán)限：SUPER #是對(duì)全局整個(gè)mysql的權(quán)限；
簡(jiǎn)來(lái)說(shuō)用戶A在數(shù)據(jù)庫(kù)OTO3中定義了一個(gè)存儲(chǔ)過(guò)程，現(xiàn)在想用用戶B來(lái)執(zhí)行、修改存儲(chǔ)過(guò)程,需要對(duì)用戶B添加以下權(quán)限：
GRANT SELECT ON MYSQL.PROC TO 'B'; GRANT EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `OTO3`.* TO 'B'; GRANT SUPER ON *.* TO 'B';
所以用戶B的最基本的權(quán)限：
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)
至此，對(duì)于Mysql中以另的用戶修改其它人定義的存儲(chǔ)過(guò)程權(quán)限也就非常的顯白了；
如果不是以另一個(gè)用戶身份調(diào)用存儲(chǔ)過(guò)程，可以使用root權(quán)限修改存儲(chǔ)過(guò)程的定義者; 這樣就等于linux里的所有者權(quán)限變更了；
update mysql.proc set DEFINER='b'@'%' WHERE NAME='proc_cs' AND db='OTO3';
到此這篇關(guān)于Mysql修改存儲(chǔ)過(guò)程相關(guān)權(quán)限問(wèn)題的文章就介紹到這了,更多相關(guān)Mysql 存儲(chǔ)過(guò)程權(quán)限內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL定時(shí)任務(wù)EVENT事件的使用方法
本文主要介紹了MySQL定時(shí)任務(wù)EVENT事件的使用方法，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-05-05
Mysql InnoDB引擎的索引與存儲(chǔ)結(jié)構(gòu)詳解
這篇文章主要給大家介紹了Mysql InnoDB引擎的索引與存儲(chǔ)結(jié)構(gòu)的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，需要的朋友可以參考借鑒，下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-01-01
IDEA 鏈接Mysql數(shù)據(jù)庫(kù)并執(zhí)行查詢操作的完整代碼
這篇文章主要介紹了IDEA 鏈接Mysql數(shù)據(jù)庫(kù)并執(zhí)行查詢操作的完整代碼,代碼不難，詳細(xì)大家看完本文肯定有意向不到的收獲，感興趣的朋友跟隨小編一起看看吧2021-05-05
MySQL5綠色版windows下安裝總結(jié)（推薦）
這篇文章主要介紹了MySQL5綠色版windows下安裝總結(jié),需要的朋友可以參考下2017-03-03
join顧名思義就是連接兩張表，大致分為內(nèi)連接，外連接，右連接，左連接，自然連接，今天通過(guò)本文給大家介紹MySql中JOIN的用法詳解，感興趣的朋友一起看看吧2022-03-03
MySql通過(guò)ip地址進(jìn)行訪問(wèn)的方法
這篇文章主要介紹了MySql通過(guò)ip地址進(jìn)行訪問(wèn)的方法,首先要登錄mysql，切換數(shù)據(jù)庫(kù)然后授權(quán)，具體代碼詳情大家參考下本文2018-06-06
JDBC是指Java數(shù)據(jù)庫(kù)連接，是一種標(biāo)準(zhǔn)Java應(yīng)用編程接口（?JAVA?API），用來(lái)連接?Java?編程語(yǔ)言和廣泛的數(shù)據(jù)庫(kù)。從根本上來(lái)說(shuō)，JDBC?是一種規(guī)范，它提供了一套完整的接口，允許便攜式訪問(wèn)到底層數(shù)據(jù)庫(kù)，本篇文章我們來(lái)了解MySQL連接JDBC的五步走流程方法2022-01-01
MySQL 遷移后無(wú)法快速導(dǎo)數(shù)據(jù)問(wèn)題解決
這篇文章主要為大家介紹了MySQL 遷移后無(wú)法快速導(dǎo)數(shù)據(jù)問(wèn)題解決,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2023-10-10
數(shù)據(jù)庫(kù)市場(chǎng)需要細(xì)分,行式數(shù)據(jù)庫(kù)不再滿足所有的需求,而有很多需求需要,下面這篇文章主要給大家介紹了關(guān)于mysql單列多值存儲(chǔ)的相關(guān)資料,文中通過(guò)示例代碼介紹介紹的非常詳細(xì),需要的朋友可以參考下2022-04-04
這篇文章主要介紹了MySQL架構(gòu)設(shè)計(jì)思想詳解，文章圍繞主題展開(kāi)詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，需要的朋友可以參考一下2022-08-08