MYSQL存储过程权限问题的举例分析
发布时间：2021-12-23 11:23:39 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍了MYSQL存储过程权限问题的示例分析，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。 MYSQL数据库权限汇总： SELECT ,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD,
|
这篇文章主要介绍了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 *************************** 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.% ##看此用户是否有执行存储过程权限、访问对象权限 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' 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 | +------+ （编辑：聊城站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐