II. Grant Permission to a MySQL User on Linux via Command Line
III. Remove Permissions for a MySQL User on Linux via Command Line
IV. Remove a MySQL User on Linux via Command Line
MySQL via Command Line 101: Basic Database Interaction
- These instructions are intended for granting 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 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
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.
The basic syntax for granting permissions is as follows:
GRANT permission ON database.table TO 'user'@'localhost';
Here is a short list of commonly used
- 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 grant
GRANT CREATE ON *.* TO 'testuser'@'localhost';
Using an asterisk (*) in the place of the
Example #2: To grant
GRANT DROP ON tutorial_database.* TO 'testuser'@'localhost';
When finished making your permission changes, it’s good practice to reload all the privileges with the
After you’ve granted permissions to a MySQL user you’ll probably want to double check them. Use the following command to check the grants for
SHOW GRANTS FOR 'testuser'@'localhost';