Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

migrated from stackoverflow.com Mar 21 '13 at 18:33

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
Hi Michael, thanks for the insight. How do you mean the issue of scalability with creating a database for each user? Do you mean should there be updated/additions then I'll need to add new tables to every database on the server? Also, you are correct - they wont be accessing the server themselves, so if I set it to xxx@'localhost', that will mean the database will only be modified by the application (or if someone gets onto the RDP, which is highly unlikely)? Also, in reference to your comment below, do I not need to use FLUSH then? As when I was researching this, they all said to FLUSH? –  Joshua M Mar 21 '13 at 20:02
    
Thanks Michael, very useful information! –  Joshua M Mar 22 '13 at 9:08
add comment

The first thing you need to know is what permissions can be granted to a user that can only access a single database. Here is the grant table mysql.db:

mysql> desc mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)

mysql>

There are 19 privileges you can grant on a per-database level:

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLE, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, EVENT, TRIGGER

You can create each user with any mixture of these privileges per database. Read the MySQL Documentation on each of these grants and make sure you assign the needed privileges.

share|improve this answer
add comment

Remove the "identified by" from the statement. It should read

GRANT SELECT, INSERT, UPDATE, DELETE ON database.* TO "user"@"%";

Also remember to do a FLUSH PRIVILEGES; after the GRANT

share|improve this answer
    
I'm setting this user up as a new user though - they don't exist already - so I need to use the IDENTIFIED BY to properly set them up? And thanks, I have made sure to do the FLUSH –  Joshua M Mar 21 '13 at 18:32
1  
This answer is incorrect on two counts, since IDENTIFIED BY is correct when using GRANT to implicitly create a user and set their password; also FLUSH PRIVILEGES is not needed when the grant tables are not being directly manipulated. –  Michael - sqlbot Mar 21 '13 at 19:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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