How To Create a New User and Grant Permissions in MySQL

MySQL is an open-source database management software that helps users store, organize, and later retrieve data. It has a variety of options to grant specific users nuanced permissions within the tables and databases—this tutorial will give a short overview of a few of the many options.

At the command line, log in to MySQL as the root user:

mysql -u root -p

Let’s start by making a new user within the MySQL shell:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

To create a database, type the following command. Replace dbname with the name of the database that you want to create:

CREATE DATABASE dbname;

At this point newuser has no permissions to do anything with the databases. In fact, even if newuser tries to login (with the password, password), they will not be able to reach the MySQL shell.

Therefore, the first thing to do is to provide the user with access to the information they will need.

GRANT ALL PRIVILEGES ON dbname. * TO 'newuser'@'localhost';

As a final step following any updates to the user privileges, be sure to save the changes by issuing the FLUSH PRIVILEGES command from the MySQL prompt:

FLUSH PRIVILEGES;

Use following command to import database from sql file.

mysql -u username -p dbname < file.sql

Now you should have a sense of how to add new users and grant them a variety of permissions in a MySQL database.

Adarsh Vengarathodi

Adarsh Vengarathodi is a serial entrepreneur and the CEO and founder of MistLayer, Inc. MistLayer is an ICT Company headquartered in San Francisco offering a full range of technology products, solutions, and services. MistLayer delivers innovative products that augment the lives of thousands of people around the world.

You may also like...