What privileges is it better to use to satisfy the requirements of most popular Content Management Systems?
Is it safe to say:
GRANT ALL PRIVILEGES ON siteX.* TO siteX@localhost
Or better to use something like:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER,
CREATE TEMPORARY TABLES, LOCK TABLES,
EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE
ON siteX.* TO siteX@localhost
This depends on the CMS to a very large extent (assuming you mean content management system).
Keep in mind however that specific requirements may vary with implementations. For example for some things, delete may not be something you want to support.
This being said, usually in the most common case, you want to support all database operations on all tables the CMS expects to use. Again, that is not always the case. You need to take individual instance requirements into account.