MySQL存儲(chǔ)過(guò)程的權(quán)限問(wèn)題小結(jié)

MySQL的存儲(chǔ)過(guò)程，沒錯(cuò)，看起來(lái)好生僻的使用場(chǎng)景。問(wèn)題源于一個(gè)開發(fā)同學(xué)提交了權(quán)限申請(qǐng)的工單，需要開通一些權(quán)限。

本來(lái)是一個(gè)很正常的操作，但在我來(lái)看是比較著急且緊迫的，說(shuō)來(lái)慚愧，忙著方向規(guī)劃和開發(fā)的事情，這個(gè)基礎(chǔ)的操作功能竟然給忽略了，所以看到目前的一些實(shí)現(xiàn)方式，還是希望能夠做一些細(xì)小的事情把這些重復(fù)性的工作給解放了。

當(dāng)然我決定把一些基礎(chǔ)性的工作接過(guò)來(lái)，一方面是給同事減壓，另一方面是在做一個(gè)完整的體驗(yàn)，因?yàn)楹芏嘈枨蠛屯袋c(diǎn)通過(guò)實(shí)踐是能夠很容易捕捉到重點(diǎn)的，如果我覺得不合理，那么這個(gè)過(guò)程中勢(shì)必會(huì)有一些改進(jìn)的地方。比如部署安裝，比如權(quán)限開通。數(shù)據(jù)庫(kù)的權(quán)限開通就是一個(gè)相對(duì)典型的案例，而存儲(chǔ)過(guò)程的權(quán)限開通甚至都有點(diǎn)讓人懷疑人生了。

問(wèn)題的場(chǎng)景還是很基礎(chǔ)的，開發(fā)同學(xué)需要開通一些基礎(chǔ)的權(quán)限，在標(biāo)記權(quán)限的時(shí)候聲明需要增刪改查的權(quán)限，還有DDL的權(quán)限，比如drop,alter,create等等。看到這里，我就感覺不太妥了，什么樣的操作竟然需要這么大的權(quán)限呢。

簡(jiǎn)單聲明了下立場(chǎng)，開發(fā)同學(xué)的想法是能夠方便管理，于是乎我就直接招過(guò)去了，簡(jiǎn)單溝通下，其實(shí)發(fā)現(xiàn)他們的需求場(chǎng)景還是很常規(guī)的，他們需要?jiǎng)討B(tài)創(chuàng)建一些日表，那么需要create權(quán)限在評(píng)估之后是可以給與的，而對(duì)于一般的用戶而言，create的權(quán)限是不建議開放的，主要的出發(fā)點(diǎn)就是能夠?qū)QL進(jìn)行一些基本的審核，哪怕是人工審核還是平臺(tái)審核都是一個(gè)需要的過(guò)程。所以溝通了一圈發(fā)現(xiàn)，開通的權(quán)限就可以迅速裁剪，對(duì)他們而言，修改存儲(chǔ)過(guò)程的邏輯也是需要的，因?yàn)樵谝恍┨囟ǖ膱?chǎng)景下，他們對(duì)邏輯的控制希望能夠更加靈活。

好了，基礎(chǔ)的背景介紹完了。賦予基本的表的權(quán)限，賦予存儲(chǔ)過(guò)程的權(quán)限，存儲(chǔ)過(guò)程的這個(gè)地方需要注意一個(gè)重要的點(diǎn)是SQL SECURITY，默認(rèn)創(chuàng)建是definer，如果需要開放給其他的用戶調(diào)用，則建議是設(shè)置為invoker.

所以很簡(jiǎn)單的一句：

grant execute,alter procedure on xxx.xxx to xxx@'xxxx'; 

但是很不幸的，開發(fā)同學(xué)反饋，他們通過(guò)SQLyog或者是Navicator打開的時(shí)候，竟然看不到存儲(chǔ)過(guò)程的內(nèi)容。

因?yàn)槲覀儧]有select procedure或者view procedure的權(quán)限，所以我們幾乎再無(wú)從干預(yù)了。

使用命令行的方式能夠復(fù)現(xiàn)出這個(gè)問(wèn)題:

沒有存儲(chǔ)過(guò)程的實(shí)質(zhì)性內(nèi)容。在那兒折騰了好一會(huì)，發(fā)現(xiàn)是個(gè)老問(wèn)題了，10多年前的老問(wèn)題了。

https://bugs.mysql.com/bug.php?id=20235

問(wèn)題的解決其實(shí)很簡(jiǎn)單，就是需要這樣一句:

grant select on mysql.proc to xxxx@'xxxx'即可 

所以細(xì)粒度的權(quán)限控制就是這么糾結(jié)，但是確實(shí)有效。

比如我們舉一反三一下，我們知道MySQL里的all privileges算是一個(gè)很大的權(quán)限，但是里面包含多少種權(quán)限，可能我們沒有清晰的概念。

我們就完全可以通過(guò)細(xì)粒度的權(quán)限控制來(lái)反推。

比如創(chuàng)建一個(gè)用戶，賦予all privileges的權(quán)限。

mysql> grant all privileges on test.* to 'jeanron'@'%' identified by 'jeanron100'; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 

包含的權(quán)限如下：

mysql> show grants for jeanron; 
+---------------------------------------------------+ 
| Grants for jeanron@%        | 
+---------------------------------------------------+ 
| GRANT USAGE ON *.* TO 'jeanron'@'%'    | 
| GRANT ALL PRIVILEGES ON `test`.* TO 'jeanron'@'%' | 
+---------------------------------------------------+ 
2 rows in set (0.00 sec) 

我們做一下收縮。

mysql> revoke insert on test.* from jeanron@'%'; 
Query OK, 0 rows affected (0.00 sec) 
喏，all privileges的權(quán)限就現(xiàn)出原形了。
mysql> show grants for jeanron; 
| Grants for jeanron@% 
+------------------------------------------------------------------------------------------------------ 
| GRANT USAGE ON *.* TO 'jeanron'@'%' 
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'jeanron'@'%' | 

所以在上面的問(wèn)題中，其實(shí)如果select on *.*其實(shí)已經(jīng)包含了我們需要的細(xì)粒度權(quán)限mysql.proc，如果要抽絲剝繭，基本就是這樣的套路。

補(bǔ)充:下面看下mysql創(chuàng)建存儲(chǔ)過(guò)程權(quán)限問(wèn)題

首先，在mysql存儲(chǔ)過(guò)程出現(xiàn)的同時(shí)，用戶權(quán)限也增加了5種，其中和存儲(chǔ)過(guò)程有關(guān)的權(quán)限有 三種：

• ALTER ROUTINE 編輯或刪除存儲(chǔ)過(guò)程
• CREATE ROUTINE 建立存儲(chǔ)過(guò)程
• EXECUTE 運(yùn)行存儲(chǔ)過(guò)程
  

在使用GRANT創(chuàng)建用戶的時(shí)候分配這三種權(quán)限。 存儲(chǔ)過(guò)程在運(yùn)行的時(shí)候默認(rèn)是使用建立者的權(quán)限運(yùn)行的。

需要注意的是在一個(gè)用戶擁有建立存儲(chǔ)過(guò)程的權(quán)限時(shí)，如果其沒有對(duì)于select、update或delete等權(quán)限的話，雖然操作數(shù)據(jù)的存儲(chǔ)過(guò)程可以建立，但調(diào)用存儲(chǔ)過(guò)程的話仍是無(wú)法成功的，會(huì)返回權(quán)限錯(cuò)誤，就算擁有運(yùn)行存儲(chǔ)過(guò)程的權(quán)限也一樣。所以，如果有人給你建立了一個(gè)沒有select、update、delete權(quán)限只有CREATE ROUTINE權(quán)限的用戶，罵他吧，他是故意的。

當(dāng)然這樣的用戶建立的存儲(chǔ)過(guò)程倒并不是完全不能使用，創(chuàng)建存儲(chǔ)過(guò)程中有一個(gè)特征子句可以讓存儲(chǔ)過(guò)程使用運(yùn)行者的權(quán)限，在建立存儲(chǔ)過(guò)程后只要加上SQL SECURITY INVOKER特征子句就可以了。

如下。

 CREATE PROCEDURE p() SQL SECURITY INVOKER 這樣的話就可以分配兩批人，一批給與創(chuàng)建存儲(chǔ)過(guò)程的權(quán)限，作為開發(fā)者，一批給與運(yùn)行存儲(chǔ)過(guò)程和select、update、delete權(quán)限，作為測(cè)試者。（腦筋秀逗了） 有了這種權(quán)限分配，mysql的安全性完全不需要在功能層去保護(hù)了，我通過(guò)root用戶建立的存儲(chǔ)過(guò)程，但是在功能層用一個(gè)只擁有運(yùn)行存儲(chǔ)過(guò)程權(quán)限的用戶來(lái)調(diào)用。那么，你就算從功能層上得到數(shù)據(jù)庫(kù)的用戶名和密碼，并且模擬了ip，也不能得到你想要的任何東西。 有了權(quán)限，我們可以放心大膽的使用存儲(chǔ)過(guò)程，不用擔(dān)心安全問(wèn)題了。

總結(jié)

以上所述是小編給大家介紹的MySQL存儲(chǔ)過(guò)程的權(quán)限問(wèn)題小結(jié)，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)億速云網(wǎng)站的支持！