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.
Prerequisites
There are certain prerequisites that need to be met before you begin:
An Ubuntu 20.04 server
MySQL installed on your Ubuntu system
Knowledge of CLI
Get Sarted
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. 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.
Last updated
Was this helpful?