Create and grant permission for MySQL user

When working on a project that uses MySQL, on your computer you usually use the root user with all the rights to manipulate the database. This is nothing to say, but imagine if you were working in a production environment, where any database-related errors will affect the entire system.

Therefore, there are cases where you should not use the root user, we should create another MySQL user less power to use.

In this tutorial, I will guide you all how to create new and grant access to MySQL user.

We first need to log in to MySQL with the root account in order to work with all permissions:

Enter and then enter the password.

Now, we will create a new user using the following command:

With the above command, the created user will only be accessible from your computer, if you want to access from another machine then you need to replace localhost with %, details as follows:

After you run the command above, you still cannot use this user to log in because we have not granted any access to this user, so you need to run the following command line:

Or:

Access permission includes the following:

  • ALL PRIVILEGES: This privilege is the same as the root user, which has all the permissions to the database or table specified in the command line.
  • CREATE: Allows the user to create a new database or table.
  • DROP: Allows to delete any database or table.
  • DELETE: Allows to delete any record in the table.
  • INSERT: Allows to add new records to the table.
  • SELECT: Allows the use of SELECT statements to read data from the table.
  • UPDATE: Allows you to update the records in the table.
  • GRANT OPTION: Allows you to grant or remove permissions for another user.

If you want to assign this user access to all databases, the tables then table names and database names will use *.

For now, I want to grant permission to create a new table for the user, then I will run the following command:

Or:

Finally, for our changes to take effect, you need to run the following command line:

Add Comment