How To Create a New MySQL User?

...
profile picture of ghostzero
GhostZero
Software Developer
Published at December 13, 2021

Sometimes you need to create a new mysql user. This can be the case if you onboard a new employee or if you need to create a new user for a new project.

In this tutorial we will create a new mysql user. A mysql user is a user that has access to a mysql database. You can give them different permissions, like an administrator or a read-only user.

Create a new database (optional)

Here we create a new database. This is optional. If you don't want to create a new database, you can skip this step.

CREATE DATABASE new_database;

Log in to the MySQL server

Depending on your setup, you may need to log in to the mysql server. In my case, I use the root user to create and grant permissions to new users.

mysql -u root -p

Create a new user

Next we create a new user. In this step, we only create the login name and password. The user will have no access to any databases by default. Also, we use % as the hostname. This means that the user will have access to the mysql server on all hosts. If you want to restrict the user to a specific host, you can use a different hostname.

CREATE USER 'new_user'@'%' IDENTIFIED BY 'your_secure_password';

Grant permissions to the new user

In the next step, we grant permissions to our new created user. I will explain many scenarios, like admin, db full access, db ready-only.

Option 1: Grant all permissions to the new user (administrator)

GRANT ALL PRIVILEGES ON *.* TO 'new_user'@'%' WITH GRANT OPTION;

Option 2: Grant full access to the database (db full access)

GRANT ALL PRIVILEGES ON new_database.* TO 'new_user'@'%';

Option 3: Grant read-only access to the database (db read-only)

GRANT SELECT ON new_database.* TO 'new_user'@'%';

Apply the changes

After modifying the user, we need to apply the changes. This is done by executing the FLUSH PRIVILEGES command. It's important to do this after modifying the user.

FLUSH PRIVILEGES;

Conclusion

Congratulations! You have created a new mysql user. You can now log in to the mysql server with the new user and password.

Follow me!

Related stories

You liked How To Create a New MySQL User?? You may also be interested in these following articles...
...

With SUBtember right around the corner, you might be wondering what all the fuss is about. What's the difference between a subathon and SUBtember? Let's take a closer look.

GhostZero
1 month ago
...

Düsseldorf. 70,000 visitors came to the DoKomi for manga fans and cosplay in Düsseldorf. This means that DoKomi set a new record for visitor numbers and a growth of +27 percent (compared to 2019).

GhostZero
3 months ago
...

Mit YAFS (Yet Another Firmware Selector) ist es nun möglich die Freifunk Ense Firmware für unterstützte Router zu finden und herunterzuladen.

GhostZero
6 months ago
GhostZero is live on Twitch!
Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy.