- 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
- These instructions are intended for revoking a MySQL user permissions on Linux via the command line
- I’ll be working from a sxi.io Web Core Managed CentOS 6.5 server, and I’ll be logged in as root.
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:
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.
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!