MySQL: Grant Read-Only DB Access for one DB and Create/Drop Access for other DBs 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' ...;
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.