Inspect and revoke privileges

Privileges drift. Someone grants extra access during an incident, the incident ends, and the grant stays. Reviewing what each account holds needs to be as routine as reviewing code.

Inspect an account with:

SHOW GRANTS FOR 'notes_app'@'localhost';

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

Read the output as a complete inventory. Anything not listed, the account cannot do. Note that you must name the full account including its host — SHOW GRANTS FOR 'notes_app'@'%' would describe a different account, if it exists at all.

Suppose a review decides the running application should not delete rows anymore. Remove a privilege with REVOKE ... FROM and the same scope used by GRANT:

REVOKE DELETE
ON notes_app.*
FROM 'notes_app'@'localhost';

The scope has to match. Privileges granted on notes_app.* are revoked on notes_app.*; you cannot subtract a single table from a database-level grant.

Prove the change

Run SHOW GRANTS again and confirm DELETE disappeared from the list. Then test both directions from a session connected as notes_app:

SELECT COUNT(*) FROM notes;   -- still works
DELETE FROM notes WHERE id = 1;

The SELECT succeeds and the DELETE fails with ERROR 1142 (42000): DELETE command denied to user 'notes_app'@'localhost' for table 'notes'. That pair of results is the verification: you removed exactly the capability you intended and nothing else.

One catch: an existing connection may keep working with the privileges it had, because some privilege changes only apply to new sessions or the next USE statement. Restart the application or its pool after a revoke so every connection picks up the reduced grants, and re-run the behavioral test through the application itself.

Grant DELETE back only if the running application genuinely needs it. The default answer to “does this account need that privilege?” should be no, backed by the SHOW GRANTS output that proves it.