Shulou(Shulou.com)06/01 Report--

The stored procedure of MySQL, yes, looks like an obscure usage scenario. The problem comes from a developer who submitted a work order for permission application and needed to open some permissions.

Originally, it is a very normal operation, but in my opinion, it is quite urgent and urgent. I am ashamed to say that I am busy with directional planning and development, but this basic operation function has been ignored, so I see some current implementation methods. I still hope to be able to do some small things to liberate these repetitive work.

Of course, I decided to take over some basic work, on the one hand, to decompress my colleagues, and on the other hand, to make a complete experience, because many needs and pain points can easily capture the key points through practice. If I think it is unreasonable, then there is bound to be some improvement in this process. Such as deployment and installation, such as permission activation. The permission opening of the database is a relatively typical case, and the permission opening of stored procedures is even a little suspicious of life.

The scenario of the problem is still very basic. Developers need to activate some basic permissions and declare that they need to add, delete, modify and check permissions when marking permissions, as well as permissions for DDL, such as drop,alter,create, and so on. See here, I feel not quite right, what kind of operation should require so much authority?

I simply stated my position that the ideas of the development students are convenient to manage, so I recruited them directly. After simple communication, I found that their demand scenarios are still very routine. They need to create some daily tables dynamically, so they need create permissions to be given after evaluation, but for ordinary users, create permissions are not recommended to be open. The main starting point is to be able to do some basic audit of SQL, even manual audit or platform audit is a required process. Therefore, after a circle of communication, it is found that the opened permissions can be quickly tailored, and it is also necessary for them to modify the logic of stored procedures, because in some specific scenarios, they want to be more flexible in their control of logic.

All right, the basic background is over. Give permissions to basic tables and stored procedures. An important point to note in this part of the stored procedure 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 very simple sentence:

Grant execute,alter procedure on xxx.xxx to xxx@'xxxx'

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

Because we don't have access to select procedure or view procedure, we can hardly intervene anymore.

Using the command line can reproduce this problem:

There is no substantive content of the stored procedure. After tossing about there for a long time, I found that it was an old problem, an old problem more than 10 years ago.

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

In fact, the solution to the problem is very simple, which requires such a sentence:

Grant select on mysql.proc to xxxx@'xxxx' is fine.

So fine-grained access control is so tangled, but it does work.

For example, if we cite an example, we know that all privileges in MySQL is a large authority, but we may not have a clear idea of how many permissions it contains.

We can push back through fine-grained access control.

For example, create a user and give all privileges permissions.

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 some contraction.

Mysql > revoke insert on test.* from jeanron@'%'; Query OK, 0 rows affected (0. 00 sec) well, the permissions of all privileges show its original form. 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, in fact, if select on *. * actually contains the fine-grained permission mysql.proc that we need, if we want to peel off the cocoon, it is basically such a routine.

Add: let's take a look at the permissions of mysql to create stored procedures.

First of all, with the emergence of mysql stored procedures, five kinds of user permissions have been added, of which three are related to stored procedures:

ALTER ROUTINE edits or deletes stored procedures CREATE ROUTINE establishes stored procedures EXECUTE runs stored procedures

Assign these three permissions when you create a user using GRANT. Stored procedures run by default with the permissions of the creator.

It should be noted that when a user has permission to create a stored procedure, if he does not have permission for select, update or delete, although the stored procedure for operating data can be established, the call to the stored procedure is still unsuccessful, and a permission error will be returned, even if he has the permission to run the stored procedure. So, if someone gives you a user who has no select, update or delete permissions but only CREATE ROUTINE permissions, scold him, he is intentional.

Of course, the stored procedure created by such users is not completely unusable. When creating a stored procedure, there is a characteristic clause that allows the stored procedure to use the permissions of the operator. After the stored procedure is established, just add the SQL SECURITY INVOKER characteristic clause.

As follows.

CREATE PROCEDURE p () SQL SECURITY INVOKER allows you to assign two groups of people, one to create stored procedures, and the other to run stored procedures and select, update, and delete as testers. With this permission allocation, the security of mysql does not need to be protected at the functional layer. I use the stored procedure created by the root user, but at the functional layer it is called by a user who only has the right to run the stored procedure. So, even if you get the user name and password of the database from the functional layer and simulate ip, you won't get anything you want. With permissions, we can rest assured that we can boldly use stored procedures without worrying about security.

Summary

The above is a summary of the permissions of MySQL stored procedures introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply you in time. Thank you very much for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.