How to Create New User and Grant Permissions in MySQL

Table of Contents
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.
LATEST POSTS
-
How to Install Docker Compose on Debian 10
-
How Unzip File in Linux from Terminal
-
How to Install Adobe Flash Player on Debian
-
How to Install Postman on Debian 9
-
How to Install XAMPP on Manjaro 18
-
How to install Nginx on CentOS 7
-
How to Install Google Earth on Ubuntu 18.04 Linux
-
How to Install RubyMine on Ubuntu 18.04
-
Easily Quit Vim/Vi Without Saving Changes Command
-
How to setup UFW firewall on Ubuntu 18.04
-
How to Install Sublime Text 3 on Fedora 29
-
How to Install R on Debian 9