How to Create a MySQL User and Grant Access to a Database

Table of contents

Introduction

In many cases, we need to create dedicated MySQL users for different applications or individuals to manage database access securely. This article will guide you through the process of creating a new MySQL user and granting them access to a database named example.

Step 1: Create the Example Database (If Not Already Created)

Before creating a new user, you need to ensure that the example database exists. If it doesn’t, you can create it using the following command:

CREATE DATABASE example;

Step 2: Create a New MySQL User

To create a new MySQL user, you can use the following command:

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

In this command:

• newuser is the username you want to create.
• localhost is the hostname from which the user can connect to the MySQL server. You can replace localhost with the IP address of the remote server if you want to allow connections from a specific IP address.
• password is the password for the new user.

Step 3: Grant Access to the Example Database

After creating the new user, you need to grant them access to the example database. You can do this using the following command:

GRANT SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE,
DROP, ALTER, INDEX, TRIGGER, CREATE VIEW,
SHOW VIEW, EXECUTE, ALTER ROUTINE,
CREATE ROUTINE, CREATE TEMPORARY TABLES, LOCK TABLES,
EVENT ON `example`.* TO 'newuser'@'localhost';

In this command:

• example is the name of the database to which you want to grant access.
• newuser is the username you created in Step 2.
• localhost is the hostname from which the user can connect to the MySQL server.
• The list of permissions after the GRANT keyword specifies the operations that the user can perform on the database.

Step 4: Apply the Changes

After creating the new user and granting them access to the example database, you need to apply the changes by running the following command:

FLUSH PRIVILEGES;

Step 5: Verify the User’s Access

To verify that the new user has access to the example database, you can log in to the MySQL server using the new user’s credentials:

mysql -u newuser -p

You will be prompted to enter the password for the new user. After entering the password, you should see the MySQL prompt, indicating that you have successfully logged in.