Chapter 3
Chapter 3 — Users and Privileges
Every database-backed application needs to connect to MySQL. What account it uses and what that account is permitted to do is one of the most consequential security decisions in your stack. This chapter covers creating dedicated application users, understanding MySQL's privilege system, and applying the principle of least privilege — giving each account exactly what it needs, nothing more.
What this chapter covers: Why application code must never connect as root. MySQL's three-level privilege system. The host field — what
localhost, 127.0.0.1, and % actually mean (and the PHP gotcha that breaks connections). CREATE USER and authentication plugins. GRANT — the key privilege sets every administrator needs to know. Complete walkthrough: creating a web app user for a database. SHOW GRANTS auditing. REVOKE, DROP USER, and ALTER USER for password changes and account control. Seeing who is connected with SHOW PROCESSLIST. Troubleshooting.
Why Application Code Must Never Connect as Root
It's tempting to use the root account in your PHP or Python connection string — it always works, it never hits a permissions error, and it removes a whole class of debugging. It's also a critical mistake.
- SQL injection amplification: If an attacker finds an injection point in your application, the root account gives them unrestricted access to every database on the server — not just yours. They can read other users' data, drop databases, or create new admin accounts.
- Credential exposure: Your application's database credentials live in a config file. If that file is ever exposed (misconfigured web server, Git accident, server compromise), a leaked root password is catastrophic. A leaked app user password with limited grants is contained.
- No audit trail: Everything looks like root in the logs. You can't distinguish what your WordPress site did from what your custom app did.
- Accidental destruction: A bug in your code that runs
DROP TABLEas root works. As a constrained app user without DDL privileges, it fails safely.
The rule is simple: one database, one dedicated user, minimal privileges. Root is for administration from the terminal only.
How MySQL's Privilege System Works
MySQL has three tiers of privilege scope. Each GRANT statement applies to exactly one tier — and the tier is determined by what you write after ON.
Privilege scope — determined by the ON clause in GRANT:
GRANT SELECT ON *.* ← GLOBAL — applies to every database on this server
GRANT SELECT ON bookshop.* ← DATABASE — applies to every table in bookshop
GRANT SELECT ON bookshop.books ← TABLE — applies to one specific table
Scope check order: when a connection runs a query, MySQL checks from most
specific to most general and applies the highest-matching privilege set.
┌──────────────────────────────────┐
│ Global level (*.*) │ ALL PRIVILEGES — full server control
│ ┌───────────────────────────┐ │
│ │ Database level (db.*) │ │ CREATE, DROP, and object privileges on one db
│ │ ┌─────────────────────┐ │ │
│ │ │ Table level (db.t) │ │ │ SELECT, INSERT, UPDATE, DELETE on one table
│ │ └─────────────────────┘ │ │
│ └───────────────────────────┘ │
└──────────────────────────────────┘
The Host Field — The Most Misunderstood Part of MySQL Users
A MySQL user account is not just a username. It's a username + host pair. 'app'@'localhost' and 'app'@'127.0.0.1' are completely separate accounts — they can have different passwords and different privileges. Getting this wrong is the single most common cause of "access denied" errors when connecting from PHP.
'localhost'
Connections via the Unix socket file (
/var/run/mysqld/mysqld.sock). Only from the local machine. No network involved.Use for: local PHP/Python apps connecting via socket
'127.0.0.1'
Connections via TCP loopback. Local machine only but goes through the network stack. Different from socket auth.
Use for: tools that force TCP (e.g. some JDBC drivers)
'%'
Wildcard — any host. Accepts connections from anywhere on the network. Never use for privileged accounts.
Dangerous — avoid unless explicitly needed
'192.168.1.%'
Subnet wildcard — any host on this subnet. Useful for allowing connections from an internal LAN only.
Use for: LAN-restricted remote access
'192.168.1.10'
A specific IP address. Most restrictive remote access pattern — only one known machine can connect.
Use for: locked-down remote admin access
The PHP host=localhost gotcha: In PHP's PDO or mysqli,
host=localhost in the connection string means "use the Unix socket" — which matches a MySQL account with @'localhost'. But host=127.0.0.1 means "use TCP" — which matches @'127.0.0.1' or @'%', but not @'localhost'. If you create 'app'@'localhost' but your PHP DSN says host=127.0.0.1, you'll get Access denied even though the user exists. Either make your DSN and user host consistent, or create both 'app'@'localhost' and 'app'@'127.0.0.1' with the same grants.
Creating Users
# Basic syntax
mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'StrongPassword!9';
# Create a web application user (socket/local connection)
mysql> CREATE USER 'bookshop_app'@'localhost' IDENTIFIED BY 'Bk$h0p!2026';
# Create a read-only reporting user
mysql> CREATE USER 'bookshop_ro'@'localhost' IDENTIFIED BY 'R3adOnly!55';
# Create a user for a specific remote IP (e.g. a dev workstation)
mysql> CREATE USER 'philip'@'192.168.1.50' IDENTIFIED BY 'Dev!Access7';
# Create a user and immediately lock the account (create now, activate later)
mysql> CREATE USER 'future_user'@'localhost' IDENTIFIED BY 'Pass!word9' ACCOUNT LOCK;
# Check the user was created
mysql> SELECT user, host, plugin FROM mysql.user WHERE user = 'bookshop_app';
+--------------+-----------+-----------------------+
| user | host | plugin |
+--------------+-----------+-----------------------+
| bookshop_app | localhost | caching_sha2_password |
+--------------+-----------+-----------------------+
MySQL 8.0 change:
GRANT no longer creates users. In MySQL 5.x, you could write GRANT SELECT ON db.* TO 'user'@'host' IDENTIFIED BY 'pass' and it would create the user if they didn't exist. MySQL 8.0 removed this — you must CREATE USER first, then GRANT. If you use an old tutorial that combines both, it will fail with ERROR 1410: You are not allowed to create a user with GRANT.
Authentication plugins: caching_sha2_password vs mysql_native_password
caching_sha2_password (MySQL 8.0 default)
Stronger security — uses SHA-256 with salting and caching for performance.
Supported by: PHP 7.4+ with mysqlnd, PHP 8.x, Python mysql-connector 8.0+, MySQL Workbench, modern drivers.
Use this unless you hit a compatibility error.
Supported by: PHP 7.4+ with mysqlnd, PHP 8.x, Python mysql-connector 8.0+, MySQL Workbench, modern drivers.
Use this unless you hit a compatibility error.
mysql_native_password (legacy)
Older SHA-1 based authentication. Required for older PHP versions with libmysqlclient (not mysqlnd), legacy Laravel setups, some Java JDBC drivers.
Error that tells you to switch:
Error that tells you to switch:
PHP Warning: The server requested authentication method unknown to the client [caching_sha2_password]
CREATE USER 'app'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Pass!';
# Switch an existing user to mysql_native_password (for old PHP compatibility)
mysql> ALTER USER 'bookshop_app'@'localhost'
-> IDENTIFIED WITH mysql_native_password BY 'Bk$h0p!2026';
Query OK, 0 rows affected (0.01 sec)
# Or create with the legacy plugin from the start
mysql> CREATE USER 'legacy_app'@'localhost'
-> IDENTIFIED WITH mysql_native_password BY 'LegacyP@ss7';
Granting Privileges
A freshly created user can connect but can't do anything — they can't even see databases. Privileges are granted separately. The GRANT statement specifies what operations the user can perform and on what scope.
# Syntax
mysql> GRANT privilege_list ON scope TO 'user'@'host';
# Grant SELECT only (read-only user)
mysql> GRANT SELECT ON bookshop.* TO 'bookshop_ro'@'localhost';
# Grant typical web app privileges (read + write data, no schema changes)
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON bookshop.* TO 'bookshop_app'@'localhost';
# Grant full control of one database (but not other databases)
mysql> GRANT ALL PRIVILEGES ON bookshop.* TO 'bookshop_admin'@'localhost';
# Grant on a single table only
mysql> GRANT SELECT ON bookshop.books TO 'catalogue_reader'@'localhost';
# Privileges take effect immediately — no need to FLUSH PRIVILEGES
# (FLUSH is only needed for direct mysql.user table edits)
GRANT ALL PRIVILEGES ON bookshop.* vs GRANT ALL PRIVILEGES ON *.* — the difference is enormous.
bookshop.* gives full control of one database. *.* gives full control of the entire server — equivalent to root. Always scope grants to a specific database for application accounts.
The privilege sets you'll actually use
Read-only
SELECT
Reporting tools, analytics dashboards, data exports
Web app (typical)
SELECT, INSERT, UPDATE, DELETE
WordPress, Joomla, custom PHP apps — data manipulation only
Web app (with schema)
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER
Frameworks that run migrations (Laravel, Django) — add REFERENCES, CREATE TEMPORARY TABLES if needed
Backup account
SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER
mysqldump requires these — CREATE is not needed for backups
GRANT OPTION — never
WITH GRANT OPTION
Lets user pass their own privileges to others — an instant privilege escalation path. Never grant to application accounts
Walkthrough: Setting Up a Web Application Database and User
Practical Walkthrough · Creating the bookshop Application User
Create a database, a dedicated user, grant appropriate privileges, and verify everything works.
1
Connect as root and create the application database.
$ sudo mysql
mysql> CREATE DATABASE IF NOT EXISTS bookshop
-> CHARACTER SET utf8mb4
-> COLLATE utf8mb4_unicode_ci;
Query OK, 1 row affected (0.01 sec)
Using utf8mb4 (not utf8) — MySQL's utf8 is a 3-byte encoding that can't handle emoji and some CJK characters. utf8mb4 is the real 4-byte UTF-8.
2
Create a dedicated application user. Use
localhost if your PHP app connects via the Unix socket (the most common setup — DSN with host=localhost).
mysql> CREATE USER 'bookshop_app'@'localhost'
-> IDENTIFIED BY 'Bk$h0p!2026';
Query OK, 0 rows affected (0.01 sec)
3
Grant the privileges the application actually needs. This app reads and writes data but doesn't need to create or drop tables.
mysql> GRANT SELECT, INSERT, UPDATE, DELETE
-> ON bookshop.*
-> TO 'bookshop_app'@'localhost';
Query OK, 0 rows affected (0.01 sec)
4
Verify the grants are correct.
mysql> SHOW GRANTS FOR 'bookshop_app'@'localhost';
+-----------------------------------------------------------------------+
| Grants for bookshop_app@localhost |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `bookshop_app`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `bookshop`.* TO `bookshop_app`@`localhost` |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)
The first row (GRANT USAGE ON *.*) is automatic — it means "user can connect but has no global privileges." The second row shows the database-level grants. This is exactly right.
5
Test the connection as the new user. Open a new terminal (don't close your root session yet).
$ mysql -u bookshop_app -p
Enter password: Bk$h0p!2026
Welcome to the MySQL monitor. ...
# This user can only see the bookshop database (and information_schema)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| bookshop |
| information_schema |
+--------------------+
# Confirm the user can't drop the database (principle of least privilege working)
mysql> DROP DATABASE bookshop;
ERROR 1044 (42000): Access denied for user 'bookshop_app'@'localhost' to database 'bookshop'
6
Store the credentials in your PHP config outside the web root.
# /etc/bookshop/db.php (outside /var/www — not web-accessible)
<?php
define('DB_HOST', 'localhost');
define('DB_NAME', 'bookshop');
define('DB_USER', 'bookshop_app');
define('DB_PASS', 'Bk$h0p!2026');
# PDO connection using these constants
$dsn = 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME . ';charset=utf8mb4';
$pdo = new PDO($dsn, DB_USER, DB_PASS, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
Chapter 8 covers securing database credentials in PHP applications in more detail, including keeping config files outside the web root and using environment variables.
Auditing Privileges with SHOW GRANTS
# Show grants for a specific user
mysql> SHOW GRANTS FOR 'bookshop_app'@'localhost';
# Show grants for the currently connected user
mysql> SHOW GRANTS;
# Audit ALL users and their privilege level in one query
mysql> SELECT
-> u.user,
-> u.host,
-> u.plugin,
-> u.account_locked,
-> u.password_expired
-> FROM mysql.user u
-> WHERE u.user NOT LIKE 'mysql.%'
-> ORDER BY u.user, u.host;
+--------------+-----------+-----------------------+----------------+------------------+
| user | host | plugin | account_locked | password_expired |
+--------------+-----------+-----------------------+----------------+------------------+
| bookshop_app | localhost | caching_sha2_password | N | N |
| bookshop_ro | localhost | caching_sha2_password | N | N |
| root | localhost | auth_socket | N | N |
+--------------+-----------+-----------------------+----------------+------------------+
# See database-level privileges across all users
mysql> SELECT user, host, db, Select_priv, Insert_priv, Update_priv, Delete_priv
-> FROM mysql.db
-> ORDER BY db, user;
+--------------+-----------+----------+-------------+-------------+-------------+-------------+
| user | host | db | Select_priv | Insert_priv | Update_priv | Delete_priv |
+--------------+-----------+----------+-------------+-------------+-------------+-------------+
| bookshop_app | localhost | bookshop | Y | Y | Y | Y |
| bookshop_ro | localhost | bookshop | Y | N | N | N |
+--------------+-----------+----------+-------------+-------------+-------------+-------------+
Revoking Privileges
# Revoke a specific privilege (user keeps all others)
mysql> REVOKE DELETE ON bookshop.* FROM 'bookshop_app'@'localhost';
Query OK, 0 rows affected (0.01 sec)
# Revoke ALL privileges on a specific database
mysql> REVOKE ALL PRIVILEGES ON bookshop.* FROM 'bookshop_app'@'localhost';
Query OK, 0 rows affected (0.01 sec)
# Revoke ALL privileges at ALL levels (leaves the user account intact but powerless)
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'bookshop_app'@'localhost';
Query OK, 0 rows affected (0.01 sec)
# Confirm what remains
mysql> SHOW GRANTS FOR 'bookshop_app'@'localhost';
+------------------------------------------------------+
| Grants for bookshop_app@localhost |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `bookshop_app`@`localhost` |
+------------------------------------------------------+
# Only USAGE remains — user can connect but cannot do anything
REVOKE affects currently-connected sessions. If
bookshop_app is already connected when you revoke privileges, MySQL checks grants at query execution time, not at connection time. The running session will immediately lose the revoked permissions on their next query. You don't need to kick them off first.
Modifying and Removing Users
# ── Changing passwords ────────────────────────────────────────────
# Change another user's password (as root)
mysql> ALTER USER 'bookshop_app'@'localhost' IDENTIFIED BY 'NewP@ssword!8';
Query OK, 0 rows affected (0.01 sec)
# Change your own password (as any logged-in user)
mysql> ALTER USER USER() IDENTIFIED BY 'MyNewPass!7';
# ── Account locking and unlocking ────────────────────────────────
# Lock an account temporarily (connection attempts will fail)
mysql> ALTER USER 'bookshop_app'@'localhost' ACCOUNT LOCK;
Query OK, 0 rows affected (0.00 sec)
# Unlock it again
mysql> ALTER USER 'bookshop_app'@'localhost' ACCOUNT UNLOCK;
# ── Password expiry ───────────────────────────────────────────────
# Force a user to change password on next login
mysql> ALTER USER 'bookshop_app'@'localhost' PASSWORD EXPIRE;
# Set a rolling expiry policy (user must change password every 90 days)
mysql> ALTER USER 'bookshop_app'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
# Disable expiry for an account (useful for service accounts)
mysql> ALTER USER 'bookshop_app'@'localhost' PASSWORD EXPIRE NEVER;
# ── Removing users ────────────────────────────────────────────────
# Drop a user and all their privileges in one step
mysql> DROP USER 'bookshop_app'@'localhost';
Query OK, 0 rows affected (0.00 sec)
# Safe version — no error if the user doesn't exist
mysql> DROP USER IF EXISTS 'bookshop_app'@'localhost';
DROP USER removes the user and revokes all their privileges atomically. You don't need to REVOKE first. The user's objects (tables, views, stored procedures they created) are NOT dropped — only the account is removed.
Seeing Who Is Connected — SHOW PROCESSLIST
# See all active connections and what they're doing
mysql> SHOW PROCESSLIST;
+----+--------------+-----------+----------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+--------------+-----------+----------+---------+------+----------+------------------+
| 1 | event_scheduler | localhost | | Daemon | 1024 | Waiting | NULL |
| 8 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
| 11 | bookshop_app | localhost | bookshop | Sleep | 42 | | NULL |
+----+--------------+-----------+----------+---------+------+----------+------------------+
3 rows in set (0.00 sec)
# Terminate a specific connection (use the Id column)
mysql> KILL 11;
Query OK, 0 rows affected (0.00 sec)
# A more detailed view via the sys schema
mysql> SELECT user, host, db, command, time, state, info
-> FROM sys.processlist
-> WHERE command != 'Sleep'
-> ORDER BY time DESC;
Use SHOW PROCESSLIST when you need to identify runaway queries, check which accounts are currently active, or confirm a connection test worked. The Time column shows how many seconds the connection has been in its current state — a very high number on a non-Sleep query indicates a stuck or slow query.
Troubleshooting
ERROR 1045 (28000): Access denied for user 'bookshop_app'@'127.0.0.1'
The user exists as
'bookshop_app'@'localhost' but your PHP DSN says host=127.0.0.1, which makes MySQL see a TCP connection from 127.0.0.1 — a different host string than localhost. Either change your PHP DSN to host=localhost, or create a second account: CREATE USER 'bookshop_app'@'127.0.0.1' IDENTIFIED BY 'same_password'; then grant the same privileges to it.
PHP Warning: mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password]
Your PHP/mysqli version is too old to speak
caching_sha2_password. Fix by switching the user to the legacy plugin: ALTER USER 'bookshop_app'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourPassword!';. Long-term fix: upgrade PHP to 7.4+ compiled with mysqlnd (not libmysqlclient). PHP 8.x + mysqlnd handles caching_sha2_password natively.
ERROR 1044 (42000): Access denied for user 'bookshop_app'@'localhost' to database 'bookshop'
The user was created but the
GRANT either didn't run or used a different host string. Check: SHOW GRANTS FOR 'bookshop_app'@'localhost'; — if it only shows GRANT USAGE ON *.* with no database-level grant, run: GRANT SELECT, INSERT, UPDATE, DELETE ON bookshop.* TO 'bookshop_app'@'localhost';
ERROR 1410: You are not allowed to create a user with GRANT
You tried to use the old MySQL 5.x syntax:
GRANT ... TO 'user'@'host' IDENTIFIED BY 'pass'. MySQL 8.0 removed the ability to create a user implicitly in a GRANT statement. Run CREATE USER first, then GRANT as two separate statements.
User was granted privileges but still gets "Access denied" for specific operations
Check whether the operation requires a privilege you didn't grant. Common missing privileges: CREATE TABLE (framework migrations), LOCK TABLES (mysqldump backups), REFERENCES (foreign keys in some frameworks). Run
SHOW GRANTS FOR 'user'@'host'; and compare against the required privilege. Add missing ones with another GRANT statement — grants are additive and safe to run again.
Quick Reference — Chapter 3
| Command | Purpose |
|---|---|
| CREATE USER 'u'@'h' IDENTIFIED BY 'pass'; | Create a new MySQL user account |
| GRANT SELECT, INSERT, UPDATE, DELETE ON db.* TO 'u'@'h'; | Grant typical web app privileges on one database |
| GRANT ALL PRIVILEGES ON db.* TO 'u'@'h'; | Grant full control of one database (not global) |
| SHOW GRANTS FOR 'u'@'h'; | Display all privileges assigned to a user |
| REVOKE DELETE ON db.* FROM 'u'@'h'; | Remove a specific privilege |
| REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'u'@'h'; | Strip all privileges (user account remains) |
| ALTER USER 'u'@'h' IDENTIFIED BY 'newpass'; | Change a user's password |
| ALTER USER 'u'@'h' ACCOUNT LOCK; | Temporarily prevent logins without deleting the account |
| ALTER USER 'u'@'h' ACCOUNT UNLOCK; | Re-enable a locked account |
| ALTER USER 'u'@'h' IDENTIFIED WITH mysql_native_password BY 'pass'; | Switch to legacy auth plugin (for old PHP compatibility) |
| DROP USER IF EXISTS 'u'@'h'; | Delete a user and all their privileges |
| SHOW PROCESSLIST; | List all active connections and current queries |
| KILL <Id>; | Terminate a specific connection (use Id from SHOW PROCESSLIST) |
| SELECT user, host, plugin FROM mysql.user WHERE user NOT LIKE 'mysql.%'; | Full audit of all non-system accounts |