Summary of MySQL stored procedure permission issues

MySQL stored procedures, yes, look like very rare usage scenarios. The problem originated from a developer who submitted a work order for permission application, which required some permissions to be enabled.

This is a very normal operation, but in my opinion it is rather urgent and pressing. To be honest, I was so busy with direction planning and development that I overlooked this basic operation function. So after seeing some of the current implementation methods, I still hope to do some small things to free up these repetitive tasks.

Of course, I decided to take over some of the basic work. On the one hand, it was to relieve the pressure on my colleagues, and on the other hand, it was to create a complete experience. This is because many needs and pain points can be easily captured through practice. If I think it is unreasonable, then there will inevitably be some improvements in this process. For example, deployment and installation, and permission activation. Enabling database permissions is a relatively typical case, and enabling permissions for stored procedures can even make people question their lives.

The scenario of the problem is still very basic. Developers need to open some basic permissions and declare the permissions required to add, delete, modify and query when marking permissions, as well as DDL permissions, such as drop, alter, create, etc. When I saw this, I felt something was wrong. What kind of operation would require such broad permissions?

I simply stated my position. The developer's idea was to facilitate management, so I directly hired him. After a brief communication, I found that their demand scenarios were actually very routine. They needed to dynamically create some daily tables, so the create permission could be granted after evaluation. For general users, the create permission is not recommended. The main starting point is to be able to perform some basic audits on SQL, even manual audits or platform audits are a necessary process. So after some communication, we found that the enabled permissions can be quickly trimmed. For them, it is also necessary to modify the logic of the stored procedure because in some specific scenarios, they hope to have more flexible control over the logic.

Okay, that’s the basic background introduction. Grant permissions to basic tables and stored procedures. An important point to note about stored procedures is SQL SECURITY. The default creation is definer. If you need to open it to other users, it is recommended to set it to invoker.

So a simple sentence:

grant execute,alter procedure on xxx.xxx to xxx@'xxxx';

Unfortunately, developers reported that they could not see the contents of the stored procedure when they opened it through SQLyog or Navicator.

Because we do not have the permission to select procedure or view procedure, we have almost no way to intervene.

This problem can be reproduced using the command line:

There is no substantial content of stored procedures. After struggling with it for a while, I found out that it was an old problem, from more than 10 years ago.

https://bugs.mysql.com/bug.php?id=20235

The solution to the problem is actually very simple, just need this sentence:

grant select on mysql.proc to xxxx@'xxxx'

So fine-grained permission control is so complicated, but it is indeed effective.

For example, we know that all privileges in MySQL is a very large permission, but we may not have a clear concept of how many kinds of permissions it contains.

We can completely reverse this through fine-grained permission control.

For example, create a user and grant all privileges.

mysql> grant all privileges on test.* to 'jeanron'@'%' identified by 'jeanron100'; 
Query OK, 0 rows affected, 1 warning (0.00 sec)

The permissions included are as follows:

mysql> show grants for jeanron; 
+---------------------------------------------------+ 
| Grants for jeanron@% | 
+---------------------------------------------------+ 
| GRANT USAGE ON *.* TO 'jeanron'@'%' | 
| GRANT ALL PRIVILEGES ON `test`.* TO 'jeanron'@'%' | 
+---------------------------------------------------+ 
2 rows in set (0.00 sec)

Let's do a contraction.

mysql> revoke insert on test.* from jeanron@'%'; 
Query OK, 0 rows affected (0.00 sec) 
Now, the permissions of all privileges are revealed.
mysql> show grants for jeanron; 
| Grants for jeanron@% 
+------------------------------------------------------------------------------------------------------ 
| GRANT USAGE ON *.* TO 'jeanron'@'%' 
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'jeanron'@'%' |

So in the above question, if select on *.* actually already includes the fine-grained permissions we need, mysql.proc. If you want to unravel it, it is basically this routine.

Supplement: Let's take a look at the MySQL stored procedure creation permission issue

First, when MySQL stored procedures appeared, five more user permissions were added, of which three were related to stored procedures:

• ALTER ROUTINE Edit or delete a stored procedure
• CREATE ROUTINE Create a stored procedure
• EXECUTE runs a stored procedure
  

These three permissions are assigned when creating a user using GRANT. By default, a stored procedure is run with the permissions of the creator.

It should be noted that when a user has the permission to create a stored procedure, if he does not have the select, update, or delete permissions, although the stored procedure for operating data can be created, calling the stored procedure will still fail and a permission error will be returned, even if he has the permission to run the stored procedure. So, if someone creates a user for you who has no select, update, or delete permissions but only the CREATE ROUTINE permission, scold him, because he did it on purpose.

Of course, the stored procedures created by such users are not completely unusable. There is a feature clause in the creation of stored procedures that allows the stored procedures to use the permissions of the runner. After creating the stored procedure, just add the SQL SECURITY INVOKER feature clause.

as follows.

CREATE PROCEDURE p() SQL SECURITY INVOKER 這, two groups of people can be assigned, one group is given the permission to create stored procedures, as developers, and the other group is given the permission to run stored procedures and select, update, and delete permissions, as testers. (My brain is crazy) With this kind of permission allocation, the security of MySQL does not need to be protected at the functional level. I created the stored procedure through the root user, but called it at the functional level with a user who only has the permission to run the stored procedure. So, even if you get the database username and password from the functional layer and simulate the IP, you can't get anything you want. With permissions, we can use stored procedures without worrying about security issues.

Summarize

The above is a summary of the MySQL stored procedure permission issues that I introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!