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.