How to Create a New User and Manage permissions in MySQL

Overview

In this tutorial, we will learn how to create and manage new and existing users using CLI (command line interface). These commands will function on any platform.

Pre-requisite

You need to have the following setup on your system and knowledge of using them:

• An Ubuntu 20.04 server 
• MySQL installed on your Ubuntu system
• Knowledge of CLI

Procedure

1. Follow these steps to setup a password-based access by creating an account to login:-

sudo mysql

2. Check access levels of existing accounts by using the below command:

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

3. To setup a password for root account, use the below command.

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

Or if you have an older version try:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

4. Now, check the list of existing accounts to confirm.

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

5. Follow these steps for making a separate user access than root.

sudo mysql

Or if you have a password setup, use:

mysql -u root -p

6. Type this command to create a user.

CREATE USER 'john'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'your_password';

Again, if you have an older version, try:

ALTER USER 'john'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';

7. To grant suitable privileges to your user, type the following command:

GRANT ALL PRIVILEGES ON *.* TO 'john'@'localhost' WITH GRANT OPTION;

8. After this, you can exit mysql.

exit

Conclusion

In this tutorial, we have created a new user and granted privileges to the user in MySQL client. These commands will work on any operating system and let you manage the user in MySQL.