MariaDB Users and Privileges

Apps don't log in as root. Each gets a user of its own that may use only its own database — so a bug or a break-in in one app can't read or wipe the others.

sudo mariadb -e "SELECT user, host FROM mysql.user"

+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| mariadb.sys | localhost |
| mysql       | localhost |
| root        | localhost |
+-------------+-----------+

A MariaDB user is a name and a host: 'shop'@'localhost' may log in only from this server.

A user for the shop

sudo mariadb -e "CREATE USER 'shop'@'localhost' IDENTIFIED BY 'S3cret-Pass'"
sudo mariadb -e "GRANT ALL PRIVILEGES ON shop.* TO 'shop'@'localhost'"
sudo mariadb -e "SHOW GRANTS FOR 'shop'@'localhost'"

| GRANT USAGE ON *.* TO `shop`@`localhost` IDENTIFIED BY PASSWORD '*0215…' |
| GRANT ALL PRIVILEGES ON `shop`.* TO `shop`@`localhost`                   |

shop.* is every table in the database shop — and nothing else. Stricter still: GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* … — the app may work with rows, but not drop tables. (MariaDB keeps only a hash of the password.)

Logging in as it

mariadb -u shop -p shop

-p asks for the password. -pPASSWORD (no space!) takes it from the command line — handy in examples, but it lands in your shell's history:

mariadb -u shop -pS3cret-Pass shop -e "SELECT * FROM products"
mariadb -u shop -pS3cret-Pass mysql

ERROR 1044 (42000): Access denied for user 'shop'@'localhost' to database 'mysql'

A password file: ~/.my.cnf

Scripts shouldn't carry passwords in their text. The client reads them from ~/.my.cnf:

[client]
user=shop
password=S3cret-Pass

chmod 600 ~/.my.cnf
mariadb shop -e "SELECT COUNT(*) FROM products"

chmod 600 — the password is yours alone.

Changing and removing