- Create a MySQL User on Linux via Command Line
- Grant Permissions to a MySQL User on Linux via Command Line
- Remove Permissions for a MySQL User on Linux via Command Line
- Remove a MySQL User on Linux via Command Line
MySQL via Command Line 101: Basic Database Interaction
- 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:
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!
Our Sales and Support teams are available 24 hours by phone or e-mail to assist.