What are reasonable privileges to grant typical users? 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 
developer 
application


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?
A typical user should have:

SELECT, INSERT, DELETE, UPDATE, CREATE TEMPORARY TABLES, EXECUTE


The first four are pretty obvious -- though you may also set up "read only" users with only SELECT.

CREATE TEMPORARY is also handy and typically harmless: temporary tables can help in optimizing queries, breaking them into smaller &amp; faster parts. They are limited to the executing connection, and are automatically dropped when it is closed. 

EXECUTE depends on your type of system. Do you have stored routines? Would you like your users to access them? Make sure you also know about the SECURITY=DEFINER/INVOKER definition for stored routines.

At any case, make sure to apply all the above on specific schemas. Avoid using:

GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'some_user'@'some_host';


as the above also grants privileges on the mysql system tables, effectively allowing any user to create new accounts or upgrade their own set of privileges.
Instead, do:

GRANT SELECT, INSERT, UPDATE, DELETE ON some_schema.* TO 'some_user'@'some_host';
GRANT SELECT, INSERT, UPDATE, DELETE ON another_schema.* TO 'some_user'@'some_host';