MYSQL存儲(chǔ)過程權(quán)限問題的示例分析

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

   MYSQL數(shù)據(jù)庫(kù)權(quán)限匯總：

        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

   與存儲(chǔ)過程本身有關(guān)的權(quán)限有三類，分別是CREATE ROUTINE, ALTER ROUTINE, EXECUTE。一般來說如果用戶需要有創(chuàng)建、刪除存儲(chǔ)過程權(quán)限，需要賦予CREATE ROUTINE即可；如果有修改存儲(chǔ)過程權(quán)限，需要賦予ALTER ROUTINE即可；如果需要有調(diào)用存儲(chǔ)過程權(quán)限，需要賦予EXECUTE權(quán)限即可。

但MYSQL本身對(duì)存儲(chǔ)過程定義的語法結(jié)構(gòu)有些限制，也會(huì)對(duì)用戶調(diào)用權(quán)限做嚴(yán)格的篩選，主要與存儲(chǔ)過程定義參數(shù)：Definer 和 Security_type有關(guān)，前者是創(chuàng)建存儲(chǔ)過程的用戶，一般是表現(xiàn)形式為root@localhost等；而Security_type主要分為DEFINER | INVOKER，主要用以審核調(diào)用存儲(chǔ)過程的安全審核，如果設(shè)置為DEFINER，則創(chuàng)建存儲(chǔ)過程的用戶需要存在、并且有調(diào)用存儲(chǔ)過程權(quán)限、有訪問存儲(chǔ)過程里面對(duì)象的權(quán)限，每次調(diào)用都會(huì)對(duì)definer=root@localhost審核，看其是否存在并由相應(yīng)的權(quán)限，如果設(shè)置為INVOKER，則每次調(diào)用不會(huì)去審核definer對(duì)應(yīng)的賬戶是否存在，只需要調(diào)用存儲(chǔ)過程的用戶有執(zhí)行存儲(chǔ)過程權(quán)限，訪問存儲(chǔ)過程里面包含對(duì)象的權(quán)限即可。

    測(cè)試用例驗(yàn)證如下：

    本示例采用dbtest數(shù)據(jù)庫(kù)，以及其下面的表t1,分別利用root,dbuser01,dbuser02三個(gè)用戶

1.創(chuàng)建測(cè)試賬戶dbuser01

創(chuàng)建賬戶dbuser01，僅賦予usage,create routine權(quán)限

[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.創(chuàng)建存儲(chǔ)過程pro_test

利用dbuser01登陸dbtest數(shù)據(jù)庫(kù)，并創(chuàng)建存儲(chǔ)過程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 ;

存儲(chǔ)過程pro_test調(diào)用場(chǎng)景一

場(chǎng)景1：創(chuàng)建存儲(chǔ)過程者：dbuser01

             dbuser01權(quán)限：usage on *.*,create routine ON `dbtest`.*

                    Definer: dbuser01@10.127.%

                    Security_type: DEFINER

dbuser01調(diào)用存儲(chǔ)過程pro_test:

mysql> call pro_test;

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

dbuser01調(diào)用存儲(chǔ)過程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調(diào)用存儲(chǔ)過程：

(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'

場(chǎng)景01結(jié)論：dbuser01用戶存在，且Security_type: DEFINER，dbuser01創(chuàng)建存儲(chǔ)過程后，需要賦予賬戶execute 存儲(chǔ)過程pro_test的權(quán)限，否則會(huì)報(bào)無權(quán)限執(zhí)行。即調(diào)用存儲(chǔ)過程的時(shí)候會(huì)檢查

Definer: dbuser01@10.127.%  ##看此用戶是否有執(zhí)行存儲(chǔ)過程權(quán)限

Security_type: DEFINER

存儲(chǔ)過程pro_test調(diào)用場(chǎng)景二

場(chǎng)景02：創(chuàng)建存儲(chǔ)過程者：dbuser01

             dbuser01權(quán)限：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調(diào)用存儲(chǔ)過程pro_test:

mysql> call pro_test();

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

root調(diào)用存儲(chǔ)過程：

(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'

場(chǎng)景02結(jié)論：dbuser01用戶存在，且Security_type: DEFINER ,dbuser01創(chuàng)建存儲(chǔ)過程后，需要賦予賬戶execute 存儲(chǔ)過程pro_test的權(quán)限，還要被賦予存儲(chǔ)過程里相應(yīng)對(duì)象的訪問權(quán)限，比如select on dbtest.t1權(quán)限，否則會(huì)報(bào)無權(quán)限執(zhí)行。即調(diào)用存儲(chǔ)過程的時(shí)候會(huì)檢查Definer: dbuser01@10.127.% ##看此用戶是否有執(zhí)行存儲(chǔ)過程權(quán)限、訪問對(duì)象權(quán)限

Security_type: DEFINER

存儲(chǔ)過程pro_test調(diào)用場(chǎng)景三

場(chǎng)景03：創(chuàng)建存儲(chǔ)過程者：dbuser01

             dbuser01權(quán)限：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調(diào)用存儲(chǔ)過程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調(diào)用存儲(chǔ)過程：

(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)

場(chǎng)景03結(jié)論：dbuser01用戶存在，且Security_type: DEFINER ,dbuser01創(chuàng)建存儲(chǔ)過程后，需要賦予賬戶execute 存儲(chǔ)過程pro_test的權(quán)限，還要被賦予存儲(chǔ)過程里相應(yīng)對(duì)象的訪問權(quán)限，比如select on dbtest.t1權(quán)限，否則會(huì)報(bào)無權(quán)限執(zhí)行。即調(diào)用存儲(chǔ)過程的時(shí)候會(huì)檢查Definer: dbuser01@10.127.% ##看此用戶是否有執(zhí)行存儲(chǔ)過程權(quán)限、訪問對(duì)象權(quán)限

Security_type: DEFINER

存儲(chǔ)過程pro_test調(diào)用場(chǎng)景四

場(chǎng)景04：創(chuàng)建存儲(chǔ)過程者：dbuser02

             dbuser01權(quán)限：usage on *.*,create routine ON `dbtest`.*, execute on  procedure  dbtest.pro_test,select on dbtest.t1

                   dbuser02權(quán)限：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調(diào)用存儲(chǔ)過程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'

場(chǎng)景04結(jié)論：dbuser01用戶存在，且Security_type: DEFINER ,dbuser01創(chuàng)建存儲(chǔ)過程后，需要賦予賬戶execute 存儲(chǔ)過程pro_test的權(quán)限，還要被賦予存儲(chǔ)過程里相應(yīng)對(duì)象的訪問權(quán)限，比如select on dbtest.t1權(quán)限，否則會(huì)報(bào)無權(quán)限執(zhí)行。即調(diào)用存儲(chǔ)過程的時(shí)候會(huì)檢查Definer: dbuser01@10.127.% ##看此用戶是否有執(zhí)行存儲(chǔ)過程權(quán)限、訪問對(duì)象權(quán)限

Security_type: DEFINER

其他用戶如dbuser02若要調(diào)用pro_test存儲(chǔ)過程，只需要被賦予execute權(quán)限即可，里面的對(duì)象權(quán)限無需擁有，只要?jiǎng)?chuàng)建過程的用戶有執(zhí)行權(quán)限、訪問對(duì)象權(quán)限即可。

存儲(chǔ)過程pro_test調(diào)用場(chǎng)景五

場(chǎng)景05：刪除用戶dbuser01

    dbuser02權(quán)限：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調(diào)用存儲(chǔ)過程pro_test：

mysql> call pro_test;

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

root調(diào)用存儲(chǔ)過程：

(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)

場(chǎng)景05結(jié)論：dbuser01用戶被刪除，且Security_type: DEFINER ,dbuser01創(chuàng)建存儲(chǔ)過程pro_test無法被其他賬戶訪問。即調(diào)用存儲(chǔ)過程的時(shí)候會(huì)檢查

Definer: dbuser01@10.127.% ##看此用戶是否有執(zhí)行存儲(chǔ)過程權(quán)限、訪問對(duì)象權(quán)限

Security_type: DEFINER

存儲(chǔ)過程pro_test調(diào)用場(chǎng)景六

場(chǎng)景06：刪除用戶dbuser01

                    dbuser02權(quán)限：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調(diào)用存儲(chǔ)過程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的權(quán)限，沒有select on dbtest.t1的權(quán)限，則調(diào)用也會(huì)報(bào)錯(cuò)

mysql> call pro_test;

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

root調(diào)用存儲(chǔ)過程：

(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)

場(chǎng)景06結(jié)論：dbuser01用戶被刪除，且Security_type: INVOKER ,dbuser01創(chuàng)建存儲(chǔ)過程pro_test可以被授予execute權(quán)限、訪問存儲(chǔ)過程里相應(yīng)對(duì)象權(quán)限，的賬戶執(zhí)行。即調(diào)用存儲(chǔ)過程的時(shí)候會(huì)不會(huì)檢查

Definer: dbuser01@10.127.% ##不會(huì)看此用戶是否有執(zhí)行存儲(chǔ)過程權(quán)限、訪問對(duì)象權(quán)限

Security_type: INVOKER     ##只檢查調(diào)用存儲(chǔ)過程賬戶是否有執(zhí)行權(quán)限、訪問對(duì)象權(quán)限

存儲(chǔ)過程pro_test調(diào)用場(chǎng)景七

場(chǎng)景06：dbuser01存在，且賬戶權(quán)限被回收的情況下

                    dbuser02權(quán)限：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調(diào)用存儲(chǔ)過程pro_test:

mysql> call pro_test;

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

dbuser02調(diào)用存儲(chǔ)過程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調(diào)用存儲(chǔ)過程：

(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調(diào)用存儲(chǔ)過程pro_test:

mysql> call pro_test; 

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

dbuser02調(diào)用存儲(chǔ)過程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調(diào)用存儲(chǔ)過程：

(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)

場(chǎng)景07結(jié)論：dbuser01用戶存在，且Security_type: INVOKER ,dbuser01創(chuàng)建存儲(chǔ)過程pro_test可以被授予execute權(quán)限、訪問存儲(chǔ)過程里相應(yīng)對(duì)象權(quán)限的賬戶執(zhí)行。即調(diào)用存儲(chǔ)過程的時(shí)候會(huì)不會(huì)檢查，即時(shí)是dbuser01是存儲(chǔ)過程的創(chuàng)建者，但其沒有被賦予execute和select on dbtest.t1的權(quán)限，其也無法執(zhí)行pro_test.

Definer: dbuser01@10.127.% ##不會(huì)看此用戶是否有執(zhí)行存儲(chǔ)過程權(quán)限、訪問對(duì)象權(quán)限

Security_type: INVOKER     ##只檢查調(diào)用存儲(chǔ)過程賬戶是否有執(zhí)行權(quán)限、訪問對(duì)象權(quán)限

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“MYSQL存儲(chǔ)過程權(quán)限問題的示例分析”這篇文章對(duì)大家有幫助，同時(shí)也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來學(xué)習(xí)!