MYSQL存储过程权限问题的示例分析

这篇文章主要介绍了MYSQL存储过程权限问题的示例分析，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

   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     ##只检查调用存储过程账户是否有执行权限、访问对象权限

感谢你能够认真阅读完这篇文章，希望小编分享的“MYSQL存储过程权限问题的示例分析”这篇文章对大家有帮助，同时也希望大家多多支持亿速云，关注亿速云行业资讯频道，更多相关知识等着你来学习!