MYSQL儲存過程許可權問題分析(Security_type詳解)

   與儲存過程本身有關的許可權有三類，分別是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 -h10.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:  ##看此使用者是否有執行儲存過程許可權

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: ##看此使用者是否有執行儲存過程許可權、訪問物件許可權

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: ##看此使用者是否有執行儲存過程許可權、訪問物件許可權

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 -h10.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: ##看此使用者是否有執行儲存過程許可權、訪問物件許可權

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: ##看此使用者是否有執行儲存過程許可權、訪問物件許可權

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: ##不會看此使用者是否有執行儲存過程許可權、訪問物件許可權

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: ##不會看此使用者是否有執行儲存過程許可權、訪問物件許可權

Security_type: INVOKER     ##只檢查呼叫儲存過程賬戶是否有執行許可權、訪問物件許可權