Lately, I have been working pretty hard on a paper I have to hand out at the end of my university semester for the machine learning class I'm taking. I will probably do a long blog post about this paper in May if it turns out to be good, but for the time being I have some time to kill while my latest boosting model runs.
So let's talk about something I've started doing lately: creating issues on FOSS webapp project trackers when their documentation tells people to grant all privileges to the database user.
You know, something like:
GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost' IDENTIFIED BY 'password';
I'd like to say I've never done this and always took time to specify a restricted subset of privileges on my servers, but I'd be lying. To be honest, I woke up last Christmas when someone told me it was an insecure practice.
When you take a few seconds to think about it, there are quite a few database level SQL privileges and I don't see why I should grant them all to a webapp if it only needs a few of them.
So I started asking projects to do something about this and update their documentation with a minimal set of SQL privileges needed to run correctly. The Drupal project does this quite well and tells you to:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES ON databasename.* TO 'username'@'localhost' IDENTIFIED BY 'password';
When I first reached out to the upstream devs of these projects, I was sure I'd be seen as some zealous nuisance. To my surprise, everyone thought it was a good idea and fixed it.
If you are using a webapp and the documentation states you should grant all privileges to the database user, here is a template you can use to create an issue and ask them to change it:
Hi! The installation documentation says that you should grant all SQL privileges to the database user: GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost' IDENTIFIED BY 'password'; I was wondering what are the true minimal SQL privileges WEBAPP needs to run properly. I don't normally like to grant all privileges for security reasons and would really appreciate it if you could publish a minimal SQL database privileges list. I guess I'm expecting something like [Drupal][drupal] does. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES ON databasename.* TO 'username'@'localhost' IDENTIFIED BY 'password'; At the database level, [MySQL/MariaDB][mariadb] supports: * `ALTER` * `CREATE` * `CREATE ROUTINE` * `CREATE TEMPORARY TABLES` * `CREATE VIEW` * `DELETE` * `DELETE HISTORY` * `DROP` * `EVENT` * `INDEX` * `INSERT` * `LOCK TABLES` * `REFERENCES` * `SELECT` * `SHOW VIEW` * `TRIGGER` * `UPDATE` Does WEBAPP really need database level privileges like EVENT or CREATE ROUTINE? If not, why should I grant them? Thanks for your work on WEBAPP! [drupal]: https://api.drupal.org/api/drupal/INSTALL.mysql.txt/7.x [mariadb]: https://mariadb.com/kb/en/library/grant/#database-privileges