Remove Permissions for a MySQL User on Linux via Command Line

Remove Permissions for a MySQL User on Linux via Command Line

Pre-Flight Check

  • These directions area unit meant for revoking a MySQL user permissions on UNIX via the program line.
  • I’ll be performing from a Liquid net Core Managed CentOS half-dozen.5 server, and I’ll be logged in as root.

Login to MySQL

First we’ll login to the MySQL server from the program line with the following command:

mysql -u root -p

In this case, I’ve fixed the user root with the -u flag, so used the -p flag so MySQL prompts for a word. Enter your current word to complete the login.

If you would like to alter your root (or any other) word within the info, then follow this tutorial on ever-changing a word for MySQL via the command.

You should currently be at a MySQL prompt that appears terribly almost like this:


If you haven’t yet created a MySQL user, please refer to our tutorial on creating a MySQL user.

View Grants for MySQL User

Use the following command to check the grants for the user testuser :

SHOW GRANTS FOR 'testuser'@'localhost';

Revoke Permissions to MySQL User

The basic syntax for revoking permissions is as follows:

REVOKE permission ON database.table FROM 'user'@'localhost';

Here is a brief list of unremarkably used permissions :

ALL – permit complete access to a particular info. If a info isn’t given, then permit complete access to the whole lot of MySQL.

CREATE – permit a user to make databases and tables.

DELETE – permit a user to delete rows from a table.

DROP – permit a user to drop databases and tables.

EXECUTE – permit a user to execute hold on routines.

GRANT possibility – permit a user to grant or take away another user’s privileges.

INSERTpermit a user to insert rows from a table.

SELECT – permit a user to pick information from a info.

SHOW DATABASES– permit a user to look at an inventory of all databases.

UPDATE – permit a user to update rows in a table.

Example 1: To revoke produce permissions for all databases * and every one tables * from the user we created in a previous tutorial, testuser, use the following command:

REVOKE CREATE ON *.* FROM 'testuser'@'localhost';

Using Associate in Nursing asterisk (*) within the place of the information or table is a totally valid possibility, and implies all databases or all tables.

Example 2: To revoke testuser the flexibility to drop tables within the specific information, tutorial_database , use the DROP permission:

REVOKE DROP ON tutorial_database.* FROM 'testuser'@'localhost';


If the specified user does not have the specified permission, then you will receive an error. Be sure to use the SHOW GRANTS command, as demonstrated above, to see what permissions are granted.

When finished making your permission changes, it’s good practice to reload all the privileges with the flush command!


About the author

Wikitechy Editor

Wikitechy Founder, Author, International Speaker, and Job Consultant. My role as the CEO of Wikitechy, I help businesses build their next generation digital platforms and help with their product innovation and growth strategy. I'm a frequent speaker at tech conferences and events.

Add Comment

Click here to post a comment