Remove Permissions for a MySQL User on Linux via Command Line

Reading Time: 2 minutes     

• 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;

Series Navigation

<< Previous ArticleNext Article >>