Reading Time: 2 minutes

Preflight Check

  • 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.
Grant Permissions to a MySQL User on Linux via Command Line

Login to MySQL

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.

Grant Permissions to 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 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 grant CREATE permissions for all databases * and all tables * to the user we created in the previous tutorial, test user, use the following command:

GRANT CREATE ON *.* TO '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 grant testuser the ability to drop tables in the specific database, tutorial_database, use the DROP permission:

GRANT DROP ON tutorial_database.* TO 'testuser'@'localhost';

When finished making your permission changes, its good practice to reload all the privileges with the flush command!

FLUSH PRIVILEGES;

View Grants for MySQL User

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 testuser :

SHOW GRANTS FOR 'testuser'@'localhost';

The Most Helpful Humans In Hosting™

We pride ourselves on being The Most Helpful Humans In Hosting™! Our support staff is always available to assist with any Dedicated, Cloud, or VPS server issues 24 hours a day, 7 days a week 365 days a year.

We are available, via our ticketing systems at support@liquidweb.com, by phone (at 800-580-4986) or via a LiveChat for whatever method you prefer. We work hard for you so you can relax.

Series Navigation
<< Previous ArticleNext Article >>

About the Author: Justin Palmer

Have Some Questions?

Our Sales and Support teams are available 24 hours by phone or e-mail to assist.

1.800.580.4985
1.517.322.0434

Latest Articles

5 Alternatives to GitHub

Read Article

5 Steps to Install and Configure osquery on CentOS 7 & 8

Read Article

Five Steps to Create a Robots.txt File for Your Website

Read Article

Premium Business Email Pricing FAQ

Read Article

Microsoft Exchange Server Security Update

Read Article