DEV Community

Sospeter Mong'are
Sospeter Mong'are

Posted on

How to Create a New MySQL User and Grant All Permissions

#mysql #sql #database #programming

When working with MySQL, it's good practice to avoid using the root account for applications or development work. Instead, create a dedicated user with the necessary permissions. This improves security, allows better access control, and keeps your setup clean.

In this article, you'll learn how to:

  • Create a new MySQL user
  • Grant all privileges to the user
  • Understand what WITH GRANT OPTION means
  • Test the new user login

Step 1: Log in as Root

Open your terminal and log into MySQL as the root user:

mysql -u root -p
Enter fullscreen mode Exit fullscreen mode

You’ll be prompted to enter the root password.

Step 2: Create a New User

Run the following command inside the MySQL prompt:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'StrongPassword123';
Enter fullscreen mode Exit fullscreen mode
  • Replace newuser with your desired username.
  • Replace 'localhost' with '%' if you want the user to connect remotely.
  • Replace 'StrongPassword123' with a strong password.

Step 3: Grant All Permissions

To allow the new user to perform any action on all databases:

GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
Enter fullscreen mode Exit fullscreen mode

If you want this user to be able to grant permissions to others, include:

GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost' WITH GRANT OPTION;
Enter fullscreen mode Exit fullscreen mode

Step 4: Apply the Privileges

Always run this after changing privileges to ensure they're applied:

FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

Step 5: Test the New User

Exit MySQL:

exit
Enter fullscreen mode Exit fullscreen mode

Then test the new user login:

mysql -u newuser -p
Enter fullscreen mode Exit fullscreen mode

If successful, you've set up the user correctly.

Understanding WITH GRANT OPTION

The WITH GRANT OPTION clause allows the user to grant their own privileges to other users. It's useful for admin-level users but should be used with caution.

Example:

If you give alice select access to a database with grant option, she can also grant that select access to others.

GRANT SELECT ON mydb.* TO 'alice'@'localhost' WITH GRANT OPTION;
Enter fullscreen mode Exit fullscreen mode

Then alice can do:

GRANT SELECT ON mydb.* TO 'bob'@'localhost';
Enter fullscreen mode Exit fullscreen mode

Note: For typical application users or developers, you don’t need WITH GRANT OPTION.

Conclusion

Creating and managing MySQL users properly is crucial for maintaining secure and organized systems. By following the steps above, you can create a new user, grant the necessary permissions, and optionally enable that user to manage others.

Top comments (0)