MySQL Should each Database be managed by a different user?

Question

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?

Tom H · Accepted Answer · 2012-05-26 12:18:54Z

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';

answered May 26 '12 at 12:18

Tom H
5,0251927

	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

1 Answer