What a user is permitted to do.
THE COMMON ONES
- SELECT: read data
- INSERT: add rows
- UPDATE: change rows
- DELETE: remove rows
- CREATE and DROP: add and remove tables
- ALTER: change table structure
- INDEX: manage indexes
FOR A NORMAL APPLICATION
ALL PRIVILEGES. Applications create tables during installation and alter them during updates, so restricting them causes update failures.
WHEN TO RESTRICT
A reporting tool that only reads could be given SELECT only.
A user for a specific integration could be limited to the tables it needs.
This is refinement rather than a common requirement on shared hosting.
WHAT RESTRICTION BREAKS
An application without CREATE and ALTER cannot install or update. The symptoms are confusing: installation appearing to work and then failing, or an update leaving the database out of step with the code.
If an application misbehaves after you restricted privileges, restore them.
CHECKING WHAT A USER HAS
cPanel > MySQL Databases. Click the privileges link beside the user.
THE PRACTICAL ADVICE
All privileges, one user per database, and no user shared across applications.
