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>

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';

Courtesy: Grant Permissions to a MySQL User on Linux via Command Line | Liquid Web