How to Create New User and Grant Permissions in MySQL

Create New User and Grant Permissions in MySQL

MySQL is opensource and one of the most popular and widely used database management systems. Sometimes we stuck in checking the version of the software we are using in different Linux distros like Ubuntu, Debian, RedHat etc. In this tutorial, you are going to learn Create New User and Grant Permissions in MySQL.

Prerequisites

Before you start to Create New User and Grant Permissions in MySQL. You must have the root user account credentials of your system.

Log in to MySQL database using below command:

sudo mysql -u root -p

Now enter the password for root user to login successfully.

Create a New User in MySQL

The basic syntax for creating a new user is given below:

CREATE USER new_username IDENTIFIED BY new_password;

You can create a new user in MySQL using username and a strong-password for the username.

To create a new user run below command by replacing username with your new username and strong-password with the password you want to give for the new user:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'strong-password';

In above command ‘localhost’ stands for the host of this machine. You can also place the IP Address of the host instead of ‘localhost’ in the above command.

You can also create a user account which can connect from any host machine using below command:

CREATE USER 'username'@'%' IDENTIFIED BY 'strong-password';

Grant Permissions in MySQL

After creating a new user, the permissions should be granted for that user to perform different operations on databases.

Below are the permissions that you can grant for newly created user:

• • ALL PRIVILEGES- This will allow a MySQL user full access to a given database
  • INSERT- It will allow the user to insert rows into tables
  • DROP- It will allow the user to them to delete tables or databases
  • DELETE- It will allow the user to delete rows from tables

• SELECT- It will allow the user to use the SELECT command.
• UPDATE- It will allow the user to update table rows
• CREATE- It will allow the user to create new tables or databases
• GRANT OPTION- It will allow the user to grant or remove other user’s privileges

For grating permissions to the user following basic syntax is used:

GRANT permission_type ON database_name.table_name TO ‘username’@'localhost’;

You can grant all permissions to all databases by using below command:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

To grant all permissions to only a specific database following command is used:

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

To grant all permissions for only a specific database table following command is used:

GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'localhost';

You can also grant only INSERT permission for a user using below command:

GRANT INSERT ON database_name.table_name TO 'username'@'localhost';

You can aslo grant multiple permissions using below command:

GRANT INSERT, SELECT, UPDATE, CREATE ON database_name.table_name TO 'username'@'localhost';

Next, to show all permissions granted for a user type following command:
SHOW GRANTS FOR ‘database_user’@’localhost’;

Revoke Permissions in MySQL

For grating permissions to the user, following basic syntax is used:

REVOKE permission_type ON database_name.table_name TO ‘username’@'localhost’;

You can aslo revoke all permissions to databases by using below command:

REVOKE ALL PRIVILEGES ON *.* TO 'username'@'localhost';

DROP USER

You can drop created user using below command:

DROP USER 'username'@'localhost'

Conclusion

You have successfully learned How to Create New User and Grant Permissions in MySQL. If you have any queries please don’t forget to comment out.