When you have a MySQL server, a common task, especially as the server admin, is to create a new user and grant permissions in MySQL. Creating users is how you give them access to perform tasks depending on the permissions you grant them. For instance, you can create a user whose privilege is only to select elements from the table but can’t perform other tasks such as create, insert, update, or drop. So, how do you create a new user and grant permissions in MySQL? Keep reading this post to find out!

Before we discuss creating a new user, some prerequisites must be in place. For starters, you must have a MySQL database server installed on your system or your cloud platform. Check the required instructions to install and start MySQL for your case.

Once a MySQL database server is available, access the server as the root user account. You must be root/admin to create new users for the database.

In granting permissions to a new or existing user, there are different permission types that you can grant. We’ve discussed them below.

SELECT – The permission allows users to retrieve data from a database or table.

CREATE – Once granted, the user can create a database or tables inside the databases.

INSERT – The permission allows the user to add new records to tables.

UPDATE – The permission allows users to change existing records/entries.

DELETE – The user can delete table entries if they have this permission.

DROP – The permission allows the user to delete database tables.

When granting permissions in MySQL, you can specify what permissions to grant or choose to grant all privileges to the user. Again, you can specify whether the user should only have permissions on a particular table or all the databases and tables.

So far, we’ve discussed what you need to create a new user and described the different permissions you can grant the new or existing database user.

To create a new MySQL user, log in to your database server with the below command.

Enter your password to log in and bring up the shell.

To create a new user, utilize the below syntax.

In the syntax above, replace ‘user’ with the username to assign to the new user you want to create. Next, add the ‘localhost’ if the user will connect to the database locally from your server. If the user will connect remotely, add their hostname instead of ‘localhost.’ Lastly, add the password to authenticate the user when they are accessing the MySQL database.

Here’s an example where we’ve created a new user named ‘linuxmeta’ who is identified with the provided password.

List the available users to verify that your new user has been successfully created.

Now that we’ve created a new user, the remaining task is to grant them permission. First, let’s list the available permissions for our newly created user. Replace the username accordingly.

The output below shows that our user only has the USAGE permission, granted by default.

To grant permissions in MySQL, we use the below syntax.

For instance, if we want our new user to have the CREATE and ALTER permissions to a table in a database, we would grant them as in the example below. Our database is files, and it contains the names table.

Rechecking the available permissions for our new user, we verify that the two permissions we’ve assigned to them have been successfully granted.

You can then flush privileges to persist the changes.

Suppose you want to grant permissions to the entire database instead of a table. You would run your command following the syntax below.

Look at the below example where we’ve granted the SELECT permission to the database.

Instead of granting specific permissions, you may give the user all the available permissions. However, be keen on this option as the user will have admin privileges and can perform all tasks on your database. To grant all privileges to the user for your database, use the syntax below.

That’s how you create a new user and grant permissions in MySQL. If, for whatever reason, you may want to revoke the granted permissions, run the command below and specify the target username.

How to create a new user and grant permissions in MySQL is not a complicated task. Start by ensuring you have MySQL installed and running. Next, create the user by specifying their username and password. From there, grant them the required permissions and verify the new permissions. All steps and commands are detailed in this post.