MySQL: Grant Read-Only DB Access for one DB and Create/Drop Access for other DBs

Question

Within MySQL, is it possible to provide a user read-only access to an existing database, but allow the user to create or drop any new databases?

I know it is possible to grant privileges via matching the database name (e.g. by prefix or suffix), but what would be the proper syntax for the first statement below:

GRANT ALL PRIVILEGES ON 'NOT(readonly_db_name)' ...; GRANT SELECT ON 'readonly_db_name' ...;

Answer 1

No. Instead use the following as a general way to provide limited actions.

Here's how to "grant" things to the user indirectly. Create a Stored Procedure with "security definer" and be "root" when you define it. In the SP, first check that the user is not trying to touch mysql, information_schema, or the readonly db. Then proceed to do the CREATE or DROP.

This can obviously be extended to a variety of actions. You should probably have one SP per action -- one for CreateDb('dbname'), one for DropDb('dbname'), and whatever else you might need.

Then train the users to use

CALL CreateDb('...');

instead of

CREATE DATABASE ...;

Note: you might need extra parameters for what should be added to the creation, such as CHARACTER SET utf8mb4. Or you could assert more control by not giving the user a choice.

• Rick James: You can probably limit the permissions GRANTed to the users so that they cannot do CREATE DATABASE, but can create a database via CALL CreateDb.