Revoke all privileges for all users on a MySQL DB

Question

From: http://dev.mysql.com/doc/refman/5.0/en/drop-database.html

...when a database is dropped, user privileges on the database are not automatically dropped.

So the question becomes, how do you revoke all privileges for all users on a MySQL DB? I imagine it's simple, but I'm surprised I haven't been able to find this anywhere.

You can use the [`revoke`](http://dev.mysql.com/doc/refman/5.1/en/revoke.html) command. — ryanprayogo, Mar 13 '11 at 04:16

score 27 · Answer 1 · answered Apr 20 '16 at 14:31

27

REVOKE ALL PRIVILEGES ON *.* FROM '<user_name>'@'localhost';
REVOKE ALL PRIVILEGES ON *.* FROM '<user_name>'@'%';

Eg.:

REVOKE ALL PRIVILEGES ON *.* FROM 'jeffrey'@'localhost';
REVOKE ALL PRIVILEGES ON *.* FROM 'jeffrey'@'%';

answered Apr 20 '16 at 14:31

Iasmini Gomes

727
1
9
14

score 21 · Answer 2 · edited Dec 01 '15 at 16:58

21

You can revoke all privileges for a specific user with this syntax:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
FLUSH PRIVILEGES;

which drops all global, database, table, column, and routine privileges for the named user or users

Not sure if there's a way to do this for all users at once, though.

edited Dec 01 '15 at 16:58

Matt V

113
1
5

answered Mar 13 '11 at 04:18

Matt Ball

354,903
100
647
710

3

important to note, 5.5 at least the ",grant option" is required even though the user didn't possess the grant option – CrackerJack9 Dec 01 '13 at 19:43
Thanks, however it's not clear how to run mentioned command with database, the following fails with SQL error - `revoke all, with grant on $dbs.* from 'user'\@'localhost'`. Any clue? – Ilia Ross Sep 23 '20 at 11:32

score 0 · Answer 3 · edited Apr 27 '13 at 07:45

0

REVOKE ALL PRIVILEGES FROM '%'@'%';

The above could be dangerous as i suppose it will delete all the privileges from all the users including root

Modify it to:

REVOKE ALL PRIVILEGES FROM 'user'@'localhost';

or

REVOKE ALL PRIVILEGES FROM 'user'@'%';

before execute

edited Apr 27 '13 at 07:45

nhahtdh

55,989
15
126
162

answered Apr 27 '13 at 07:08

tkam

39
2
7

1

need ", grant option", at least with 5.5 for revoke all privileges – CrackerJack9 Dec 01 '13 at 19:44
2

This is very very dangerous and not recommended. – MontyPython Aug 17 '17 at 18:53

score -1 · Answer 4 · edited Apr 27 '13 at 07:45

-1

I suppose you can do:

REVOKE ALL PRIVILEGES FROM '%'@'%';
FLUSH PRIVILEGES;

(Don't modify MySQL tables directly)

edited Apr 27 '13 at 07:45

nhahtdh

55,989
15
126
162

answered Nov 02 '12 at 20:23

Federico

125
1
1

need ", grant option", at least with 5.5 for revoke all privileges – CrackerJack9 Dec 01 '13 at 19:44

Revoke all privileges for all users on a MySQL DB

4 Answers4

Linked