MySQL and MariaDB¶

A database server holds the data the whole site exists to protect. The defaults on an image are commonly: root with no password, listening on every interface, and able to read any file on the server.

Where¶

/etc/mysql/mysql.conf.d/mysqld.cnf (MySQL, common on Mint) or /etc/mysql/mariadb.conf.d/50-server.cnf (MariaDB, what Debian ships). sudo mysql opens a root shell on the database using socket authentication. After config changes: sudo systemctl restart mysql (or mariadb).

Root password¶

mysql -u root -e 'quit' && echo "root has NO password"

If that prints the message, anyone on the machine (and, if it's listening on the network, anyone anywhere) is the database administrator. Set one:

sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'a long password';
FLUSH PRIVILEGES;

On MariaDB: ALTER USER 'root'@'localhost' IDENTIFIED BY 'a long password';. sudo mysql_secure_installation asks the same questions interactively (set root password, remove anonymous users, disallow remote root, remove the test database) and is fine to use.

Accounts¶

SELECT User, Host, plugin, authentication_string = '' AS blank FROM mysql.user;

Finding	Fix
`root` with Host `%` or an IP	`DROP USER 'root'@'%';` Remote root logins are never needed; the application uses its own account.
A user with empty `User` (anonymous)	`DROP USER ''@'localhost';`
Any user with `blank` = 1	`ALTER USER 'x'@'localhost' IDENTIFIED BY '…';`
Users the README doesn't describe	`DROP USER`
The application's user with `ALL PRIVILEGES ON .`	`REVOKE ALL ON . FROM 'app'@'localhost'; GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'localhost';`
`test` database	`DROP DATABASE test;`

SHOW GRANTS FOR 'app'@'localhost'; shows what an account can do.

Server settings¶

In the [mysqld] section:

Setting	Value	Why
`bind-address`	`127.0.0.1`	Only local programs connect. Only if the README names a remote client does it become that client's network, never `0.0.0.0`.
`local_infile`	`0` (OFF)	`LOAD DATA LOCAL INFILE` can read files off the client, and `LOAD DATA INFILE` off the server; both are used to read `/etc/passwd` through SQL injection
`secure_file_priv`	`/var/lib/mysql-files` (or `NULL` to forbid)	Limits where `INTO OUTFILE` can write, which stops a web shell being written through SQL
`log_error`	`/var/log/mysql/error.log`, uncommented	Errors are recorded
`general_log`	usually off (huge), but `log_error` on
`skip-symbolic-links`	present	Tables can't point at files elsewhere
`skip-show-database`	present	Users see only databases they have rights to

F=/etc/mysql/mysql.conf.d/mysqld.cnf
sudo sed -i -E 's/^\s*#?\s*bind-address\s*=.*/bind-address = 127.0.0.1/; s/^\s*#?\s*log_error\s*=.*/log_error = \/var\/log\/mysql\/error.log/' $F
grep -q '^local_infile' $F || echo 'local_infile = 0' | sudo tee -a $F
grep -q '^skip-symbolic-links' $F || echo 'skip-symbolic-links' | sudo tee -a $F
sudo systemctl restart mysql

Files¶

stat -c '%a %U:%G' /var/lib/mysql
sudo chmod 750 /var/lib/mysql; sudo chown -R mysql:mysql /var/lib/mysql
sudo chmod 640 /etc/mysql/mysql.conf.d/mysqld.cnf

The data directory's "other" digit is 0; a world-readable data directory is the database in the clear. Also look for .my.cnf files in home directories containing a plain-text password, and for .sql dumps lying around (Prohibited Files).

Verify¶

mysql -u root -e 'quit' 2>&1 | head -1        # Access denied
sudo ss -tlnp | grep 3306                      # 127.0.0.1:3306
sudo mysql -sN -e "SHOW VARIABLES LIKE 'local_infile'; SELECT User,Host FROM mysql.user WHERE User='root';"
sudo grep -E '^(bind-address|log_error|local_infile)' /etc/mysql/mysql.conf.d/mysqld.cnf

Example¶

mysql -u root -e quit succeeds: no root password. SELECT User,Host FROM mysql.user shows root@%, ''@localhost, and webapp@% with all privileges. bind-address = 0.0.0.0 and #log_error commented. Set the root password, drop root@% and the anonymous user, recreate webapp as webapp@localhost with rights on its own database only, bind to 127.0.0.1, uncomment log_error, set local_infile = 0, restart.

Try it¶

Log in as root with no password on a fresh install, then set one and try again.
Create a user with ALL ON *.*, read SHOW GRANTS, revoke down to one database.

Build it¶

A mysql-check.sh that runs the SELECT User, Host and SHOW VARIABLES queries and greps mysqld.cnf for bind-address.

Next¶

PostgreSQL