Mysql modify stored procedure related permissions issue

When using MySQL database, you often encounter such a problem: the stored procedure defined by other users cannot be modified or deleted by another user. Normally, the definer of the stored procedure has the permission to modify and delete it, but other users must have the corresponding authorization, otherwise they cannot view or call it.

In MySQL, user A creates a stored procedure. Now, another user B wants to modify the stored procedure created by A. The following records are generated based on this situation.

User A's permissions for the OTO3 library:

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)

User B's permissions:

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)

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

When checking the stored procedure list, it prompts that there is no permission:

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'

As root, add permission to view stored procedures to user B:

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)

Go back to user B to view the stored procedure list:

mysql> select `name` from mysql.proc where db = 'OTO3' and `type` = 'PROCEDURE';
+------------------------+
| name |
+------------------------+
| proc_cs |
+------------------------+
1 row in set (0.00 sec)

At this time, an additional mysql database is found, but only query permissions for mysql.proc are available:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|OTO3|
|mysql |
+--------------------+
3 rows in set (0.00 sec)

There is only one table in the mysql library: proc

mysql> use mysql
mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
|proc|
+-----------------+
1 row in set (0.00 sec)

You can also see the details of the stored procedure:

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

Try to modify the configuration of the stored procedure:

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'

In order to facilitate viewing, try to modify the stored procedure on the Navicat tool, and report the following permission problem when saving:

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

Try to add a stored procedure and report permission information:

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

This means that user b does not have the permission to modify the authorized stored procedure of OTO3;

Try to call the following stored procedure as user B:

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

It is obvious that there is no permission to run.

Try to delete the original user-defined stored procedure a, and the permission information will also be reported, as follows:

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

It can be seen that user B does not even have the permission to call the stored procedure. Here we first add the execution permission:

Next, add a permission to execute the stored procedure:

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)

Execute the stored procedure again and find that it succeeds;

Time: 0.080ms
Procedure executed successfully
Affected rows: 0

Then add the permission to create and add stored procedures:

mysql> grant CREATE ROUTINE 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, CREATE ROUTINE ON `OTO3`.* TO 'b'@'%' |
| GRANT SELECT ON `mysql`.`proc` TO 'b'@'%' |
+-----------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

After adding the permissions above, you can create a stored procedure;

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

But you cannot delete the ones you created.

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

Next, add a modification permission, which can also be deleted;

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

View User Permissions

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)

After adding the alter ROUTINE permission above, you will have the delete permission for all OTO3 stored procedures [add, delete, and modify your own defined ones]. You can delete the stored procedures defined by others, but you cannot modify them. When modifying the stored procedures defined by others, the following prompt will appear:

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

Can you explain here where this SUPER permission is? By checking the user permissions, it turns out that here:

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)

Try adding the SUPER permission:

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)

This permission cannot be executed on the specified database because SUPER is a global permission for the entire MySQL.

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)

When you check again, you will find that Super_priv: Y has changed; then modify the stored procedure defined by others;

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

Checking all databases, I found that only one proc table in the mysql database had read permissions. SUPER was not as powerful as I thought.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|OTO3|
|mysql |
+--------------------+
3 rows in set (0.00 sec)

If you look closely, you will find the execution statement:

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

We can see that there are Create_routine_priv: N and Alter_routine_priv: N. These two permissions are obviously permissions for stored procedures. Can we use these two permissions without SUPER?

Reclaim this SUPER permission;

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

Add Alter_routine_priv, Create_routine_priv

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

View user b's permissions

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)

It is found that the same permission problem is still reported:

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

After executing the above permissions, you can see other system libraries: [For example, the sys library also has stored procedures, because these two permissions are global]

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|OTO3|
|mysql |
| performance_schema |
|sys|
| test |
+--------------------+
6 rows in set (0.00 sec)

These two permissions are more powerful. They can even see, modify, and delete stored procedures in the system library sys, which is very dangerous. We decided to revoke the permissions again.

create routine, alter routine;
mysql> revoke create routine,alter routine on *.* from 'b'@'%';

It is safer to use the SUPER privilege.

The following conclusions were drawn from the above tests:

View stored procedure permissions: SELECT # is the permission for the mysql.proc table;
Execute stored procedure permission: EXECUTE # is the permission for the specified database;
Create stored procedure permission: CREATE ROUTINE # is the permission for the specified database;
Modify the stored procedure permissions: ALTER ROUTINE # is to define the stored procedure in the specified database;
Modify the stored procedure permissions defined by others: SUPER # is the global permission for the entire MySQL;

In short, user A defines a stored procedure in the database OTO3. Now he wants to use user B to execute and modify the stored procedure. He needs to add the following permissions to user B:

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

So the most basic permissions for user B are:

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)

At this point, it is very clear that another user has the right to modify the stored procedures defined by others in MySQL.

If the stored procedure is not called as another user, you can use root privileges to change the definer of the stored procedure; this is equivalent to changing the owner's privileges in Linux;

update mysql.proc set DEFINER='b'@'%' WHERE NAME='proc_cs' AND db='OTO3';

This is the end of this article about the issue of permissions related to MySQL modified stored procedures. For more relevant MySQL stored procedure permissions content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: