Mysql granting provileges on all databases, except a few

Question

I have a dev MySQL server, on which there are many databases, and most of them change constantly...

I need to grant full access to all databases to a particular user, and only read access to 2 of those databases (these are replicated from another server).

I don't want to explicitly grant full privileges on every database, as the list changes constantly, and it will be a pain to maintain.

I tried grant all, and then grant only usage on the database, but grant all on everything takes precedence.

Is there a good way of accomplishing the goal here?

Answer 1

You can run a SQL statement such as the one below which will generate the SQL statements to do the grants, then just run them. In this example, db1 and db2 are the DB's to exclude from the user, and some_user is the user you are doing the grants for.

SELECT concat(concat('grant all on ',SCHEMA_NAME), ' to some_user') AS `Database`
  FROM INFORMATION_SCHEMA.SCHEMATA
where schema_name not in ('db1','db2')

I don't know of a way to do this without granting the privs on each DB, but at least this way you have an automated way to generate the necessary commands.