Remove Permissions for a MySQL User on Linux via Command Line

Login to MySQL

First we’ll login 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.

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

mysql>


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 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 alltables.

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

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


Note: 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!

FLUSH PRIVILEGES;

  • 18 Users Found This Useful
Was this answer helpful?

Related Articles

How to List Compiled PHP Modules from Command Line

Pre-Flight Check These instructions are intended specifically for listing compiled PHP modules...

How to Install or Uninstall PECL Extensions

Pre-Flight Check These instructions are intended specifically for installing or uninstalling...

How to Install the MongoDB PHP Driver (Extension) on CentOS 6

Step 1: Setup Environment, Install PHP Extension & Application Repository (PEAR) As a matter...

How to Add a User and Grant Root Privileges on CentOS 6.5

Step 1: Add the User It’s just one simple command to add a user. In this case, we’re...

How to Add a User and Grant Root Privileges on Ubuntu 14.04

Step 1: Add the User It’s just one simple command to add a user. In this case, we’re...

Powered by WHMCompleteSolution