Nishant Munjal

Code For Humanity

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 onchanging 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 userwe 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 impliesalldatabases oralltables.

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