99thz桃花论族在线播放,亚洲特级片在线,在线观看中文字幕的网站

在使用mysql數(shù)據(jù)庫(kù)經(jīng)常都會(huì)遇到這么一個(gè)問題，其它用戶定義的存儲(chǔ)過程，現(xiàn)在使用另一個(gè)用戶卻無法修改或者刪除等；正常情況下存儲(chǔ)過程的定義者對(duì)它有修改、刪除的權(quán)限；但是其它的用戶就要相于的授權(quán)，不然無法查看、調(diào)用；

mysql 中使用用戶A創(chuàng)建一個(gè)存儲(chǔ)過程,現(xiàn)在想通過另一個(gè)用戶B來修改A創(chuàng)建的存儲(chǔ)過程；以下記錄就是基于這樣的情況產(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ǔ)過程列表時(shí)就提示沒有權(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ǔ)過程的權(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ǔ)過程列表：

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ǔ)過程的詳細(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ǔ)過程的配置：

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ǔ)過程，在保存的時(shí)候報(bào)如下權(quán)限問題：

1227 -Access denied;you need(at least one of)the SUPER privilege(s) for this operation

嘗試添加一個(gè)存儲(chǔ)過程，報(bào)權(quán)限信息：

1044 - Access denied for user ‘b'@'%' to database ‘OTO3'

這里表示b用戶沒有對(duì)OTO3有授權(quán)存儲(chǔ)過程的修改權(quán)限；

以B用戶嘗試調(diào)用一下存儲(chǔ)過程：

Procedure execution failed
1370 - execute command denied to user ‘b'@'%' for routine ‘OTO3.proc_cs'

這里很明顯連運(yùn)行權(quán)限也沒有；

嘗試刪除原有的a用戶定義的存儲(chǔ)過程，也會(huì)報(bào)權(quán)限信息，如下：

1370 - alter routine command denied to user ‘b'@'%' for routine ‘OTO3.proc_cs'

可以看出B用戶連調(diào)用存儲(chǔ)過程的權(quán)限都沒有，這里先加入執(zhí)行權(quán)限：

接下來添加一個(gè)執(zhí)行存儲(chǔ)過程的權(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ǔ)過程，發(fā)現(xiàn)成功了；

時(shí)間: 0.080ms
Procedure executed successfully
受影響的行: 0

那再添加一下創(chuàng)建添加存儲(chǔ)過程的權(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ǔ)過程了;

CREATE DEFINER=`b`@`%` PROCEDURE `aaaa`()
BEGIN
  #Routine body goes here...
SELECT * from mysql.user;
END

但是自己創(chuàng)建的都無法刪除；

1370 - alter routine command denied to user ‘b'@'%' for routine ‘OTO3.aaaa'

接下來再添加一個(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ǔ)過程有刪除權(quán)限[自己定義的增、刪、改]，別人定義的可以刪除，但是還不能修改；修改別人定義的存儲(chǔ)過程會(huì)有如下提示：

1227 - Access denied; you need (at least one of)the SUPER privilege(s) for this operation

這里說明一下這個(gè)SUPER權(quán)限在哪里？通過查看用戶權(quán)限原來在這里：

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ǔ)過程；

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ǔ)過程的權(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)限問題：

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ǔ)過程，由于這兩個(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ǔ)過程都能看到，甚至修改刪除，非常危險(xiǎn)；決定再次回收權(quán)限

create routine,alter routine；
mysql> revoke create routine,alter routine on *.* from 'b'@'%';

還是使用SUPER權(quán)限比較安全；

通過上面的測(cè)試得出以下結(jié)論：

查看存儲(chǔ)過程權(quán)限：SELECT #是對(duì)mysql.proc表的權(quán)限；
執(zhí)行存儲(chǔ)過程權(quán)限：EXECUTE #是對(duì)指定數(shù)據(jù)庫(kù)的權(quán)限；
創(chuàng)建存儲(chǔ)過程權(quán)限：CREATE ROUTINE #是對(duì)指定數(shù)據(jù)庫(kù)的權(quán)限；
修改存儲(chǔ)過程權(quán)限：ALTER ROUTINE #是對(duì)指定數(shù)據(jù)庫(kù)的中自己定義的存儲(chǔ)過程；
修改別人定義的存儲(chǔ)過程權(quán)限：SUPER #是對(duì)全局整個(gè)mysql的權(quán)限；

簡(jiǎn)來說用戶A在數(shù)據(jù)庫(kù)OTO3中定義了一個(gè)存儲(chǔ)過程，現(xiàn)在想用用戶B來執(zhí)行、修改存儲(chǔ)過程,需要對(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ǔ)過程權(quán)限也就非常的顯白了；

如果不是以另一個(gè)用戶身份調(diào)用存儲(chǔ)過程，可以使用root權(quán)限修改存儲(chǔ)過程的定義者; 這樣就等于linux里的所有者權(quán)限變更了；

update mysql.proc set DEFINER='b'@'%' WHERE NAME='proc_cs' AND db='OTO3';

到此這篇關(guān)于Mysql修改存儲(chǔ)過程相關(guān)權(quán)限問題的文章就介紹到這了,更多相關(guān)Mysql 存儲(chǔ)過程權(quán)限內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

淺談MySQL user權(quán)限表
Mysql 用戶權(quán)限管理實(shí)現(xiàn)
詳解MySQL 用戶權(quán)限管理
MySQL 權(quán)限控制細(xì)節(jié)分析
MySQL 權(quán)限控制詳解
mysql創(chuàng)建用戶并賦予用戶權(quán)限詳細(xì)操作教程
MySQL8.0設(shè)置遠(yuǎn)程訪問權(quán)限的方法
MySQL之權(quán)限以及設(shè)計(jì)數(shù)據(jù)庫(kù)案例講解

標(biāo)簽：鹽城沈陽(yáng) 拉薩沈陽(yáng) 移動(dòng) 珠海徐州黔東

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《Mysql修改存儲(chǔ)過程相關(guān)權(quán)限問題》，本文關(guān)鍵詞 Mysql,修改,存儲(chǔ),過程,相關(guān),；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。