Thesql: show which users have permissions to a DB

Nothing built-in. You have two options though:

• Use common_schema's sql_show_grants view. For example, you can query:

  SELECT sql_grants FROM common_schema.sql_show_grants;
  

  Or you can query for particular users, for example:

  SELECT sql_grants FROM common_schema.sql_show_grants WHERE user='app';
  

  To install common_schema, follow the instructions here.

  Disclaimer: I am author of this tool.

• Use Percona Toolkit's pt-show-grants, for example:

  pt-show-grants --host localhost --user root --ask-pass
  

In both cases you can ask for the GRANT command or the REVOKE (opposite) command.

The first case requires that you install a schema, the latter requires that you install PERL scripts + dependencies.

I would question the scalability of creating a database for each user, but other than that it looks like you are on the right track.

Presumably, you're not allowing users to connect directly to your MySQL server, in which case 'xxx'@'%' is probably too generous... Your MySQL server should be behind a firewall, of course, but even still, 'xxx'@'app.server.or.web.server.ip.address' might be a better option if it makes sense.

As for permissions, the minimum needed would be the correct route, and the ones you've listed would probably be the ones to start with. Maybe CREATE TEMPORARY TABLE.

Stored procedures and functions would require EXECUTE, though triggers do not. Triggers have their own set of security mechanisms.

Remember, also, that views, by default, use the permissions of the DEFINER to determine privileges on the tables underlying the views, unless you explicitly use SQL SECURITY INVOKER when declaring them, which causes the privileges of the invoking user to be used instead.

Update:

The Internet at large seems really good at off-target advice for MySQL.

In the manual, When Privilege Changes Take Effect explains why FLUSH PRIVILEGES is not needed in your scenario.

If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.

If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE, your changes have no effect on privilege checking until you either restart the server or tell it to reload the tables.

You can actually change privileges by directly manipulating mysql.user and the other grant tables. These changes don't take effect until FLUSH PRIVILEGES causes the server to re-cache those tables, because live privilege checking never looks in the tables -- it only looks in the cached data in the privilege system's memory structures.

The potential scalability concerns I alluded to was not meant to persuade you to do otherwise, but was more of a reflection the fact that I didn't want to leave the impression of blanket endorsement of your plan, for reasons that included the one you mentioned -- that changes to your application's data structure needs will require a lot of changes to be made (not an impossible challenge), as well as the fact that your datadir will have a directory created for each database, which could eventually result in a constraint (depending on your OS limits), and I rarely deal with servers with more than about 500 schemas or 15,000 tables or 1 TB of data... though there are no preset limits to what you can provision.