CODEWITHSUNDEEP
mysqlsql
AkBKukU
AkBKukU

Reputation: 150

MySQL separate users for read, write, and admin

I took a MySQL class a couple years ago where the instructor had us create separate users with different permissions on the tables as a last line of defense against attacks like SQL injection. The users would basically be created like this:

CREATE USER 'read'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON * TO 'read'@'localhost';

CREATE USER 'write'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON * TO 'write'@'localhost';

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON * TO 'admin'@'localhost';

Then based what the application was doing with the database you would use the credentials that had sufficient access. I was recently looking into implementing this and was trying to see what the best practices for this are but I haven't been able to find anything.

Are there best practices for this? Is this something people actually do? Is it overkill with prepared and sanitized statements?

Upvotes: 1

Views: 4171

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270003

I don't think this is a best practice by itself. Of course, having the right permissions is definitely a best practice. And, having read-only access for read-only applications is good.

The simplest method for preventing injection is to use parameterized queries. This is your first line of defense. If all your queries are select queries, then connecting as a user with read-only access is a win.

However, if you are modifying the database as well, this doesn't work. Instead, wrap the code that makes the changes in stored procedures. These can run under a different security context. This allows the application to have read permissions and execute permissions, but not data modification permissions.

There are other advantages to using stored procedures instead of insert/update/delete statements directly. For instance: you can log the operations, you can run additional logic, you can modify multiple tables at the same time, transactions can be hidden from the application.

Upvotes: 2

Related Questions