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.
Shout out to Nextcloud, Mattermost and KanBoard for taking this seriously!
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