Revoke Privilege in MySQL Database Server

This is an article which is used to describe some steps on revoking privilege from certain user in MySQL Database Server. Sometime, after granting some privileges to a certain user exists in MySQL Database Server, it is needed to be revoked.

In other context, there is also a chance that the privileges given is being abused or not approprivate to be given. Below is the step which is needed to be done in order to revoke privileges :

1. Check MySQL Database Service’s server. If it is active or not, please refer to this article titled ‘Check MySQL Service Status‘.

2. Try to connect to the running MySQL Database Server. Read the article for a little insight which is titled ‘MySQL Database Access from Command Line in Linux‘.

3. Execute the revoke command.

So, in order to revoke it, below is the query syntax which actually can be executed :

revoke privilege_name on database_name.* from user_name
Description : 
revoke : It is the command used to remove, erase or delete privilege which has been granted to a certain user for a certain database element. 
privilege_name : It is the parameter of revoke command
database_name.* : It is the database which also included all the tables exist inside the database represent with the '*' wildcard sign.
user_name : It is the user name whom the privileges are going to be revoked 

Below is the query command syntax which is executed as an example :

mysql> revoke all on komandansikd.* from admin;
ERROR 1141 (42000): There is no such grant defined for user 'admin' on host '%'
mysql> revoke all on komandansikd.* from 'admin'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'admin' on host '%'

4. Since there is no grant defined for privileges which is going to be revoked, one or another way to do it by listing the privileges before by executing the following command :

mysql> show grants for 'admin'@'%';

After previewing list of privileges, it can be automatically revoked by executing the following query as an example :

mysql> revoke all privileges on database_name.* to 'admin'@'%';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to 'admin'@'%'' at line 1
mysql> revoke all privileges on database_name.* from 'admin'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql>

As shown in the above output, the revoke privileges command has already successfully executed.