Remove Permissions for a MySQL User on Linux via Command Line

• These instructions are intended for revoking a MySQL user permissions on Linux via the command line
• I’ll be working from a Liquid Web Core Managed CentOS 6.5 server, and I’ll be logged in as root.

First we’ll log in to the MySQL server from the command line with the following command:

mysql -u root -p

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

If you need to change your root (or any other) password in the database, then follow this tutorial on changing a password for MySQL via the command line.

You should now be at a MySQL prompt that looks very similar to this:

mysql>

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

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

SHOW GRANTS FOR 'testuser'@'localhost';

The basic syntax for revoking permissions is as follows:

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

Here is a short list of commonly used permissions :

• ALL – Allow complete access to a specific database. If a database is not specified, then allow complete access to the entirety of MySQL.
• CREATE – Allow a user to create databases and tables.
• DELETE – Allow a user to delete rows from a table.
• DROP – Allow a user to drop databases and tables.
• EXECUTE – Allow a user to execute stored routines.
• GRANT OPTION – Allow a user to grant or remove another user’s privileges.
• INSERT – Allow a user to insert rows from a table.
• SELECT – Allow a user to select data from a database.
• SHOW DATABASES- Allow a user to view a list of all databases.
• UPDATE – Allow a user to update rows in a table.

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

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

Using an asterisk (*) in the place of the database or table is a completely valid option, and implies all databases or all tables.

Example #2: To revoke testuser the ability to drop tables in the specific database, tutorial_database , use the DROP permission:

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

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

FLUSH PRIVILEGES;