Wordpress - MySQL Database User: Which Privileges are needed?
"All privileges" usually means you should grand everything to the user. However ...
I've found at least one article that claims the MySQL user only needs:
- SELECT
- INSERT
- UPDATE
Digging deeper, I found that in order to operate fully (automated updates, plug-in installation/uninstallation, etc.), WordPress requires some additional permissions:
- DELETE
- ALTER (for updates)
- CREATE TABLE
- DROP TABLE
Also, not referenced but it makes sense:
- INDEX
But those are the only two solid references I can find that are backed up by opinions posted elsewhere. I'd still encourage you to stick with GRANT ALL, but if you absolutely must limit your DB use, start with these 7 privileges and test fully to make sure things work as expected.
The others are not needed as you point out.
Btw, what you could do is, conditionally set the user/pass based on the requested page. As in unprivileged with select/insert/update/delete for normal usage, and privileged with definition/index related stuff in addition when visiting the upgrade page.
Here's what Codex has to say on restricting database user privileges:
For normal WordPress operations, such as posting blog posts, uploading media files, posting comments, creating new WordPress users and installing WordPress plugins, the MySQL database user only needs data read and data write privileges to the MySQL database; SELECT, INSERT, UPDATE and DELETE.
Therefore any other database structure and administration privileges, such as DROP, ALTER and GRANT can be revoked. By revoking such privileges you are also improving the containment policies.
Note: Some plugins, themes and major WordPress updates might require to make database structural changes, such as add new tables or change the schema. In such case, before installing the plugin or updating a software temporarily allow the database user the required privileges.
http://codex.wordpress.org/Hardening_WordPress