Privileges for only specific stored procedures in MySQL/MariaDB

MySQL is a very popular relational database (it’s freeee), so I was a bit surprised when realized that you just can’t easily grant some permissions ONLY for certain procedure without any problems and also there is not much information about such problem over noble resources. Sure you can do it with a bit of a `hack` and let’s try that.

Who is that newuser? What can he do?

First things first:

The Problem:

1. We have a new developer in a company, who has to write some new procedures/change old ones with some logic in there. We don’t want to give him more permissions then needed to complete that task as we are not yet completely sure in his competence.
2. So we want to give him (newuser@host) rights to: CREATE/DROP/EXECUTE ONLY specific (with specific names, for example Procedure1)procedures.

Here are sample solutions:

Solution 1 (not that right, but it’s working)

We create a new schema, let’s call s2 (and the primary schema is s1) and give the new user full access on it for example:

GRANT ALL PRIVILEGES ON s2.* TO 'newuser'@'host';

After that he can CREATE/DROP/EXECUTE anything he wants there (for example Procedure1Temp).

Another user with more privileges on s1 can then create Procedure1 wrapper in s1 which will call the s2.Procedure1Temp. This will complete our task, but when the new developer finishes his work we will still need to copy procedure body from s2 to s1 to “complete work” and get normal database structure without any additional relation to other schemas.

Solution 2 (probably this is not ideal too, but it’s more proper in this case)

We grant the new user privilege to create any procedure in s1

GRANT CREATE ROUTINE ON s1.* to ‘newuser’@’host’; # This will grant creation of any new procedures in schema s1, which is secure in our case: he can't DROP(change) old ones

After that just grant ALL privileges on procedure he needs, for example Procedure1:

GRANT ALL PRIVILEGES ON PROCEDURE s1.Procedure1 TO ‘newuser’@’host';

That’s all: he can do whatever he wants with Procedure1 ! Actually this effectively gives the following:

GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE s1.Procedure1 TO ‘newuser’@’host’ WITH GRANT OPTION;

The only problem if it’s a problem here is: the definer of the newly created procedures will be ‘newuser’@’host’, he can’t set the definer until he is SUPER privilege set and you probably won’t give him such privilege on s1 schema. But you can override it (with another user who has more privileges on s1 schema) with simple query like:

UPDATE mysql.proc SET definer = 'newuser@host' WHERE definer='root@localhost'; # For example changes the definer to root@localhost

References:

MySQL :: MySQL 8.0 Reference Manual :: 13.7.1.6 GRANT Statement

https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html