1

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?

1 Answer 1

2

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

7
  • 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, 2012 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
    May 26, 2012 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
    May 26, 2012 at 12:27
  • Yeah i do mysql -uroot -psomepassword somedatabase
    – h00j
    May 26, 2012 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
    May 26, 2012 at 12:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .