Module 1 · The server

Databases, users & privileges

Beginner 16 min read Least privilege, practically

Every application connecting as root is one SQL-injection bug away from dropping every database on the server. MySQL's account model is slightly unusual — an account is a user plus a host — and once you understand that, setting up safe, minimal access is straightforward.

1. Databases = schemas

CREATE DATABASE retail CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
SHOW DATABASES;
USE retail;
DROP DATABASE IF EXISTS scratch;      -- deletes every table inside. No undo.

Unlike Postgres, MySQL has no separate schema level inside a database: retail.orders is database retail, table orders, and you can join across databases on the same server with that dotted name.

2. An account is user@host

'app'@'10.0.1.%' from the app subnet only SELECT, INSERT, UPDATE, DELETE on retail.* ✓ least privilege 'app'@'localhost' a DIFFERENT account own password, own privileges (may not exist at all) 'app'@'%' from ANY host convenient, and the widest attack surface avoid in production When several accounts could match, the MOST SPECIFIC host wins A login from 10.0.1.7 matches 'app'@'10.0.1.%' before 'app'@'%'. "Access denied" with the right password usually means a different account matched.
Same user name, different accounts. This is the source of most "but I granted it!" confusion — the grant went to one account and the login matched another. 
CREATE USER 'app'@'10.0.1.%'      IDENTIFIED BY 'a-long-random-secret';
CREATE USER 'analyst'@'%'         IDENTIFIED BY '…' PASSWORD EXPIRE INTERVAL 90 DAY;
CREATE USER 'backup'@'localhost'  IDENTIFIED BY '…';

SELECT user, host, plugin, account_locked FROM mysql.user;
ALTER USER 'analyst'@'%' ACCOUNT LOCK;
DROP USER 'analyst'@'%';
Authentication plugins

MySQL 8 defaults to caching_sha2_password. Some old client libraries only speak the legacy mysql_native_password, which is disabled by default in 8.4. The right fix is to upgrade the client library, not to downgrade the server's authentication.

3. GRANT and REVOKE

GRANT SELECT, INSERT, UPDATE, DELETE ON retail.*         TO 'app'@'10.0.1.%';   -- one database
GRANT SELECT                          ON retail.orders    TO 'analyst'@'%';     -- one table
GRANT SELECT (customer_id, city)      ON retail.customers TO 'analyst'@'%';     -- columns
GRANT PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';

SHOW GRANTS FOR 'app'@'10.0.1.%';
REVOKE DELETE ON retail.* FROM 'app'@'10.0.1.%';
AccountNeedsMust NOT have
Web applicationSELECT/INSERT/UPDATE/DELETE on its databaseDROP, ALTER, GRANT, FILE, SUPER, anything on *.*
Migration runner+ CREATE, ALTER, INDEX, DROP on its databaseGlobal privileges
Analyst / BI toolSELECT (ideally on a read replica)Any write privilege
BackupSELECT, LOCK TABLES, RELOAD, PROCESS, REPLICATION CLIENTWrite privileges
Never let the application use root

If the app's account can only modify rows in one database, an injection bug can at worst damage that data — not drop tables, read mysql.user or write files to disk (FILE privilege). Separating the migration account from the runtime account means the running app cannot even ALTER its own tables.

4. Roles (MySQL 8)

CREATE ROLE 'retail_read', 'retail_write';
GRANT SELECT                          ON retail.* TO 'retail_read';
GRANT INSERT, UPDATE, DELETE          ON retail.* TO 'retail_write';

GRANT 'retail_read', 'retail_write' TO 'app'@'10.0.1.%';
SET DEFAULT ROLE ALL TO 'app'@'10.0.1.%';     -- ⚠ roles are NOT active on login otherwise
The role gotcha

Granting a role does not activate it. Without SET DEFAULT ROLE (or activate_all_roles_on_login=ON), the user logs in with no effective privileges and gets "access denied" despite SHOW GRANTS listing the role.

Recap

  • Database = schema in MySQL; cross-database joins use db.table.
  • An account is 'user'@'host'; the most specific host match wins.
  • Grant the minimum, at the narrowest scope — separate runtime, migration, analyst and backup accounts.
  • Roles bundle privileges — remember SET DEFAULT ROLE.
  • Keep caching_sha2_password; upgrade old clients instead.

Checkpoint

1 · You granted SELECT to 'report'@'%', but report connecting from the server itself gets "access denied". Why?
MySQL picks the most specific matching account. A local login can match a localhost-specific account with a different password or no privileges. Check SELECT CURRENT_USER() after connecting — it shows which account actually matched. (FLUSH PRIVILEGES is not needed after GRANT.)
2 · Which privilege set is appropriate for a production web application account?
Least privilege limits the blast radius of a compromise or bug. Schema changes belong to a separate migration account used only during deploys.