Introduction
MySQL is the world’s leading open-source relational database management system (RDBMS). Widely used in the LAMP stack (Linux, Apache, MySQL, PHP), MySQL powers numerous web applications. This guide will walk you through the steps to create a new MySQL user and grant the necessary permissions.

---

Table of Contents

Section	Description
1. Prerequisites	Ensure you have access to a MySQL database and set up your environment.
2. Creating a New MySQL User	Learn how to create a new MySQL user with password authentication.
3. Accessing MySQL as Root	Instructions for accessing the MySQL shell with root privileges.
4. Creating a User with Password Authentication	Steps for creating a user using MySQL’s default authentication method.
5. Granting Permissions to a MySQL User	How to grant specific privileges to a user for performing tasks.
6. Granting Privileges	Syntax and examples for granting permissions to MySQL users.
7. Applying the Changes	Learn about applying permissions and reloading the grant tables.
8. Revoking Privileges	Instructions for revoking user permissions in MySQL.
9. Viewing User Permissions	How to view a user’s current privileges using SHOW GRANTS.
10. Deleting a MySQL User	Steps for deleting a MySQL user using the DROP USER command.
11. Logging in as the New User	How to log in as the new MySQL user with password authentication.
12. Conclusion	Summary of the process and further steps for optimizing MySQL.

SEO-Optimized Guide: How to Create a MySQL User and Grant Permissions

Introduction
MySQL is the world’s leading open-source relational database management system (RDBMS). Widely used in the LAMP stack (Linux, Apache, MySQL, PHP), MySQL powers numerous web applications. This guide will walk you through the steps to create a new MySQL user and grant the necessary permissions.

Prerequisites
Before you begin, ensure you have access to a MySQL database. This tutorial assumes your database is running on an Ubuntu 20.04 VPS, though the instructions should apply regardless of your database environment.

If you don’t have a MySQL database installed yet, refer to our guide on How to Install MySQL for the steps to set up MySQL on your server. Alternatively, you can deploy a MySQL managed database via your preferred cloud provider.

Note: Any customizable example commands in this guide will be highlighted like this.

Creating a New MySQL User

By default, MySQL creates a root user with complete administrative privileges over all databases, tables, and users. For security reasons, it’s best to create a separate user for day-to-day database operations. Here’s how to do it.

Accessing MySQL as Root

On systems running MySQL 5.7 and higher, the root MySQL user uses the auth_socket plugin by default. To access MySQL as the root user, you need to run the following command with sudo:

sudo mysql

Note: If your root MySQL user is set to authenticate with a password instead, use this command to access the MySQL shell:

mysql -u root -p

Creating a New User

Once you’re inside the MySQL prompt, you can create a new user using the CREATE USER statement. The general syntax is as follows:

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

• Replace username with your desired MySQL username.
• Set host to localhost if the user will access the database locally.
• Choose an authentication plugin or leave it to default (caching_sha2_password), which is highly secure.

Example: Create a User with Password Authentication

The following command creates a MySQL user named sammy with password-based authentication:

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

Note: If you’re using PHP-based applications like phpMyAdmin, you may need to create the user with the older mysql_native_password plugin for compatibility:

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

If necessary, you can modify the user’s authentication plugin later:

ALTER USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Granting Permissions to a MySQL User

After creating a new user, you’ll want to grant them appropriate permissions to perform tasks on your MySQL databases.

Granting Privileges

The syntax for granting privileges is as follows:

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

• PRIVILEGE: The specific actions the user can perform, like SELECT, INSERT, UPDATE, or DELETE.
• database.table: Specify the database and table (or use *.* to grant privileges globally).

Example: Grant Multiple Privileges

The following command grants the user sammy global permissions to create, alter, and drop databases and tables, insert and update data, and more:

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

Warning: Be cautious with the ALL PRIVILEGES command, which grants a user full administrative access, similar to the root user:

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

Applying the Changes

MySQL automatically updates the grant tables, so running FLUSH PRIVILEGES after GRANT is generally unnecessary. However, it doesn’t hurt to run this command to manually reload the privileges:

FLUSH PRIVILEGES;

Revoking Privileges

If you need to revoke permissions, the syntax is almost identical to granting them:

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

Viewing User Permissions

To see the current privileges for a user, run:

SHOW GRANTS FOR 'username'@'host';

Deleting a MySQL User

To remove a user from the MySQL system, use the following DROP USER command:

DROP USER 'username'@'localhost';

Logging in as the New User

After creating a new MySQL user and granting them permissions, you can log in as the new user with the following command:

mysql -u sammy -p

The -p flag prompts you for the user’s password.

Conclusion

In this guide, you’ve learned how to create new MySQL users and assign the appropriate privileges. By granting only the necessary permissions, you can ensure both security and efficient management of your MySQL database. From here, explore different permission configurations and higher-level MySQL settings to further optimize your database setup.