MYSQL存儲過程權限問題的示例分析

132次閱讀

共計 9640 個字符，預計需要花費 25 分鐘才能閱讀完成。

這篇文章主要介紹了 MYSQL 存儲過程權限問題的示例分析，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓丸趣 TV 小編帶著大家一起了解一下。

MYSQL 數據庫權限匯總：

SELECT ,INSERT, UPDATE, DELETE, CREATE,
DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW
DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION
SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER
ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

與存儲過程本身有關的權限有三類，分別是 CREATE ROUTINE, ALTER ROUTINE, EXECUTE。一般來說如果用戶需要有創建、刪除存儲過程權限，需要賦予 CREATE ROUTINE 即可；如果有修改存儲過程權限，需要賦予 ALTER ROUTINE 即可；如果需要有調用存儲過程權限，需要賦予 EXECUTE 權限即可。

但 MYSQL 本身對存儲過程定義的語法結構有些限制，也會對用戶調用權限做嚴格的篩選，主要與存儲過程定義參數：Definer 和 Security_type 有關，前者是創建存儲過程的用戶，一般是表現形式為 root@localhost 等；而 Security_type 主要分為 DEFINER | INVOKER，主要用以審核調用存儲過程的安全審核，如果設置為 DEFINER，則創建存儲過程的用戶需要存在、并且有調用存儲過程權限、有訪問存儲過程里面對象的權限，每次調用都會對 definer=root@localhost 審核，看其是否存在并由相應的權限，如果設置為 INVOKER，則每次調用不會去審核 definer 對應的賬戶是否存在，只需要調用存儲過程的用戶有執行存儲過程權限，訪問存儲過程里面包含對象的權限即可。

測試用例驗證如下：

本示例采用 dbtest 數據庫，以及其下面的表 t1, 分別利用 root,dbuser01,dbuser02 三個用戶

1. 創建測試賬戶 dbuser01

創建賬戶 dbuser01，僅賦予 usage,create routine 權限

[root@node1 ~]# mysql

(root:localhost:Wed Dec 14 14:19:05
2016)[(none)] grant USAGE on *.* to dbuser01@ 10.127.% identified by
dbuser01

Query OK, 0 rows affected (0.00 sec)

(root:localhost:Wed Dec 14 14:19:25
2016)[(none)] grant create routine on dbtest.* to dbuser01@ 10.127.%

Query OK, 0 rows affected (0.00 sec)

(root:localhost:Wed Dec 14 14:19:52
2016)[(none)] flush privileges;

Query OK, 0 rows affected (0.01 sec)

(root:localhost:Wed
Dec 14 14:20:33 2016)[(none)] show grants for dbuser01@ 10.127.%

+—————————————————————————————————————-+

| Grants for dbuser01@10.127.% |

+—————————————————————————————————————-+

| GRANT USAGE ON *.* TO dbuser01 @ 10.127.% IDENTIFIED BY PASSWORD
*0B9488E6078162E584CCE461DE11578474EBBC84 |

| GRANT CREATE ROUTINE ON `dbtest`.* TO dbuser01 @ 10.127.% |

+—————————————————————————————————————-+

2 rows in set (0.00 sec)

2. 創建存儲過程 pro_test

利用 dbuser01 登陸 dbtest 數據庫，并創建存儲過程 pro_test

[root@node4 ~]# mysql -udbuser01 -pdbuser01
-h20.127.32.121 -D dbtest

mysql delimiter //

mysql create procedure pro_test() begin
select * from t1; end;//

Query OK, 0 rows affected, 1 warning (0.00
sec)

mysql delimiter ;

存儲過程 pro_test 調用場景一

場景 1：創建存儲過程者：dbuser01

dbuser01 權限：usage on *.*,create
routine ON `dbtest`.*

Definer: dbuser01@10.127.%

Security_type: DEFINER

dbuser01 調用存儲過程 pro_test:

mysql call pro_test;

ERROR 1370 (42000): execute command denied
to user dbuser01 @ 10.127.% for routine dbtest.pro_test

dbuser01 調用存儲過程 pro_test:

(root:localhost:Wed Dec 14 14:34:28
2016)[dbtest] call pro_test();

ERROR 1370 (42000): execute command denied to user
dbuser01 @ 10.127.% for routine dbtest.pro_test

root 調用存儲過程：

(root:localhost:Wed Dec 14 14:34:28
2016)[dbtest] call pro_test();

ERROR 1370 (42000): execute command denied
to user dbuser01 @ 10.127.% for routine dbtest.pro_test

場景 01 結論：dbuser01 用戶存在，且 Security_type: DEFINER，dbuser01 創建存儲過程后，需要賦予賬戶 execute 存儲過程 pro_test 的權限，否則會報無權限執行。即調用存儲過程的時候會檢查

Definer: dbuser01@10.127.% ## 看此用戶是否有執行存儲過程權限

Security_type: DEFINER

存儲過程 pro_test 調用場景二

場景 02：創建存儲過程者：dbuser01

dbuser01 權限：usage on *.*,create
routine ON `dbtest`.*, execute on procedure dbtest.pro_test

Definer: dbuser01@10.127.%

Security_type: DEFINER

(root:localhost:Wed
Dec 14 14:34:32 2016)[dbtest] grant execute on procedure dbtest.pro_test to dbuser01 @ 10.127.%

Query OK, 0 rows affected (0.00 sec)

dbuser01 調用存儲過程 pro_test:

mysql call pro_test();

ERROR 1142 (42000): SELECT command denied to user
dbuser01 @ 10.127.32.122 for table t1

root 調用存儲過程：

(root:localhost:Wed Dec 14 14:47:03
2016)[dbtest] call pro_test();

ERROR 1142 (42000): SELECT command denied
to user dbuser01 @ 10.127.% for table t1

場景 02 結論：dbuser01 用戶存在，且 Security_type: DEFINER ,dbuser01 創建存儲過程后，需要賦予賬戶 execute 存儲過程 pro_test 的權限，還要被賦予存儲過程里相應對象的訪問權限，比如 select
on dbtest.t1 權限，否則會報無權限執行。即調用存儲過程的時候會檢查 Definer: dbuser01@10.127.% ## 看此用戶是否有執行存儲過程權限、訪問對象權限

Security_type: DEFINER

存儲過程 pro_test 調用場景三

場景 03：創建存儲過程者：dbuser01

dbuser01 權限：usage on *.*,create
routine ON `dbtest`.*, execute on procedure dbtest.pro_test,select
on dbtest.t1

Definer: dbuser01@10.127.%

Security_type: DEFINER

(root:localhost:Wed Dec 14 15:43:32 2016)[dbtest] grant select on
dbtest.t1 to dbuser01 @ 10.127.%

Query OK, 0 rows affected (0.01 sec)

dbuser01 調用存儲過程 pro_test:

mysql call pro_test();

+——+

| id |

+——+

| 3 |

| 4 |

| 1 |

+——+

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

root 調用存儲過程：

(root:localhost:Wed Dec 14 15:43:45
2016)[dbtest] call pro_test();

+——+

| id |

+——+

| 3 |

| 4 |

| 1 |

+——+

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

場景 03 結論：dbuser01 用戶存在，且 Security_type: DEFINER ,dbuser01 創建存儲過程后，需要賦予賬戶 execute 存儲過程 pro_test 的權限，還要被賦予存儲過程里相應對象的訪問權限，比如 select
on dbtest.t1 權限，否則會報無權限執行。即調用存儲過程的時候會檢查 Definer: dbuser01@10.127.% ## 看此用戶是否有執行存儲過程權限、訪問對象權限

Security_type: DEFINER

存儲過程 pro_test 調用場景四

場景 04：創建存儲過程者：dbuser02

dbuser01 權限：usage on *.*,create
routine ON `dbtest`.*, execute on procedure dbtest.pro_test,select
on dbtest.t1

dbuser02 權限：execute on procedure
dbtest.pro_test

Definer: dbuser01@10.127.%

Security_type: DEFINER

(root:localhost:Wed
Dec 14 15:44:44 2016)[dbtest] grant execute on procedure dbtest.pro_test to dbuser02 @ 10.127.% identified by dbuser02

Query OK, 0
rows affected (0.00 sec)

dbuser02 調用存儲過程 pro_test:

[root@node4 ~]# mysql -udbuser02 -pdbuser02
-h20.127.32.121 -D dbtest

mysql call pro_test();

+——+

| id |

+——+

| 3 |

| 4 |

| 1 |

+——+

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql select * from t1;

ERROR 1142 (42000): SELECT command denied to user
dbuser02 @ 10.127.32.122 for table t1

場景 04 結論：dbuser01 用戶存在，且 Security_type: DEFINER ,dbuser01 創建存儲過程后，需要賦予賬戶 execute 存儲過程 pro_test 的權限，還要被賦予存儲過程里相應對象的訪問權限，比如 select
on dbtest.t1 權限，否則會報無權限執行。即調用存儲過程的時候會檢查 Definer: dbuser01@10.127.% ## 看此用戶是否有執行存儲過程權限、訪問對象權限

Security_type: DEFINER

其他用戶如 dbuser02 若要調用 pro_test 存儲過程，只需要被賦予 execute 權限即可，里面的對象權限無需擁有，只要創建過程的用戶有執行權限、訪問對象權限即可。

存儲過程 pro_test 調用場景五

場景 05：刪除用戶 dbuser01

dbuser02 權限：execute on procedure dbtest.pro_test,select on
dbtest.t1

Definer: dbuser01@10.127.%

Security_type: DEFINER

(root:localhost:Wed Dec 14 16:11:13
2016)[dbtest] delete from mysql.user where user= dbuser01

Query OK, 1 row affected (0.00 sec)

(root:localhost:Wed Dec 14 16:11:24
2016)[dbtest] flush privileges;

Query OK, 0 rows affected (0.00 sec)

(root:localhost:Wed Dec 14 16:31:29
2016)[dbtest] grant SELECT ON `dbtest`.`t1` to dbuser02 @ 10.127.% ;

Query OK, 0 rows affected (0.00 sec)

dbuser02 調用存儲過程 pro_test：

mysql call pro_test;

ERROR 1449 (HY000): The user specified as a definer
(dbuser01 @ 10.127.%) does not exist

root 調用存儲過程：

(root:localhost:Wed Dec 14 16:11:27
2016)[dbtest] call pro_test;

ERROR 1449 (HY000): The user specified as a
definer (dbuser01 @ 10.127.%) does not exist

(root:localhost:Wed Dec 14
16:12:08 2016)[dbtest] show procedure status \G

*************************** 1.
row ***************************

Db: dbtest

Name: pro_test

Type: PROCEDURE

Definer: dbuser01@10.127.%

Modified: 2016-12-14 14:24:46

Created: 2016-12-14 14:24:46

Security_type: DEFINER

Comment:

character_set_client: utf8

collation_connection:
utf8_general_ci

Database Collation: utf8_general_ci

1 row in set (0.00 sec)

場景 05 結論：dbuser01 用戶被刪除，且 Security_type: DEFINER ,dbuser01 創建存儲過程 pro_test 無法被其他賬戶訪問。即調用存儲過程的時候會檢查

Definer: dbuser01@10.127.% ## 看此用戶是否有執行存儲過程權限、訪問對象權限

Security_type: DEFINER

存儲過程 pro_test 調用場景六

場景 06：刪除用戶 dbuser01

dbuser02 權限：execute on procedure dbtest.pro_test

Definer: dbuser01@10.127.%

Security_type: INVOKER

(root:localhost:Wed Dec 14 16:31:50
2016)[dbtest] alter procedure pro_test SQL SECURITY INVOKER ;

Query OK, 0 rows affected (0.00 sec)

dbuser02 調用存儲過程 pro_test:

mysql call pro_test;

+——+

| id |

+——+

| 3 |

| 4 |

| 1 |

+——+

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

若果 dbuser02 只有 execute 的權限，沒有 select on dbtest.t1 的權限，則調用也會報錯

mysql call pro_test;

ERROR 1142 (42000): SELECT
command denied to user dbuser02 @ 10.127.32.122 for table t1

root 調用存儲過程：

(root:localhost:Wed Dec 14 16:42:56
2016)[dbtest] call pro_test;

+——+

| id |

+——+

| 3 |

| 4 |

| 1 |

+——+

3 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

場景 06 結論：dbuser01 用戶被刪除，且 Security_type: INVOKER ,dbuser01 創建存儲過程 pro_test 可以被授予 execute 權限、訪問存儲過程里相應對象權限，的賬戶執行。即調用存儲過程的時候會不會檢查

Definer: dbuser01@10.127.% ## 不會看此用戶是否有執行存儲過程權限、訪問對象權限

Security_type: INVOKER ## 只檢查調用存儲過程賬戶是否有執行權限、訪問對象權限

存儲過程 pro_test 調用場景七

場景 06：dbuser01 存在，且賬戶權限被回收的情況下

dbuser02 權限：execute on procedure dbtest.pro_test

Definer: dbuser01@10.127.%

Security_type: INVOKER

dbuse01 僅 execute on procedure dbtest.pro_test from dbuser01

(root:localhost:Wed Dec 14
16:43:35 2016)[dbtest] grant USAGE on *.* to dbuser01@ 10.127.% identified
by dbuser01

Query OK, 0 rows affected (0.00
sec)

(root:localhost:Wed Dec 14
16:58:10 2016)[dbtest] revoke EXECUTE
ON PROCEDURE `dbtest`.`pro_test` from dbuser01 @ 10.127.% ;

Query OK, 0 rows affected (0.00
sec)

dbuser01 調用存儲過程 pro_test:

mysql call pro_test;

ERROR 1370 (42000): execute command denied
to user dbuser01 @ 10.127.% for routine dbtest.pro_test

dbuser02 調用存儲過程 pro_test:

mysql call pro_test;

+——+

| id |

+——+

| 3 |

| 4 |

| 1 |

+——+

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

root 調用存儲過程：

(root:localhost:Wed Dec 14 16:58:37
2016)[dbtest] call pro_test;

+——+

| id |

+——+

| 3 |

| 4 |

| 1 |

+——+

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

dbuser01 回收 execute on procedure dbtest.pro_test from dbuser01 以及 select on dbtest.t1

(root:localhost:Wed Dec 14
16:59:45 2016)[dbtest] revoke select
on `dbtest`.`t1` from dbuser01 @ 10.127.% ;

Query OK, 0 rows affected (0.00
sec)

dbuser01 調用存儲過程 pro_test:

mysql call pro_test;

ERROR 1370 (42000): execute command denied
to user dbuser01 @ 10.127.% for routine dbtest.pro_test

dbuser02 調用存儲過程 pro_test:

mysql call pro_test;

+——+

| id |

+——+

| 3 |

| 4 |

| 1 |

+——+

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

root 調用存儲過程：

(root:localhost:Wed Dec 14 17:01:17
2016)[dbtest] call pro_test;

+——+

| id |

+——+

| 3 |

| 4 |

| 1 |

+——+

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

場景 07 結論：dbuser01 用戶存在，且 Security_type: INVOKER ,dbuser01 創建存儲過程 pro_test 可以被授予 execute 權限、訪問存儲過程里相應對象權限的賬戶執行。即調用存儲過程的時候會不會檢查，即時是 dbuser01 是存儲過程的創建者，但其沒有被賦予 execute 和 select on dbtest.t1 的權限，其也無法執行 pro_test.

Definer: dbuser01@10.127.% ## 不會看此用戶是否有執行存儲過程權限、訪問對象權限

Security_type: INVOKER ## 只檢查調用存儲過程賬戶是否有執行權限、訪問對象權限

感謝你能夠認真閱讀完這篇文章，希望丸趣 TV 小編分享的“MYSQL 存儲過程權限問題的示例分析”這篇文章對大家有幫助，同時也希望大家多多支持丸趣 TV，關注丸趣 TV 行業資訊頻道，更多相關知識等著你來學習!

正文完