How to Take Rights Away from a MySQL Account with REVOKE
REVOKE takes a right away, and you don't need FLUSH PRIVILEGES after it. The surprise is timing: a session that's already open can keep a database-level right until it next runs USE. I tested it in two sessions, and with a running app.
Step 1: Take Rights Away
As root. This takes everything on one database, then gives back what the account should have:
REVOKE ALL PRIVILEGES ON inventory.* FROM 'inventory_app'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON inventory.* TO 'inventory_app'@'%';
SHOW GRANTS FOR 'inventory_app'@'%';
Query OK, 0 rows affected (0.002 sec)
Query OK, 0 rows affected (0.003 sec)
+------------------------------------------------------------------------------+
| Grants for inventory_app@% |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `inventory_app`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `inventory`.* TO `inventory_app`@`%` |
+------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
For a live app, there's a moment between those two statements when it has nothing. If that matters, revoke by name instead, leaving the four in place: REVOKE DROP, CREATE, ALTER, INDEX, REFERENCES ON inventory.* FROM ….
A REVOKE has to match how the right was given. A right given on inventory.* can't be taken back table by table.
Step 2: See When It Takes Effect
I opened a session as the app, and used the database:
USE inventory;
SELECT COUNT(*) FROM categories;
Then, as root, in another terminal:
REVOKE SELECT ON inventory.* FROM 'inventory_app'@'%';
Back in the app's session:
mysql> USE inventory;
Database changed
mysql> SELECT COUNT(*) FROM categories;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.000 sec)
mysql> SELECT COUNT(*) FROM categories;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.000 sec)
mysql> USE inventory;
Database changed
mysql> SELECT COUNT(*) FROM categories;
ERROR 1142 (42000): SELECT command denied to user 'inventory_app'@'localhost' for table 'categories'
mysql> SELECT COUNT(*) FROM categories;
ERROR 1142 (42000): SELECT command denied to user 'inventory_app'@'localhost' for table 'categories'
The first SELECT after the REVOKE still works. After USE, it doesn't. That's what the manual says: "Database privilege changes take effect the next time the client executes a USE" statement. And "Table and column privilege changes take effect with the client's next request."
It works the same way in reverse. The last line above is after GRANT SELECT as root: that session stayed refused until its next USE, and then it could read again.
| Kind of change | When it applies to an open session |
|---|---|
| On a table or a column | The next statement |
On a database (inventory.*) | The next USE |
| Static global rights, and passwords | Not at all: new sessions only |
With a Connection Pool
With .NET's MySql.Data, the driver sends the equivalent of USE each time it takes a connection from the pool. So a running API felt this immediately: after REVOKE SELECT ON inventory.*, the very next request failed with SELECT command denied, on the same pooled connection. After a GRANT, the next one worked.
A changed password is different: pooled connections carried on working, and the failure only came at the next new connection.
You Don't Need FLUSH PRIVILEGES
REVOKE and GRANT update what's in memory by themselves. The manual says the server "loads the grant tables into memory again immediately". FLUSH PRIVILEGES is for changes made directly to the tables with INSERT or UPDATE, which you shouldn't do.
Common Mistakes
| Mistake | What happens | Do this instead |
|---|---|---|
| Testing in a session that's already open | It still looks allowed | Open a new one, or run USE |
REVOKE on a table when the right is on the database | ERROR 1147 (42000): There is no such grant defined … on table 'categories' | Revoke at the level it was given |
Editing mysql.db directly | No effect until a flush | Use REVOKE |
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
ERROR 1141 … no such grant defined | The right isn't there at that level | Check SHOW GRANTS |
| The app still works after a revoke | It has another grant, or its session hasn't run USE | Check, and restart it |
| The app stops working altogether | You revoked more than you meant | GRANT it back |
Conclusion
REVOKE takes effect without a flush. For a database-level right, an open session keeps it until its next USE, so check from a new session.
Additional Resources
- REVOKE Statement — every form.
- When Privilege Changes Take Effect — the rules quoted here.
