User Management & Privileges
A real application should never connect to its database as root — MySQL's user and privilege system lets you create an account that can only do exactly what it needs to.
Creating a user
SQL mysql shell
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'a-strong-password';
Output
Query OK, 0 rows affected (0.02 sec)
The 'app_user'@'localhost' form matters — MySQL treats the same username connecting from a different host as a completely separate account, which is how you can grant different permissions to an app running on the same server versus one connecting remotely.
Granting specific privileges
SQL mysql shell
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app_user'@'localhost'; SHOW GRANTS FOR 'app_user'@'localhost';
Output
+------------------------------------------------------------------------------+ | Grants for app_user@localhost | +------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `app_user`@`localhost` | | GRANT SELECT, INSERT, UPDATE, DELETE ON `shop`.* TO `app_user`@`localhost` | +------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
This user can read and write data in the shop database, but can't run DROP TABLE, create new users, or touch any other database on the server — exactly the set of things a web application actually needs, and nothing more.
Revoking a privilege
SQL mysql shell
REVOKE DELETE ON shop.* FROM 'app_user'@'localhost';
Output
Query OK, 0 rows affected (0.01 sec)
Principle of least privilege: connecting your application as
root (or any account with full privileges) means a single SQL injection vulnerability anywhere in your code becomes a total server compromise — the attacker's injected query can do anything root can do, including dropping every database on the server. Creating a scoped-down user like app_user above turns that same vulnerability into a much smaller, contained problem.