Users are user + host
A MySQL account is always a pair: 'name'@'host'. 'shop'@'localhost' and 'shop'@'%' are two different accounts with their own password and privileges.
| Host part | Allows connections from |
|---|---|
'localhost' | the same machine via socket |
'127.0.0.1' | the same machine via TCP |
'10.0.0.5' | exactly that IP |
'10.0.0.%' | that subnet |
'%' | anywhere – avoid if possible |
Creating and managing users
CREATE USER 'shop'@'localhost' IDENTIFIED BY 'a-long-random-password';
ALTER USER 'shop'@'localhost' IDENTIFIED BY 'new-password'; -- change password
RENAME USER 'shop'@'localhost' TO 'webshop'@'localhost';
DROP USER 'webshop'@'localhost';
SELECT user, host, plugin FROM mysql.user; -- list accountsGenerate a strong password on the shell: openssl rand -base64 24
Granting privileges
-- typical web application: read and write data, no schema changes
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop'@'localhost';
-- everything in one database (e.g. for migrations)
GRANT ALL PRIVILEGES ON shop.* TO 'shop_admin'@'localhost';
-- read-only reporting user for one table
GRANT SELECT ON shop.orders TO 'report'@'10.0.0.%';
SHOW GRANTS FOR 'shop'@'localhost';
REVOKE DELETE ON shop.* FROM 'shop'@'localhost';Levels: *.* (whole server), shop.* (one database), shop.orders (one table), or single columns.
GRANT, REVOKE and CREATE USER take effect immediately – FLUSH PRIVILEGES is only needed if someone edited the mysql tables directly.
Important privileges
| Privilege | Allows |
|---|---|
SELECT, INSERT, UPDATE, DELETE | working with data |
CREATE, ALTER, DROP, INDEX | changing the schema |
CREATE ROUTINE, EXECUTE, TRIGGER, EVENT | procedures, triggers, events |
LOCK TABLES, PROCESS | needed by some backup tools |
FILE | reading/writing server files – almost never grant |
SUPER / admin privileges | server administration – root only |
GRANT OPTION | passing on privileges |
Give every application its own user with only the privileges it needs.
Roles
Roles (MySQL 8.0, MariaDB 10.0.5+) bundle privileges:
CREATE ROLE 'app_read', 'app_write';
GRANT SELECT ON shop.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON shop.* TO 'app_write';
GRANT 'app_read', 'app_write' TO 'shop'@'localhost';
SET DEFAULT ROLE ALL TO 'shop'@'localhost'; -- MySQL; MariaDB: SET DEFAULT ROLE app_write FOR ...Authentication plugins
| Plugin | Notes |
|---|---|
caching_sha2_password | default since MySQL 8.0 |
mysql_native_password | old; disabled by default in MySQL 8.4 and removed in 9.0 (still common in MariaDB) |
auth_socket / unix_socket | login tied to the Linux user – used for root on Ubuntu/Debian/MariaDB |
Very old client libraries may fail with "authentication method unknown" against MySQL 8 – update the client rather than downgrading the server's security.
Remote access
By default the server only listens on 127.0.0.1. If another machine must connect:
- Change
bind-addressin the server config (see MySQL Configuration, Logs and Troubleshooting) to the server's private IP, and restart. - Create a user for the client's IP only:
'app'@'10.0.0.5'. - Allow the port only for that IP in the firewall:
sudo ufw allow from 10.0.0.5 to any port 3306 proto tcp # Ubuntu/Debian
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="10.0.0.5" port port="3306" protocol="tcp" accept'
sudo firewall-cmd --reload # RHEL family- Require encryption:
ALTER USER 'app'@'10.0.0.5' REQUIRE SSL;orrequire_secure_transport = ONin the server config. MySQL 8 creates TLS certificates automatically; for MariaDB they must be configured.
Never expose port 3306 to the whole internet. For admin work, an SSH tunnel is simpler and safer:
ssh -L 3307:127.0.0.1:3306 user@server
mysql -h 127.0.0.1 -P 3307 -u shop -pSecurity checklist
mysql_secure_installationhas been run- no anonymous users, no
testdatabase, no remote root - one user per application, minimal privileges, no
'%'hosts without need bind-addressis127.0.0.1or a private IP; port 3306 firewalled- TLS required for any connection over the network
- passwords not in shell history or scripts – use
~/.my.cnfwithchmod 600 - applications use prepared statements (see PHP Databases with PDO)
- regular, tested backups (see MySQL Backup and Restore)
- security updates installed (
apt upgrade/dnf upgrade)
Next: MySQL Backup and Restore
Put together with AI as a quick reference I keep for my own work. Entries like this are usually not reviewed in detail by a person.