MySQL 8: How to create a new user with root privileges

Introduction

MySQL, the world’s second most widely used relational database management system, is a popular choice for many developers when it comes to data storage and management. A critical part of managing MySQL databases is ensuring you have the right users with the appropriate level of access. Sometimes, it may be necessary to create a new user with root privileges. In this tutorial, we’ll walk through the process of creating a new MySQL user with root-like privileges on Windows, Mac OS, and Ubuntu systems.

Prerequisites

• A system running MySQL 8 server.
• Access to a user account with sudo or administrative privileges.
• Basic understanding of MySQL commands and the command line interface.

Understanding MySQL User Privileges

In MySQL, root is a user with full access to all databases and tables. Having another user with root privileges can be useful for administrative tasks, but it’s essential to use this power responsibly as it can pose significant security risks.

Accessing MySQL Command-Line Interface

First, you’ll need to access the MySQL shell:

Windows:
mysql -u root -p

Mac OS and Ubuntu:
sudo mysql

Enter your root password when prompted.

Creating a New User

Now, we can begin the process of creating a new user with:

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

Replace ‘newuser’ with your chosen username and ‘password’ with a strong password.

Granting Root Privileges

To grant root privileges to the new user, use the GRANT command:

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

This command gives the new user full privileges to all databases and tables.

Applying Changes

Remember to apply the changes with:

FLUSH PRIVILEGES;

Cross-Platform Considerations

The above commands should work on all three operating systems. However, access methods to MySQL may differ. On Windows, you’ll use Command Prompt or PowerShell, while on Mac OS and Ubuntu, you’ll typically use the Terminal application.

Advanced Tips

Restricting User Host Access

You can control where a user can connect from by replacing ‘localhost’ with other hostnames or IP addresses:

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

The ‘%’ symbol allows the user to connect from any host.

Specifying Particular Privileges

If you want to fine-tune the privileges, you can specify them individually:

GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'newuser'@'localhost';

Replace ‘mydb’ with the database you want the user to access.

Creating Profiles with Limited Root Access

For improved security, consider granting only the necessary privileges that resemble root access:

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, SHOW VIEW, CREATE, ALTER, DROP ON *.* TO 'newuser'@'localhost' WITH GRANT OPTION;

This omits critical privileges like FILE and SUPER.

Revoking Privileges

Should you need to revoke privileges, you can do so with:

REVOKE ALL ON *.* FROM 'newuser'@'localhost';

Deleting a User

To completely remove a user:

DROP USER 'newuser'@'localhost';

Using Roles

As of MySQL 8, you can assign roles to users for easier privilege management:

CREATE ROLE 'admin';
GRANT ALL PRIVILEGES ON *.* TO 'admin';
GRANT 'admin' TO 'newuser'@'localhost';

Now, ‘newuser’ has privileges associated with the ‘admin’ role.

Conclusion

In conclusion, while it’s generally not advisable to create multiple users with root privileges for security reasons, certain situations may necessitate it. When doing so, following the proper steps ensures you maintain control over your MySQL databases and keep your system secure.