How To Create a New User in MySQL and Grant Permissions

Introduction

MySQL is an open-source relational database management system (RDBMS) developed by Oracle that is based on structured query language (SQL). MySQL runs on Linux, UNIX and Windows. Although it can be used in a wide range of applications and, is the most popular open-source database in the world.

In this guide, we’ll discuss how to create a new user in MySQL and grant permissions.

Prerequisites

Before you begin this guide, you’ll need to do the following:

• root access to the server
• MySQL installed on the server

Step 1 – Creating a New MySQL User

First, Login to the MySQL using the following command

sudo mysql

# If you have configured root MySQL user with a password use the following
mysql -u root -p

After connecting to MySQL, using the following command to create new user

CREATE USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Note: Make sure to replace “username” “password” with the actual name of the database username and password before running the command.

After creating the new user in MySQL, now grant the appropriate permissions.

Step 2 – Create Database

Note: Skip this step if you already have created the database

Run the following command to create database

CREATE DATABASE db_name;

Note: Make sure to replace “db_name” with the actual name of the database before running the command.

Step 3 – Granting Permissions to User

Now granting the permissions to the newly created user using the following command

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'username'@'localhost' WITH GRANT OPTION;

If you want to give all the privileges to the user, run the following command

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

Now, run the following command to flush the privileges to reload the updated grant tables.

FLUSH PRIVILEGES;

If you want to remove the granted privileges from the user, run the following command

REVOKE type_of_permission ON database_name.table_name FROM 'username'@'localhost';