Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Should I have a different MySQL user for each database so that if someone gets access to a mysql user they cannot effect the others?

If so how would i go about doing this?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Its a principle called "separation of privileges", which reduces the impact of an intrusion.

you can do something like

GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'127.0.0.1' IDENTIFIED BY 'somepassword';
FLUSH PRIVILEGES;

or you can provide some subset of privileges using a command similar to this;

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
     ON mydatabase.*
     TO 'myuser'@'localhost';

The privileges supported by MySQL (5.1) are documented here;
http://dev.mysql.com/doc/refman/5.1/en/grant.html#grant-privileges

share|improve this answer
    
If I am currently running all databases as root user, how do I remove the privileges from that user so I can create new users for each db. –  h00j May 26 '12 at 12:21
    
do you mean that you are running the databases as a root system user, or that each user connects to the database as root i.e. mysql -uroot -psomepassword somedatabase? –  Tom H May 26 '12 at 12:24
    
if its the latter, then create a user and password for each database, and distribute that information to your customers. When you are happy that they have migrated to the per-database user accounts, change the root password. –  Tom H May 26 '12 at 12:27
    
Yeah i do mysql -uroot -psomepassword somedatabase –  h00j May 26 '12 at 12:46
    
the root user should always exist with full privileges, but its that normal day-to-day access should not being using it! –  Tom H May 26 '12 at 12:57

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.