How to create users and grant permissions in MySQL

Share your database with other servers and users

MySQL is an incredibly powerful database server platform. With this database you can use tools such as WordPress, Nextcloud, OrangeHRM and many more. Installing MySQL is fairly easy, as is opening the MySQL prompt and creating a database. But what happens if you need to create a new database user and give that user permission to access the database? That’s when it can get a little tricky. Fortunately it is not very difficult.

When you install a platform like WordPress, it requires a database (usually a MySQL database). During the installation of a system such as WordPress, you will be asked to:

  • The name of the database to use.

  • Username with access rights to the database.

  • The password for the user who has access to the database.

During the installation of the MySQL database, you will be prompted to create a password for a user with administrator rights. You may be wondering, “Why not just use an administrator for this process?”. The answer is simple: security. This MySQL administrator may only be used to administer the MySQL database server and its users, and not as an account to install third-party software. To do this, you should always create new users and grant the new user access to a third-party database. For example, if you install WordPress, you can create:

  • Database: wordpress_db

  • User: wordpress_db_user

Then you need to create a password for wordpress_db_user and give that user full access to the database wordpress_db

Let’s go ahead and create a database. Here are the steps:

  1. Open the MySQL prompt with the command

     mysql -u root -p 

  2. Enter the MySQL administrator password and use Enter/Return on your keyboard.

    Create a database with the command

     CREATE DATABASE wordpress_db; 

    (replace “wordpress_db” with a database name that suits your needs).

  3. Reset the database permissions with the command

     ПРИВИЛЕГИИ ПРОМЫВКИ; 

  4. Exit the MySQL command line with the command

     Выход 

Once you have a database, you can create a user who can access the newly created database. This is also done from the MySQL command line. Follow these steps to create this new user:

  1. Open the MySQL prompt with the command

     mysql -u root -p 
  2. Enter the MySQL administrator password and use Enter/Return on your keyboard.

    Create user with command

     СОЗДАТЬ ПОЛЬЗОВАТЕЛЯ 'wordpress_db_user' @ 'localhost', ИДЕНТИФИЦИРОВАННОГО 'L! f3W! r3'; 

    (replace “wordpress_db_user” and “L! f3W! r3”) with username and password according to your needs).

  3. Reset the database permissions with the command

     ПРИВИЛЕГИИ ПРОМЫВКИ; 
  4. Exit the MySQL command line with the command

     Выход 

Now we need the newly made wordpress_db_user permission to access the newly created database wordpress_db † This is achieved by the following steps:

  1. Open the MySQL prompt with the command

     mysql -u root -p 
  2. Enter the MySQL administrator password and use Enter/Return on your keyboard.

    Give the user access with the following command:

     ПРЕДОСТАВЛЯЕТ ВСЕ ПРИВИЛЕГИИ НА wordpress_db. * TO 'wordpress_db_user' @ 'localhost', ОПРЕДЕЛЯЕМЫМ 'L! f3W! r3';. 

    (replaces wordpress_db wordpress_db_user, And L! f3W! r3 with database, username and password according to your needs).

  3. Reset the database permissions with the command

     ПРИВИЛЕГИИ ПРОМЫВКИ; 
  4. Exit the MySQL command line with the command

     Выход 

At this point, the local user wordpress_db_user has full access to the database wordpress_db † So when you are about to install WordPress (or whatever server software you are about to install), you can take advantage of wordpress_db_user if the database username and L! F3W! R3 as password.

Provide remote access

There is a problem. The above permissions only work for: wordpress_db_user on the local computer. What if your database is hosted on a remote server? To do this, you need to change the command: GRANT ALL PRIVILEGES † Let’s say the computer where you install WordPress (or other third-party server software) is at the IP address 192.168.1.100. Provided wordpress_db_user permission to access the database from this computer, new command GRANT ALL PRIVILEGES will look like this:

 ПРЕДОСТАВЛЯЕТ ВСЕ ПРИВИЛЕГИИ НА wordpress_db. * TO ‘wordpress_db_user’@’192.168.1.100’, ОПРЕДЕЛЯЕМЫМ ‘L! f3W! r3’; 

As you can see, instead of giving full access to wordpress_db on localhost we did what we gave to the user wordpress_db_user on the remote computer 192.168.1.100 full access to: wordpress_db database. This command will allow you to install WordPress (or any other third party server software you may need) on a server with IP address 192.168.1.100 and access the MySQL database wordpress_db such as wordpress_db_user .

Leave a Reply

Your email address will not be published.