Give every app and person its own MySQL account with only the rights it needs. This page covers the everyday basics: create a user, grant rights on one database, check them and apply them.
Full guide: MySQL User & Permissions
Related guide: Full MySQL users and permissions guide
1. Open the MySQL shell
On Ubuntu, root logs in through sudo with no password.
sudo mysql2. Create the user
The part after @ is where the user may connect from; localhost means this server only.
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'a-long-random-password';3. Grant rights on one database
appdb.* means every table in appdb and nothing else.
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'localhost';4. Common privileges
Grant the smallest set that works.
| Privilege | What it allows |
|---|---|
| SELECT | Read rows |
| INSERT, UPDATE, DELETE | Change data |
| CREATE, ALTER, DROP, INDEX | Change table structure (needed for app installs and upgrades) |
| ALL PRIVILEGES ON db.* | Everything on one database |
| GRANT OPTION | Let the user grant rights to others (avoid) |
5. Verify
SHOW GRANTS FOR 'appuser'@'localhost';Troubleshooting
ERROR 1045 Access denied: check the user name, the host part ('app'@'localhost' and 'app'@'%' are different accounts) and the password. ERROR 1044: the user has no rights on that database yet. See exactly what an account can do with SHOW GRANTS.
SHOW GRANTS FOR 'appuser'@'localhost';
SELECT user, host, plugin FROM mysql.user;FAQ
Do I need FLUSH PRIVILEGES?
Not after CREATE USER or GRANT; MySQL applies them at once. It is only needed after editing the grant tables directly.
Should my app use the root account?
No. A breach in that app would expose every database on the server.
Further reading: DigitalOcean: create a new user and grant permissions in MySQL