Create MySQL User And Grant Privileges in Ubuntu 20.04

MySQL is a popular and open-source relational database management system. You can create MySQL user accounts and provide access privileges. On the basis of the access privilege, the user will be able to use and manage the database. The process to create MySQL User and set its password is quite simple in Windows operating system. However, if you are a Linux user, then the steps are different. Here, you will have to do it from the command line. But, don’t worry, today, through this post, you will be able to know how to create MySQL user in Ubuntu 20.04. Also, I will show you to set the password for MySQL users. One more important thing is to grant all privileges to MySQL users.

Prerequisites

For proceeding to create MySQL user in Ubuntu 20.04, I am assuming, you are ready with the below requirements-

• Ubuntu 20.04.2 LTS (As an Operating System)
• An Ubuntu user with Sudo Privileges
• A terminal and command-line access
• MySQL or Maria DB server

If you haven’t installed MySQL server in Ubuntu then you can follow up my previous post of LAMP Stack setup in Ubuntu.

Create MySQL User in Ubuntu

Firstly, I will check the current MySQL version using the below command. It will ensure that MySQL server is intalled in my system.

mysql --version

Here, in the result, you can see, the MySQL 8 is already installed in my system.

For how to create user in Mysql, firstly, you have to connect to the MySQL using below command.

sudo mysql -u root -p

If you have already set the root user password then the above command will prompt the password. After connecting to MySQL, you will be inside the MySQL command line.

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'new_user_password';

In the above command, you will have to replace the new_user with the actual user. And also, new_user_password with the password that you want to set. This command will create a new user with the specified password. Here, for the host, I have set the localhost. It means the user will able to connect MySQL server only from the localhost. (i,e. from the system where MySQL server is running).

In the next step, I will show you how, you can set the access from the another host.

How to Install Composer For PHP in Ubuntu 20.04 LTS

Grant Access From Another Host in MySQL

To set the access from another host to the user, you will have to pass the remote machine IP address. Suppose, you have the remote machine IP address 10.6.0.1. Here, you have to pass that IP Address as showing below.

CREATE USER 'new_user'@'10.6.0.1' IDENTIFIED BY 'new_user_password';

So, here, it will grant that user access for the entered IP address instead of localhost.

Grant Access From Any Host in MySQL

In case, if you don’t want to set the privilege for any particular host, then you can set the global host. Here, global host means for any host. So, here, you will have to pass the '%' wildcard. Let’s take a look.

CREATE USER 'new_user'@'%' IDENTIFIED BY 'new_user_password';

Create MySQL User with Encrypted Password

It is necessary to encrypt the password for the MySQL user. So, it is good for the security reasons. For encrypting the password, we will be using caching_sha2_password. It will encrypt the password.

CREATE USER 'new_user'@'%' IDENTIFIED WITH caching_sha2_password BY 'new_user_password';

List All Users in MySQL

If you want to get all the users in MySQL then you can hit the below command.

SELECT user FROM mysql.user;

Here, you can see, all users are listed here.

List Users with Password in MySQL

Instead of getting only users in MySQL you can check the password that you already set it while creating. So, just add few more parameters in the same command.

SELECT user,authentication_string FROM mysql.user;

In the below result, you can see the list of users with the authentication string. If you set the password encryption then it will show like below result.

List Users, Password, Host, and Plugin in MySQL

You can get more details of MySQL user like for which host the user has been created. Also, what plugin is used to encrypt the password, etc.

SELECT user,authentication_string,plugin,host FROM mysql.user;

Here, the above command returned the result with the specified arguments.

How to Install and Configure pgAdmin 4 in Ubuntu 20.04

Grant All Privileges to MySQL User

Before granting the privileges to MySQL user, firstly, let’s understand the type of privileges. In MySQL, these are the following privileges.

• ALL PRIVILEGES – Grants all privileges to a user account.
• CREATE – This provides the access to create the database and tables.
• DROP – The drop privilege is used to drop databases and tables.
• DELETE – This is used to delete rows from a specific table.
• INSERT – The user account is allowed to insert rows into a specific table.
• SELECT – To select and read the database and table this privilege is used.
• UPDATE – The user account is allowed to update table rows.

grant all privileges on *.* to 'new_user_name'@'localhost' with grant option;

The above command will grant all privilegs to the specified user. Here, I have defined ‘*’ for the privileges type. So, it will consider all privileges. You can define the specific privileges as from the above list.

Bottom Line

Finally, we understand how to create user in Mysql with a password.. In the password, we used both encrypted and without encrypted. Also, we have seen the user account for any specific host. We checked the list of users with other details. At last, we granted the privileges to the created user account. So, I hope you will understand these steps easily.