MENU


Users, Privileges, Roles, and SQL Injection

Every statement runs as an account and is checked against its privileges. This section builds that access control, then turns to SQL injection, the attack that rewrites your queries, and to the two defenses: server-side prepared statements and a least-privilege account (the PHP side is Section 4.16). The listings change the whole server, so they run in order as root on a mysql:9.7 container of your own (Section 3.1.6) holding shop.

3.16.1 Accounts, Hosts, and Authentication Plugins

An account is a user name plus a host: 'shop_app'@'localhost' and 'shop_app'@'%' are two accounts with separate passwords and privileges. The host is localhost (the Unix socket), an address, a pattern such as '10.0.0.%' or '10.0.0.0/24', or '%' for anywhere, the default. When several accounts match, the most specific host wins. Each account also names the authentication plugin that checks its credentials:

SELECT user, host, plugin, LEFT(authentication_string, 7) AS hash

FROM mysql.user WHERE user NOT LIKE 'mysql.%';

CREATE USER 'legacy'@'%' IDENTIFIED WITH mysql_native_password BY 'Legacy#Pass-2026';

Listing 3.139: The login accounts of a fresh server, and a plugin that is gone

+------+-----------+-----------------------+---------+

| user | host | plugin | hash |

+------+-----------+-----------------------+---------+

| root | % | caching_sha2_password | $A$00A$ |

| root | localhost | caching_sha2_password | $A$00A$ |

+------+-----------+-----------------------+---------+

ERROR 1524 (HY000) at line 3: Plugin 'mysql_native_password' is not loaded

Output of Listing 3.139 $A$00A$ is the caching_sha2_password format, the only default, with 10,000 (hex 00A thousand) rounds of salted SHA-256. mysql_native_password stored an unsalted double SHA-1; it was deprecated in 8.0.34, disabled in 8.4 and removed in 9.0, hence error 1524, so switch old accounts with ALTER USER ... IDENTIFIED WITH caching_sha2_password before upgrading. Ubuntu's root uses auth_socket (Section 3.1.3); sha256_password is deprecated; LDAP, Kerberos and WebAuthn logins are Enterprise Edition only.

3.16.2 CREATE USER, ALTER USER, and Password Policy

CREATE USER makes an account that can log in and do nothing else; ALTER USER takes the same clauses later. The validate_password component (Section 3.1.7) rejects weak passwords server-wide, and per-account clauses set expiry, reuse history, and a block after failed logins:

INSTALL COMPONENT 'file://component_validate_password';

CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'shopapp2026';

CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'App#Shop-2026'

PASSWORD EXPIRE NEVER FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

CREATE USER 'ana'@'localhost' IDENTIFIED BY 'Ana#Pass-2026'

PASSWORD EXPIRE INTERVAL 90 DAY PASSWORD HISTORY 5 PASSWORD REQUIRE CURRENT

FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1;

Listing 3.140: A password policy, an application account and a human account ERROR 1819 (HY000) at line 2: Your password does not satisfy the current policy requirements Output of Listing 3.140 IDENTIFIED BY RANDOM PASSWORD has the server generate one, and REQUIRE SSL demands TLS. An expired application password takes the site down, so shop_app never expires; rotate it with dual passwords instead. RETAIN CURRENT PASSWORD keeps the old password valid beside the new while you deploy it, and DISCARD OLD PASSWORD ends the overlap:

try() { mysql -ushop_app -p"$1" -Nse "SELECT CURRENT_USER()" 2>&1 | grep -v Warning; }

sudo mysql -e "ALTER USER 'shop_app'@'localhost' IDENTIFIED BY 'App#Shop-2027'

RETAIN CURRENT PASSWORD"

try 'App#Shop-2026'; try 'App#Shop-2027'

sudo mysql -e "ALTER USER 'shop_app'@'localhost' DISCARD OLD PASSWORD"

try 'App#Shop-2026'

Listing 3.141: Rotating an application password without downtime

shop_app@localhost

shop_app@localhost

ERROR 1045 (28000): Access denied for user 'shop_app'@'localhost' (using password: YES)

Output of Listing 3.141 try hides the client's warning about passwords on the command line. Retaining one's own password needs APPLICATION_PASSWORD_ADMIN. ana changes hers with ALTER USER USER() IDENTIFIED BY '...' REPLACE 'Ana#Pass-2026', since PASSWORD REQUIRE CURRENT asks for the old one.

3.16.3 Account Locking, Renaming, and Dropping

After ana's third consecutive wrong password the server blocks her for a day, and even the right one fails. ACCOUNT UNLOCK lifts the block; ACCOUNT LOCK shuts an account on purpose:

try() { mysql -uana -p"$1" -Nse "SELECT CURRENT_USER()" 2>&1 | grep -v Warning; }

try wrong1; try wrong2; try wrong3; try 'Ana#Pass-2026'

sudo mysql -e "ALTER USER 'ana'@'localhost' ACCOUNT UNLOCK"; try 'Ana#Pass-2026'

sudo mysql -e "ALTER USER 'ana'@'localhost' ACCOUNT LOCK"; try 'Ana#Pass-2026'

Listing 3.142: Failed logins block an account; ACCOUNT LOCK shuts it

ERROR 1045 (28000): Access denied for user 'ana'@'localhost' (using password: YES)

ERROR 1045 (28000): Access denied for user 'ana'@'localhost' (using password: YES)

ERROR 3955 (HY000): Access denied for user 'ana'@'localhost'. Account is blocked for 1 day(s)

(1 day(s) remaining) due to 3 consecutive failed logins.

ERROR 3955 (HY000): Access denied for user 'ana'@'localhost'. Account is blocked for 1 day(s)

(1 day(s) remaining) due to 3 consecutive failed logins.

ana@localhost

ERROR 3118 (HY000): Access denied for user 'ana'@'localhost'. Account is locked.

Output of Listing 3.142 A lock stays in mysql.user across restarts; open sessions survive it (Section 3.17.3). RENAME USER moves every grant and DROP USER removes them, but neither rewrites the DEFINER of views, routines, triggers or events; MySQL only raises warning 4005:

ALTER USER 'ana'@'localhost' ACCOUNT UNLOCK;

GRANT SELECT ON shop.customers TO 'ana'@'localhost';

CREATE DEFINER = 'ana'@'localhost' VIEW customer_names AS SELECT id, name FROM customers;

RENAME USER 'ana'@'localhost' TO 'ana.souza'@'localhost';

SELECT * FROM customer_names LIMIT 1;

DROP VIEW customer_names;

DROP USER 'ana.souza'@'localhost';

Listing 3.143: Renaming an account orphans the view it defined

ERROR 1449 (HY000) at line 5: The user specified as a definer ('ana'@'localhost') does not

exist

Output of Listing 3.143 Before renaming or dropping a person, find their objects in information_schema (VIEWS, ROUTINES, TRIGGERS, EVENTS by DEFINER) and move them to a service account.

3.16.4 GRANT, REVOKE, and Privilege Levels

GRANT ... ON level TO account adds rights and REVOKE ... FROM removes them at five levels, each in its own grant table: global *.* (mysql.user), database shop.* (mysql.db), table (tables_priv), column (columns_priv) and routine (procs_priv). Both take effect at once, without FLUSH PRIVILEGES. GRANT no longer creates a missing account, so a typo fails, and with partial_revokes on, a global grant can exclude one schema:

CREATE USER 'support'@'localhost' IDENTIFIED BY 'Help#Desk-2026';

GRANT SELECT (id, name, country) ON shop.customers TO 'support'@'localhost';

GRANT SELECT ON shop.orders TO 'suport'@'localhost';

SET PERSIST partial_revokes = ON;

CREATE USER 'auditor'@'localhost' IDENTIFIED BY 'Audit#Read-2026';

GRANT SELECT ON *.* TO 'auditor'@'localhost';

REVOKE SELECT ON mysql.* FROM 'auditor'@'localhost';

SHOW GRANTS FOR 'auditor'@'localhost';

Listing 3.144: Column and global grants, a typo, and a partial revoke

ERROR 1410 (42000) at line 3: You are not allowed to create a user with GRANT

+-------------------------------------------------------+

| Grants for auditor@localhost |

+-------------------------------------------------------+

| GRANT SELECT ON *.* TO `auditor`@`localhost` |

| REVOKE SELECT ON `mysql`.* FROM `auditor`@`localhost` |

+-------------------------------------------------------+

Output of Listing 3.144 support may now read three columns of customers and gets error 1143 for email. A REVOKE must name the level of the grant it undoes, or it fails with error 1141; REVOKE ALL PRIVILEGES, GRANT OPTION FROM ... clears every level.

3.16.5 Static and Dynamic Privileges

Static privileges are built into the server and stored as Y/N columns of the grant tables: SELECT, INSERT, UPDATE, DELETE, the DDL rights, and global ones such as FILE (server files), PROCESS (everyone's SQL) and CREATE USER. Dynamic privileges are registered by the server and its components, are global only, and are rows in mysql.global_grants. They split the deprecated SUPER into narrow rights: CONNECTION_ADMIN to kill others' sessions, SYSTEM_VARIABLES_ADMIN for SET GLOBAL, SYSTEM_USER to shield an account from ordinary admins.

GRANT PROCESS, CONNECTION_ADMIN ON *.* TO 'auditor'@'localhost';

GRANT CONNECTION_ADMIN ON shop.* TO 'auditor'@'localhost';

SELECT user, priv FROM mysql.global_grants WHERE user = 'auditor';

REVOKE PROCESS, CONNECTION_ADMIN ON *.* FROM 'auditor'@'localhost';

Listing 3.145: Dynamic privileges are rows in their own table

ERROR 3619 (HY000) at line 2: Illegal privilege level specified for CONNECTION_ADMIN

+---------+------------------+

| user | priv |

+---------+------------------+

| auditor | CONNECTION_ADMIN |

+---------+------------------+

Output of Listing 3.145 GRANT SUPER still works but raises warning 1287, The SUPER privilege identifier is deprecated. Root on this server holds 42 dynamic privileges, and SHOW PRIVILEGES lists every privilege with its scope.

3.16.6 Roles, Default Roles, and SET ROLE

A role is a locked, named account that holds privileges. Grant privileges to the role and the role to accounts, and one change reaches every holder:

CREATE ROLE 'shop_read', 'shop_write';

GRANT SELECT ON shop.* TO 'shop_read';

GRANT INSERT, UPDATE, DELETE ON shop.* TO 'shop_write';

CREATE USER 'report'@'localhost' IDENTIFIED BY 'Report#Only-2026';

GRANT 'shop_read' TO 'report'@'localhost';

Listing 3.146: A read role, a write role and a reporting account A granted role is inactive until SET ROLE switches it on for the session, or SET DEFAULT ROLE makes it active at every login:

q() { mysql -ureport -p'Report#Only-2026' -Nse "$1" 2>&1 | grep -v Warning; }

q "SELECT CURRENT_ROLE(); SELECT COUNT(*) FROM shop.orders"

q "SET ROLE 'shop_read'; SELECT CURRENT_ROLE(); SELECT COUNT(*) FROM shop.orders"

sudo mysql -e "SET DEFAULT ROLE 'shop_read' TO 'report'@'localhost'"

q "SELECT COUNT(*) FROM shop.orders; UPDATE shop.products SET price = 0"

Listing 3.147: A granted role does nothing until it is active

NONE

ERROR 1142 (42000) at line 1: SELECT command denied to user 'report'@'localhost' for table

'orders'

`shop_read`@`%`

9

9

ERROR 1142 (42000) at line 1: UPDATE command denied to user 'report'@'localhost' for table

'products'

Output of Listing 3.147 The reporting login is read-only by construction. SHOW GRANTS FOR ... USING 'shop_read' previews a role's effect, activate_all_roles_on_login activates every granted role, and mandatory_roles grants one to everybody (off and empty by default). Roles nest: granting shop_read to shop_write makes the writer a superset.

3.16.7 How SQL Injection Works

Injection happens when code pastes input into SQL text: the server cannot tell your characters from the visitor's, so input that closes a string literal adds SQL of its own. Trust no field, cookie or header.Figure 3.13: One template, parsed with honest and hostile inputThis lookup concatenates its argument into SQL and, like too many applications, runs as root:

lookup() { sudo mysql -t shop -e "SELECT id, name FROM customers WHERE email = '$1'"; }

lookup "x' OR '1'='1" | tail -n 2

lookup "x' UNION SELECT user, plugin FROM mysql.user -- " | head -n 6

Listing 3.148: A lookup that builds SQL by concatenation

| 8 | Hana Sato |

+----+---------------+

+------------------+-----------------------+

| id | name |

+------------------+-----------------------+

| root | caching_sha2_password |

| shop_read | caching_sha2_password |

| shop_write | caching_sha2_password |

Output of Listing 3.148 The always-true condition returned all eight customers (the last is shown), and the UNION listed the server's accounts from a table the query never named; -- comments out the leftover quote. When a page shows no results, blind injection asks yes-or-no questions such as ' AND SUBSTR(@@version,1,1) = '9 and reads the answer from the page or from SLEEP(5) delays. Second-order injection stores a payload that fires when later code concatenates it. Escaping quotes is no cure: WHERE id = 1 OR 1=1 needs none. Keep values out of SQL text.

3.16.8 Server-Side Prepared Statements

PREPARE name FROM 'sql' parses a statement once, with ? where values go; EXECUTE name USING @var supplies values that are never parsed; DEALLOCATE PREPARE frees it. The listing replays the tautology through concatenated dynamic SQL, then through a placeholder:

SET @email = "x' OR '1'='1";

SET @sql = CONCAT("SELECT COUNT(*) AS matched FROM customers WHERE email = '", @email, "'");

PREPARE unsafe FROM @sql;

EXECUTE unsafe;

PREPARE lookup FROM 'SELECT COUNT(*) AS matched FROM customers WHERE email = ?';

EXECUTE lookup USING @email;

PREPARE bad FROM 'SELECT COUNT(*) FROM ?';

DEALLOCATE PREPARE lookup;

Listing 3.149: Concatenated dynamic SQL versus a placeholder

+---------+

| matched |

+---------+

| 8 |

+---------+

+---------+

| matched |

+---------+

| 0 |

+---------+

ERROR 1064 (42000) at line 7: You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near '?' at line 1

Output of Listing 3.149 PREPARE alone protects nothing: unsafe was built from text that already held the attack. The placeholder does, because the statement's shape is fixed before any input exists. It also stands only for a value, never a table, column or ASC/DESC, so take those from a whitelist in code and quote them with backticks (sys.quote_identifier() in SQL). Statements belong to their session and are capped by max_prepared_stmt_count (16,382). PHP uses the binary protocol's COM_STMT_PREPARE instead; Subsections 4.16.5 and 4.16.6 show it through PDO.

3.16.9 A Least-Privilege Account for a Web Application

Prepared statements close the hole; privileges limit the damage of one you missed. The web account gets rows in one database and nothing else, from localhost only (an app on another host gets 'shop_app'@'10.0.0.%' with REQUIRE SSL, never '%'):

GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'localhost';

ALTER USER 'shop_app'@'localhost' WITH MAX_USER_CONNECTIONS 50;

SHOW GRANTS FOR 'shop_app'@'localhost';

Listing 3.150: shop_app gets DML on shop and nothing else

+----------------------------------------------------------------------------+

| Grants for shop_app@localhost |

+----------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO `shop_app`@`localhost` |

| GRANT SELECT, INSERT, UPDATE, DELETE ON `shop`.* TO `shop_app`@`localhost` |

+----------------------------------------------------------------------------+

Output of Listing 3.150 Run as shop_app, the attacks of Subsection 3.16.7 stop at the edge of its grants:

app() { mysql -ushop_app -p'App#Shop-2027' shop -e "$1" 2>&1 | grep -v Warning; }

app "SELECT id, name FROM customers WHERE email = 'x'

UNION SELECT user, plugin FROM mysql.user -- '"

app "DROP TABLE reviews"

app "SELECT * FROM customers INTO OUTFILE '/var/lib/mysql-files/c.csv'"

app "CREATE USER 'evil'@'%' IDENTIFIED BY 'Evil#Pass-2026'"

Listing 3.151: What an injected query can no longer do

ERROR 1142 (42000) at line 1: SELECT command denied to user 'shop_app'@'localhost' for table

'user'

ERROR 1142 (42000) at line 1: DROP command denied to user 'shop_app'@'localhost' for table

'reviews'

ERROR 1227 (42000) at line 1: Access denied; you need (at least one of) the FILE privilege(s)

for this operation

ERROR 1227 (42000) at line 1: Access denied; you need (at least one of) the CREATE USER

privilege(s) for this operation

Output of Listing 3.151 Shop data is still exposed to a hole, but accounts, schema, files and other databases are not. Give other jobs their own accounts: Account Privileges Used by shop_app DML on shop.* PHP, every request report Role shop_read Dashboards, exports shop_migrate DML plus CREATE, ALTER, DROP, INDEX Deployments only root via socket Everything A person at the console Table 3.11: One account per job Where a page needs more, such as placing an order, grant EXECUTE on one SQL SECURITY DEFINER procedure (Section 3.15.1) rather than widening shop_app, which Section 4.16 uses from PHP.