Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to deal with the permissions of MySQL stored procedures, the article is very detailed, has a certain reference value, interested friends must read it!

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)

Now, the permissions of all privileges will 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.

The above is all the contents of the article "how to deal with the permissions of MySQL stored procedures". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.