ITPub博客

MYSQL存储过程权限问题分析(Security_type详解)

原创 MySQL 作者:yepkeepmoving 时间:2016-12-14 17:45:27 0 删除 编辑

 最近遇到一些咨询存储过程访问权限拒绝的问题,有可能是应用账户没有被赋予存储过程执行权限,也有可能是没有访问存储过程对象的权限,甚至有可能是存储过程定义安全类型默认的情况下创建存储过程的用户被删除等等都有可能导致存储过程无法被指定用户访问,这里做下大概的分析和验证,仅供遇到同样问题的人参考。
   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 -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: DEFINERdbuser01创建存储过程后,需要赋予账户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 -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: 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调用场景七


场景06dbuser01存在,且账户权限被回收的情况下

                    dbuser02权限:execute on procedure dbtest.pro_test

                    Definer: dbuser01@10.127.%

                    Security_type: INVOKER

 

dbuse01execute 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是存储过程的创建者,但其没有被赋予executeselect on dbtest.t1的权限,其也无法执行pro_test.

Definer: dbuser01@10.127.% ##不会看此用户是否有执行存储过程权限、访问对象权限

Security_type: INVOKER     ##只检查调用存储过程账户是否有执行权限、访问对象权限



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27067062/viewspace-2130598/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Raid详解
请登录后发表评论 登录
全部评论

注册时间:2014-12-08

  • 博文量
    76
  • 访问量
    93112