How To Create a New User and Grant Permissions in MySQL

How To Create a New User and Grant Permissions in MySQL

MySQL, one of the most popular relational databases. This allows for the creation of multiple users with varying levels of access and permissions. Managing user accounts and permissions is crucial for maintaining database security and ensuring that authorized users have the appropriate level of access to perform their tasks. If you are new to MySQL or want to learn how to create a new user and grant permissions, you’ve come to the right place. In this article, we will provide you with a step-by-step guide on how to create a new user in MySQL. Moreover we will cover how to set up their permissions, and manage their access to the database. Whether you are a developer, database administrator, or simply interested in learning MySQL, this article will walk you through the process of creating and managing users in MySQL for improved database security and access control.

If you want to learn how to back up a MySQL database to an Object Storage (e.g. Zumiv S3-compatible Object Storage)

Prerequisites/Requirements

To make use of this tutorial, you will require access to a MySQL database. The tutorial assumes that the database is installed on a virtual private server (VPS) running Ubuntu or Debian. However the process for creating a new MySQL user and assigning permissions is generally similar, regardless of the underlying operating system of your server.

Creating a MySQL User

After MySQL installation, a root user account is automatically created, granting full privileges over the MySQL server, including control over all databases, tables, and users. However, it is recommended to restrict the use of the root account to administrative tasks only. This section will detail how to utilize the root MySQL user to create a new user account and assign appropriate privileges.

Use the following command to login to your MySQL Server as root:

mysql –u root –p 

You will be prompted to enter your root-password.

Now that you have access to the MySQL prompt, you can create a new user with the “CREATE USER”-command. It follows the following general syntax:

CREATE USER 'username'@'host' IDENTIFIED BY 'password'; 

Following the “CREATE USER” command, you need to provide a username, followed by the “@” sign and the hostname from which the user will connect. If you only intend to allow local access from your local server, you can specify “localhost” as the hostname. While enclosing the username and host in single quotes may not always be mandatory, it can be beneficial in preventing potential errors.

Granting Permissions to a User

The purpose of granting privileges to a user in MySQL is to define the actions and operations that the user is allowed to perform on a database or specific database objects, such as tables, views, procedures, and functions. Privileges determine the level of access and control that a user has over the database and its objects.

By granting privileges, you can control what actions a user can perform. This includes things like creating, modifying, or deleting data, as well as managing database structures and configurations. Privileges allow you to set fine-grained access control. This ensures that users have the necessary permissions to perform their intended tasks while restricting them from unauthorized actions. With that you eliminate the risk that a user could potentially compromise the security or integrity of the database.

General Syntax

GRANT PRIVILEGE ON database.table TO 'username'@'host'; 

Granting Privileges

The PRIVILEGE value in the provided syntax determines the actions that a user is authorized to perform on the specified database and table. Multiple privileges can be granted to the same user in a single command, separated by commas. Additionally, global privileges can be granted by using asterisks (*) in place of specific database and table names, as asterisks represent “all” databases or tables in SQL.

For example, the following command grants a user the following global privileges, actions on databases, tables, and users:

CREATE

ALTER

DROP

It also allows the user to perform INSERT, UPDATE, and DELETE operations on any table, query data with SELECT, create foreign keys with REFERENCES, and perform FLUSH operations with RELOAD privilege.

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

However, it is important to only grant users the permissions they actually require, and adjust user privileges accordingly.

The complete list of available privileges can be found in the official MySQL documentation.

Some users require all permissions (e.g. if you are planning to create a user to install WordPress or Nextcloud). To do this, replace the names of the individual permissions with the word “all”. The command will then look like this:

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

Refresh the Database

To refresh the user’s permissions, use this command:

FLUSH PRIVILEGES; 

Revoke Permissions from a User

If you need to revoke a permission, the structure is almost identical to granting it:

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

Note that when revoking permissions, the syntax requires that you use FROM, instead of TO. TO would be used when granting the permissions.

Other useful MySQL Commands

Display permissions of a user

SHOW GRANTS FOR 'username'@'host'; 

Delete a user

DROP USER 'username'@'localhost'; 

 

See Also: Experience Our for Free VPS Hosting: Enjoy a 30-Day Trial with Risk-Free Servers

COMPLETE DIGITAL SERVER SOLUTIONS FOR ALL

Bare Metal Dedicated Servers

A single tenant, physical server allowing you full access to its resources

Read More

Cloud VPS

The cheapest way to get your own independent computing instance.
Read More

Cloud VDS

Virtualized server platform hosted on enterprise-grade physical servers

Read More

10 Gbps Unmetered Servers

Zomev offers high bandwidth dedicated servers up to 20Gbps.

Read More

ZOMEV NEWSLETTER

Receive the latest news, updates and offers. You can unsubscribe at any time.

ZOMEV NEWSLETTER

Receive the latest news, updates and offers. You can unsubscribe at any time.

zomiv footer logo

HOSTING REDEFINED

44-7-441-399-305
Support Hours: 24x7x365
Sale Office Hours: M-F, 7AM-5PM EST

We accept the following:

visa
mastercard
paypal
download (6)

PRODUCTS

SERVICES

© Copyright 2024, All Rights Reserved by DataCamp Int Limited.

Zomev is a trading name of DataCamp Int Limited. Registered Office: 71-75 Shelton Street, Covent Garden,
London, United Kingdom, WC2H 9JQ.Registered Number 15527709. Registered in England and Wales.

certifications

ZOMEV NEWSLETTER

Receive the latest news, and offers. You can unsubscribe at any time.

  • PRODUCTS
  • LOCATIONS
  • SOLUTIONS
  • COMPANY
This is a staging enviroment

Please tell us more about yourself.

Complete the form below and one of our experts will contact you within 24 hours or less. For immediate assistance contact us.

In order to finalize your application, please read and accept our Terms and Conditions*.

CUSTOM QUOTE REQUEST

Complete the form below and one of our experts will contact you within 24 hours or less. For immediate assistance contact us.

We promise not to sell, trade or use your email for spam. View our Privacy Policy.