Grant Permissions to a MySQL User on Linux via Command Line

Cpanel/Whm License $3/mo Plesk License $10/mo Cloudlinux License $5/mo

Pre-Flight Check
  • These instructions are intended for granting 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.

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.

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, testuser , 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, it’s 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';

Series Navigation

<< Create a MySQL User on Linux via Command LineRemove Permissions for a MySQL User on Linux via Command Line >>

Related posts

Blockchain in Your Pocket? The Phone Behind Sirin’s $157 Million ICO

SXI ADMIN

Grep Words From a File in Unix / Linux

SXI ADMIN

How to add ssh key to qcow2 Linux cloud images using virt-sysprep

SXI ADMIN

North America’s Bitcoin Roadshow Offers Free Car as Grand Prize

SXI ADMIN

Bakkt Acquires Crypto Custodian, Partners With BNY Mellon on Key Storage

SXI ADMIN

SEC Begins Accepting Public Comments on ETF Backed by Bitcoin and T-Bills

SXI ADMIN

Bitcoin Price Crosses $600, Rising 34% in One Month

SXI ADMIN

Russia’s Largest Bank is Worried About Blockchain’s Dark Side

SXI ADMIN

Linux Rip and Encode Audio CDs Commands

SXI ADMIN

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More