I find the list of privileges provided by MySQL to be a bit overwhelming. I'm not sure who should have what privileges. In my mind there are three typical users for my situation:
root is self explanatory. For
developer this user needs to be able to easily access any database, make adjustments to it, etc. For starters I'm setting this user to this privilege set:
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON
application has an even more limited set. It should just be limited to manipulating a specific database.
I'm not sure what a reasonable set of privileges is to grant. What are a reasonable set of privileges to grant a developer and an application and why?