How to create User and Grant Permission In MySQL on Linux Command Line

MySQL Linux Commands

We are going to take a quick tour of common Linux command to deal with MySQL database:

Login to MySQL in Linux using command line

To login to MySQL database run the following command:

mysql -u root -p
Enter password:

By passing -u parameter as login username and -p to enter the user password.

Creating new MySQL user in Linux using command line

To create new MySQL user , use command CREATE USER by passing the new username and follow by IDENTIFIED BY with the new password as bellow:

CREATE USER ‘newmysqluser’@’localhost’ IDENTIFIED BY ‘NiwPwd!955’;

The example attempting to create new username newmysqluser with password NiwPwd!955

Grant And Revoke Permissions to MySQL User

After you create MySQL user , the next step to grant permissions to database , the general syntax  for granting permission is

GRANT permission ON database.table TO ‘user’@’localhost’;
The permission syntax can be one of the following:

  • 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.
  • DROP – Allow a user to drop databases and tables.
  • 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.
  • DELETE – Allow a user to delete rows from a table.
  • EXECUTE – Allow a user to execute stored routines.
  • GRANT OPTION – Allow a user to grant or remove another user’s privileges.
  • UPDATE – Allow a user to update rows in a table.

Example of Grant Create permission for user newmysqluser  to all MySQL objects:

GRANT CREATE ON *.* TO ‘newmysqluser’@’localhost’;

Or Grant Drop permission to user newmysqluser on all tables of test_database

GRANT DROP ON test_database.* TO ‘newmysqluser’@’localhost’;

Now once finish granting the permission to the MySQL user, run the FLUSH to reload all the privileges


Now we can View Grants for newmysqluser MySQL User

SHOW GRANTS FOR ‘newmysqluser’@’localhost’;

Using Revoke permission command

Now To Use the Revoke command the syntax is identical to grant but this command will remove the permission of the selected user when you runt the command, for example lets remove Drop permission to user newmysqluser to all tables in test_database , that already grant drop before:

REVOKE DROP ON test_database.* TO ‘newmysqluser’@’localhost’;

Remove a MySQL User In linux using command line

To remove a user from MySQL, we again use the DROP command.As example lets remove newmysqluser

DROP USER ‘newmysqluser’@’localhost’;

if you are trying to remove none exists user you will get SQL error ERROR 1396 (HY000): Operation DROP USER failed for ‘newmysqluser’@’localhost’

View a List of MySQL Users

To view full list of MySQL users, including the host they’re associated with,run the following SQL:

SELECT User,Host FROM mysql.user;
Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Related Posts