Mysql修改存储过程相关权限问题

吾爱主题阅读：144 2024-04-02 07:58:07 评论：0

在使用mysql数据库经常都会遇到这么一个问题，其它用户定义的存储过程，现在使用另一个用户却无法修改或者删除等；正常情况下存储过程的定义者对它有修改、删除的权限；但是其它的用户就要相于的授权，不然无法查看、调用；

mysql 中使用用户A创建一个存储过程,现在想通过另一个用户B来修改A创建的存储过程；以下记录就是基于这样的情况产生的；

用户A对OTO3库的权限：

1 2 3 4 5 6 7 8 mysql> show grants for 'a' @ '%' ; + ---------------------------------------------------+ | Grants for a@% | + ---------------------------------------------------+ | GRANT USAGE ON *.* TO 'a' @ '%' | | GRANT ALL PRIVILEGES ON `OTO3`.* TO 'a' @ '%' | + ---------------------------------------------------+ 2 rows in set (0.00 sec)

用户B的权限：

1 2 3 4 5 6 7 8 mysql> show grants for 'swper' @ '%' ; + ----------------------------------------------------------------------+ | Grants for swper@% | + ----------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'swper' @ '%' | | GRANT SELECT , UPDATE , DELETE , DROP , ALTER ON `OTO3`.* TO 'swper' @ '%' | + ----------------------------------------------------------------------+ 2 rows in set (0.00 sec)

以用户B的身份登陆Mysql操作；

1	`[root@mysql ~]# mysql -h10.0.10.110 -uswper -p123456`

查存储过程列表时就提示没有权限了：

1 2	`mysql>` `select` ` `name` ` `from` `mysql.proc` `where` `db =` `'OTO3'` `and` `type` = `'PROCEDURE'` `;` `ERROR 1142 (42000):` `SELECT` `command denied` `to` `user` `'swper'` `@` `'mysql'` `for` `table` `'proc'`

以root身份给B用户添加一个查看存储过程的权限：

1 2 3 4 5 6 7 8 9 10 11 12 mysql> grant select on mysql.proc to 'swper' @ '%' ; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'swper' @ '%' ; + ----------------------------------------------------------------------+ | Grants for swper@% | + ----------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'swper' @ '%' | | GRANT SELECT , UPDATE , DELETE , DROP , ALTER ON `OTO3`.* TO 'swper' @ '%' | | GRANT SELECT ON `mysql`.`proc` TO 'swper' @ '%' | + ----------------------------------------------------------------------+ 3 rows in set (0.00 sec)

再回到B用户里查看存储过程列表：

1 2 3 4 5 6 7 mysql> select ` name ` from mysql.proc where db = 'OTO3' and `type` = 'PROCEDURE' ; + ------------------------+ | name | + ------------------------+ | proc_cs | + ------------------------+ 1 rows in set (0.00 sec)

此时发现多了一个mysql库，但只有对mysql.proc有查询权限：

1 2 3 4 5 6 7 8 9 mysql> show databases; + --------------------+ | Database | + --------------------+ | information_schema | | OTO3 | | mysql | + --------------------+ 3 rows in set (0.00 sec)

mysql库中只有一个表：proc

1 2 3 4 5 6 7 8 mysql> use mysql mysql> show tables; + -----------------+ | Tables_in_mysql | + -----------------+ | proc | + -----------------+ 1 row in set (0.00 sec)

同样也可以看到存储过程的详细信息：

1 2 3 4 5 6 mysql> show create procedure proc_cs\G *************************** 1. row *************************** Procedure : proc_cs sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Create Procedure : CREATE DEFINER=`a`@`%` PROCEDURE `proc_cs`() BEGIN

尝试修改存储过程的配置：

1 2	`mysql>` `ALTER` `PROCEDURE` `proc_cs MODIFIES SQL DATA SQL SECURITY INVOKER;` `ERROR 1370 (42000):` `alter` `routine command denied` `to` `user` `'b'` `@` `'%'` `for` `routine` `'OTO3.proc_cs'`

为了方便查看在Navicat工具上尝试修改存储过程，在保存的时候报如下权限问题：

1227 -Access denied;you need(at least one of)the SUPER privilege(s) for this operation

尝试添加一个存储过程，报权限信息：

1044 - Access denied for user ‘b'@'%' to database ‘OTO3'

这里表示b用户没有对OTO3有授权存储过程的修改权限；

以B用户尝试调用一下存储过程：

Procedure execution failed
1370 - execute command denied to user ‘b'@'%' for routine ‘OTO3.proc_cs'

这里很明显连运行权限也没有；

尝试删除原有的a用户定义的存储过程，也会报权限信息，如下：

1370 - alter routine command denied to user ‘b'@'%' for routine ‘OTO3.proc_cs'

可以看出B用户连调用存储过程的权限都没有，这里先加入执行权限：

接下来添加一个执行存储过程的权限：

1 2 3 4 5 6 7 8 9 10 11 12 mysql> grant execute on OTO3.* to 'b' @ '%' ; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'b' @ '%' ; + -------------------------------------------------------------------------------+ | Grants for b@% | + -------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'b' @ '%' | | GRANT SELECT , UPDATE , DELETE , DROP , ALTER , EXECUTE ON `OTO3`.* TO 'b' @ '%' | | GRANT SELECT ON `mysql`.`proc` TO 'b' @ '%' | + -------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)

再次执行一下存储过程，发现成功了；

时间: 0.080ms
Procedure executed successfully
受影响的行: 0

那再添加一下创建添加存储过程的权限：

1 2	`mysql>` `grant` `CREATE` `ROUTINE` `on` `OTO3.*` `to` `'b'` `@` `'%'` `;` `Query OK, 0` `rows` `affected (0.00 sec)`

1 2 3 4 5 6 7 8 9 mysql> show grants for 'b' @ '%' ; + -----------------------------------------------------------------------------------------------+ | Grants for b@% | + -----------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'b' @ '%' | | GRANT SELECT , UPDATE , DELETE , DROP , ALTER , EXECUTE , CREATE ROUTINE ON `OTO3`.* TO 'b' @ '%' | | GRANT SELECT ON `mysql`.`proc` TO 'b' @ '%' | + -----------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)

上面添加权限后就可以创建存储过程了;

1 2 3 4 5 CREATE DEFINER=`b`@`%` PROCEDURE `aaaa`() BEGIN #Routine body goes here... SELECT * from mysql. user ; END

但是自己创建的都无法删除；

1370 - alter routine command denied to user ‘b'@'%' for routine ‘OTO3.aaaa'

接下来再添加一个修改的权限，也可以删除的哦；

1 2	`mysql>` `grant` `alter` `ROUTINE` `on` `OTO3.*` `to` `'b'` `@` `'%'` `;` `Query OK, 0` `rows` `affected (0.01 sec)`

查看用户权限

1 2 3 4 5 6 7 8 9 mysql> show grants for 'b' @ '%' ; + --------------------------------------------------------------------------------------------------------------+ | Grants for b@% | + --------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'b' @ '%' | | GRANT SELECT , UPDATE , DELETE , DROP , ALTER , EXECUTE , CREATE ROUTINE, ALTER ROUTINE ON `OTO3`.* TO 'b' @ '%' | | GRANT SELECT ON `mysql`.`proc` TO 'b' @ '%' | + --------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)

上面添加完alter ROUTINE权限后就可以对OTO3所有的存储过程有删除权限[自己定义的增、删、改]，别人定义的可以删除，但是还不能修改；修改别人定义的存储过程会有如下提示：

1227 - Access denied; you need (at least one of)the SUPER privilege(s) for this operation

这里说明一下这个SUPER权限在哪里？通过查看用户权限原来在这里：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 mysql> select * from mysql. user where user = 'b' \G *************************** 1. row *************************** Host: % User : b Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *CCB4F88E945E0E14F9BEB093EB797BB0BDBFA175 password_expired: N password_last_changed: 2017-03-06 11:37:35 password_lifetime: NULL account_locked: N 1 row in set (0.00 sec)

尝试添加一下这个SUPER权限看看：

1 2 3 4 5 mysql> grant SUPER on OTO3.* to 'b' @ '%' ; ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES mysql> grant SUPER on *.* to 'b' @ '%' ; Query OK, 0 rows affected (0.00 sec)

不能对指定的库执行这个权限，因为SUPER为全局的就是整个mysql的权限；

1 2 3 4 5 6 7 8 9 mysql> show grants for 'swper' @ '%' ; + --------------------------------------------------------------------------------------------------------------+ | Grants for swper@% | + --------------------------------------------------------------------------------------------------------------+ | GRANT SUPER ON *.* TO 'swper' @ '%' | | GRANT SELECT , UPDATE , DELETE , DROP , ALTER , EXECUTE , CREATE ROUTINE, ALTER ROUTINE ON `OTO3`.* TO 'swper' @ '%' | | GRANT SELECT ON `mysql`.`proc` TO 'swper' @ '%' | + --------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)

再次检查时会发现 Super_priv: Y 变化了；再修改一下别人定义的存储过程；

1	`mysql>` `select` `*` `from` `mysql.` `user` `where` `user` `=` `'b'` `\G`

查看所有数据库，发现mysql库只有一张proc表有读取的权限，SUPER并非我所想象中那么强大；

仔细观看会发现执行语句：

1	`mysql>` `select` `*` `from` `mysql.` `user` `where` `user` `=` `'b'` `\G`

可以看到有 Create_routine_priv: N和 Alter_routine_priv: N 这两个明显就是对存储过程的权限嘛，能不能不用SUPER而使用这两个权限呢？

回收一下这个SUPER权限；

1 2	`mysql>` `revoke` `super` `on` `.` `from` `'b'` `@` `'%'` `;` `Query OK, 0` `rows` `affected (0.01 sec)`

再添加Alter_routine_priv，Create_routine_priv

1 2	`mysql>` `grant` `alter` `routine,` `create` `routine` `on` `.` `to` `'b'` `@` `'%'` `;` `Query OK, 0` `rows` `affected (0.00 sec)`

查看用户b权限

1 2 3 4 5 6 7 8 9 mysql> show grants for 'b' @ '%' ; + --------------------------------------------------------------------------------------------------------------+ | Grants for b@% | + --------------------------------------------------------------------------------------------------------------+ | GRANT CREATE ROUTINE, ALTER ROUTINE ON *.* TO 'b' @ '%' | | GRANT SELECT , UPDATE , DELETE , DROP , ALTER , EXECUTE , CREATE ROUTINE, ALTER ROUTINE ON `OTO3`.* TO 'b' @ '%' | | GRANT SELECT ON `mysql`.`proc` TO 'b' @ '%' | + --------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)

发现还是报相同的权限问题：

1227 - Access denied; you need (at least one of)the SUPER privilege(s) for this operation

执行上面权限后发现，可以看到其它的系统库：[例如sys库也有存储过程，由于这两个权限是全局的]

1 2 3 4 5 6 7 8 9 10 11 12 mysql> show databases; + --------------------+ | Database | + --------------------+ | information_schema | | OTO3 | | mysql | | performance_schema | | sys | | test | + --------------------+ 6 rows in set (0.00 sec)

这两个权限更大，连系统库sys中的存储过程都能看到，甚至修改删除，非常危险；决定再次回收权限

1 2	`create` `routine,` `alter` `routine；` `mysql>` `revoke` `create` `routine,` `alter` `routine` `on` `.` `from` `'b'` `@` `'%'` `;`

还是使用SUPER权限比较安全；

通过上面的测试得出以下结论：

查看存储过程权限：SELECT #是对mysql.proc表的权限；
执行存储过程权限：EXECUTE #是对指定数据库的权限；
创建存储过程权限：CREATE ROUTINE #是对指定数据库的权限；
修改存储过程权限：ALTER ROUTINE #是对指定数据库的中自己定义的存储过程；
修改别人定义的存储过程权限：SUPER #是对全局整个mysql的权限；

简来说用户A在数据库OTO3中定义了一个存储过程，现在想用用户B来执行、修改存储过程,需要对用户B添加以下权限：

1 2 3 GRANT SELECT ON MYSQL.PROC TO 'B' ; GRANT EXECUTE , CREATE ROUTINE, ALTER ROUTINE ON `OTO3`.* TO 'B' ; GRANT SUPER ON *.* TO 'B' ;

所以用户B的最基本的权限：

1 2 3 4 5 6 7 8 9 mysql> show grants for 'b' @ '%' ; + ----------------------------------------------------------------------------------------+ | Grants for b@% | + ----------------------------------------------------------------------------------------+ | GRANT SUPER ON *.* TO 'b' @ '%' | | GRANT SELECT , ALTER , EXECUTE , CREATE ROUTINE, ALTER ROUTINE ON `OTO3`.* TO 'b' @ '%' | | GRANT SELECT ON `mysql`.`proc` TO 'b' @ '%' | + ----------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)