MySQL Permissions

MySQLpermissions

A web application that we're working on creates a new database for each user that signs up.

With this, I'm setting up a database user for each of these new databases and wonder what the best permissions are to set for these new users?

Currently I'm using the following permissions

GRANT SELECT, INSERT, UPDATE, DELETE ON database.* TO xxx@'%' IDENTIFIED BY 'xxx_pass';

As this is a client-facing portal, I don't want to give too many permissions away because thats just asking for hack attempts, but I'm not sure if there are other permissions that might need to be granted?

I can't be too explicit on what this application will do, but it's not too heavy – it is mostly data driven, however, so it will be sending data to/receiving data from the database.

Apologies if this is on the wrong site – I'm not entirely sure where it belongs otherwise.

Best Answer

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.

Related Question