Least-Privilege Accounts

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 '%'):

shop_app gets DML on shop and nothing elseSQL

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';

Output

+----------------------------------------------------------------------------+
| Grants for shop_app@localhost                                              |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `shop_app`@`localhost`                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `shop`.* TO `shop_app`@`localhost` |
+----------------------------------------------------------------------------+

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

What an injected query can no longer doSQL

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'"

Output

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

Shop data is still exposed to a hole, but accounts, schema, files and other databases are not. Give other jobs their own accounts:

One account per job
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

Where a page needs more, such as placing an order, grant EXECUTE on one SQL SECURITY DEFINER procedure (Stored Procedures) rather than widening shop_app, which Databases with PDO uses from PHP.

MENU

Least-Privilege Accounts