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

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

打盹的水煮肉 · “发烧请假”是面照妖镜，聊聊我以前遇到的奇葩 ...· 1 年前 ·

坏坏的凳子 · 【python】基于 ...· 2 年前 ·

任性的围巾 · unicode - How to set ...· 2 年前 ·

最近遇到一些咨询存储过程访问权限拒绝的问题，有可能是应用账户没有被赋予存储过程执行权限，也有可能是没有访问存储过程对象的权限，甚至有可能是存储过程定义安全类型默认的情况下创建存储过程的用户被删除等等都有可能导致存储过程无法被指定用户访问，这里做下大概的分析和验证，仅供遇到同样问题的人参考。
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) +----------------------------------------------------------------------------------------------------------------+ | 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 mysql> delimiter ; 存储过程 pro_test 调用场景一 场景 1 ：创建存储过程者： dbuser01 dbuser01 权限： usage on *.*,create routine ON `dbtest`.* Definer: dbuser01@10.127.% Security_type: DEFINER 场景 01 结论： dbuser01 用户存在，且 Security_type: DEFINER ， dbuser01 创建存储过程后，需要赋予账户 execute 存储过程 pro_test 的权限，否则会报无权限执行。即调用存储过程的时候会检查 Definer: ## 看此用户是否有执行存储过程权限 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) 场景 02 结论： dbuser01 用户存在，且 Security_type: DEFINER ,dbuser01 创建存储过程后，需要赋予账户 execute 存储过程 pro_test 的权限，还要被赋予存储过程里相应对象的访问权限，比如 select on dbtest.t1 权限，否则会报无权限执行。即调用存储过程的时候会检查 Definer: ## 看此用户是否有执行存储过程权限、访问对象权限 Security_type: DEFINER 存储过程 pro_test 调用场景三 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) 场景 03 结论： dbuser01 用户存在，且 Security_type: DEFINER ,dbuser01 创建存储过程后，需要赋予账户 execute 存储过程 pro_test 的权限，还要被赋予存储过程里相应对象的访问权限，比如 select on dbtest.t1 权限，否则会报无权限执行。即调用存储过程的时候会检查 Definer: ## 看此用户是否有执行存储过程权限、访问对象权限 Security_type: DEFINER 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) 场景 04 结论： dbuser01 用户存在，且 Security_type: DEFINER ,dbuser01 创建存储过程后，需要赋予账户 execute 存储过程 pro_test 的权限，还要被赋予存储过程里相应对象的访问权限，比如 select on dbtest.t1 权限，否则会报无权限执行。即调用存储过程的时候会检查 Definer: ## 看此用户是否有执行存储过程权限、访问对象权限 Security_type: DEFINER 其他用户如 dbuser02 若要调用 pro_test 存储过程，只需要被赋予 execute 权限即可，里面的对象权限无需拥有，只要创建过程的用户有执行权限、访问对象权限即可。 (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:31:29 2016)[dbtest]>grant SELECT ON `dbtest`.`t1` to 'dbuser02'@'10.127.%' ; Query OK, 0 rows affected (0.00 sec) 场景 05 结论： dbuser01 用户被删除，且 Security_type: DEFINER ,dbuser01 创建存储过程 pro_test 无法被其他账户访问。即调用存储过程的时候会检查 Definer: ## 看此用户是否有执行存储过程权限、访问对象权限 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) 场景 06 结论： dbuser01 用户被删除，且 Security_type: INVOKER ,dbuser01 创建存储过程 pro_test 可以被授予 execute 权限、访问存储过程里相应对象权限，的账户执行。即调用存储过程的时候会不会检查 Definer: ## 不会看此用户是否有执行存储过程权限、访问对象权限 Security_type: INVOKER ## 只检查调用存储过程账户是否有执行权限、访问对象权限 存储过程 pro_test 调用场景七 (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 (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 (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 场景 07 结论： dbuser01 用户存在，且 Security_type: INVOKER ,dbuser01 创建存储过程 pro_test 可以被授予 execute 权限、访问存储过程里相应对象权限的账户执行。即调用存储过程的时候会不会检查，即时是 dbuser01 是存储过程的创建者，但其没有被赋予 execute 和 select on dbtest.t1 的权限，其也无法执行 pro_test. Definer: ## 不会看此用户是否有执行存储过程权限、访问对象权限 Security_type: INVOKER ## 只检查调用存储过程账户是否有执行权限、访问对象权限

广播电视节目制作经营许可证(京) 字第1234号中国互联网协会会员