2

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' ...;

1 Answer 1

0

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.

1
  • 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.
    – Rick James
    Dec 10, 2017 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.