A site with a database user that can drop tables and read every other database is one SQL injection away from losing everything on the server. The fix costs nothing: give each site its own user with rights over its own database only.

A user for one database

CREATE USER 'site_shop'@'localhost' IDENTIFIED BY 'a-long-random-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'site_shop'@'localhost';
FLUSH PRIVILEGES;

Those four verbs are what a running application needs. Note what is absent: DROP, ALTER, CREATE, and anything on *.*.

What an installer needs, briefly

An installer or a migration does need to create and alter tables. Grant it for the install and take it back afterwards - a permission held only while it is used cannot be abused when it is not.

GRANT CREATE, ALTER, INDEX, DROP ON shop.* TO 'site_shop'@'localhost';
-- after the install
REVOKE CREATE, ALTER, INDEX, DROP ON shop.* FROM 'site_shop'@'localhost';

Never these

GRANT ALL ON *.* gives one site read and write over every other database on the server, plus the rights to create users. It is the default in far too many tutorials.
  • FILE - reads and writes files on the server as the database user.
  • SUPER - changes global settings and kills other sessions.
  • GRANT OPTION - lets the user give away rights, including to itself.

Check what you already gave

SELECT user, host FROM mysql.user;
SHOW GRANTS FOR 'site_shop'@'localhost';

The host part is half the security

'user'@'localhost' can only connect from the machine itself. 'user'@'%' can connect from anywhere on the internet, and a password is then the only thing in the way.

EGPNL creates the user with the right grants when you create a database, so this page matters most on a VPS you administer yourself.