How to Revoke and Re-grant Permissions for an in-use MySQL User We have a user which (wrongly) has global privileges (i.e. *.* rather then database specific).

We would like to restrict this user to one database, so my approach would normally be to REVOKE ALL and then GRANT per-database.

However, the account is currently being used by a live application, so we ideally need to preserve access for the required database while removing others.

My question is twofold: 1) Will it even work? and 2) will the below SQL do the job?

GRANT ALL ON mydb.* to 'myuser'@'%';
REVOKE ALL PRIVILEGES ON *.* FROM 'mysuer'@'%';
FLUSH PRIVILEGES;
I hope It should work, Please have a look at following 

GRANT ALL ON *.* TO 'myuser'@'%';
Query OK, 0 rows affected (0.00 sec)

show grants for 'myuser'@'%'\G
*************************** 1. row ***************************
Grants for myuser@%: GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%'
1 row in set (0.00 sec)


Above shows that all privileges are granted to myuser on all databases.Now grant privileges for single db.

GRANT ALL ON mydb.* to 'myuser'@'%';
Query OK, 0 rows affected (0.00 sec)

show grants for 'myuser'@'%'\G
*************************** 1. row ***************************
Grants for myuser@%: GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%'
*************************** 2. row ***************************
Grants for myuser@%: GRANT ALL PRIVILEGES ON `mydb`.* TO 'myuser'@'%'
2 rows in set (0.00 sec)


Now revoke unnecessary privleges

REVOKE ALL PRIVILEGES ON *.* FROM 'myuser'@'%';
Query OK, 0 rows affected (0.00 sec)


Now check which privileges still exits

show grants for 'myuser'@'%'\G
*************************** 1. row ***************************
Grants for myuser@%: GRANT USAGE ON *.* TO 'myuser'@'%'
*************************** 2. row ***************************
Grants for myuser@%: GRANT ALL PRIVILEGES ON `mydb`.* TO 'myuser'@'%'
2 rows in set (0.00 sec)


That's what you need !!