Log in Sign up

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 partAllows 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 accounts

Generate 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

PrivilegeAllows
SELECT, INSERT, UPDATE, DELETEworking with data
CREATE, ALTER, DROP, INDEXchanging the schema
CREATE ROUTINE, EXECUTE, TRIGGER, EVENTprocedures, triggers, events
LOCK TABLES, PROCESSneeded by some backup tools
FILEreading/writing server files – almost never grant
SUPER / admin privilegesserver administration – root only
GRANT OPTIONpassing 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

PluginNotes
caching_sha2_passworddefault since MySQL 8.0
mysql_native_passwordold; disabled by default in MySQL 8.4 and removed in 9.0 (still common in MariaDB)
auth_socket / unix_socketlogin 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:

  1. Change bind-address in the server config (see MySQL Configuration, Logs and Troubleshooting) to the server's private IP, and restart.
  2. Create a user for the client's IP only: 'app'@'10.0.0.5'.
  3. 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
  1. Require encryption: ALTER USER 'app'@'10.0.0.5' REQUIRE SSL; or require_secure_transport = ON in 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 -p

Security checklist

  • mysql_secure_installation has been run
  • no anonymous users, no test database, no remote root
  • one user per application, minimal privileges, no '%' hosts without need
  • bind-address is 127.0.0.1 or a private IP; port 3306 firewalled
  • TLS required for any connection over the network
  • passwords not in shell history or scripts – use ~/.my.cnf with chmod 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.