共计 9640 个字符，预计需要花费 25 分钟才能阅读完成。
这篇文章主要介绍了 MYSQL 存储过程权限问题的示例分析，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。
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 ## 只检查调用存储过程账户是否有执行权限、访问对象权限
感谢你能够认真阅读完这篇文章，希望丸趣 TV 小编分享的“MYSQL 存储过程权限问题的示例分析”这篇文章对大家有帮助，同时也希望大家多多支持丸趣 TV，关注丸趣 TV 行业资讯频道，更多相关知识等着你来学习!
