How to Create Users And Grant Permissions In MySQL

Share your database with other servers and users

PixabayOwner

MySQL is an incredibly powerful database server platform. With this database you can power tools like Wordpress, Nextcloud, OrangeHRM, and so many more. Installing MySQL is fairly straightforward, as is accessing the MySQL prompt, and creating a database. But what happens when you need to create a new database user and grant that user permission to access a database? That’s when it can get a bit tricky. Luckily, it’s not terribly difficult.

When you install a platform like Wordpress, it requires a database (most often a MySQL database). During the installation of a system like Wordpress, you’ll be asked to provide:

  • The name of the database to be used.
  • The name of a user with permission to access the database.
  • The password for the user that can access the database.

During the installation of the MySQL database, you'll be asked to create a password for the admin user. You might be asking yourself, “Why not just use the admin user for this process?” The answer is simple: security. That MySQL admin user should only be used to administer the MySQL database server and its users, not as an account for the installation of third-party software. To that end, you should always create new users and grant the new user access to the third-party specific database. For example, if you’re installing Wordpress, you might create the following:

  • Database: wordpress_db
  • User: wordpress_db_user

You would then create a password for wordpress_db_user and grant that user full access to the wordpress_db database.

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

  1. Access the MySQL prompt with the command

    mysql -u root -p
    Screenshot of the MySQL command prompt.
  2. Type the MySQL admin password and use Enter/Return on your keyboard.

    Create the database with the command

    CREATE DATABASE wordpress_db; 
    (substitute "wordpress_db" with a database name to fit your needs).
    Screenshot of creating a MySQL database.
  3. Flush the database privileges with the command

    FLUSH PRIVILEGES;
    .
  4. Exit out of the MySQL prompt with the command

    exit

With the database in place, you can now create the user that will have access to the newly-created database. This is also done from the MySQL prompt. To create this new user, follow these steps:

  1. Access the MySQL prompt with the command

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

    Create the user with the command

    CREATE USER 'wordpress_db_user'@'localhost' IDENTIFIED BY ‘L!f3W!r3’;
    (substitute "wordpress_db_user" and "L!f3W!r3" with a username and password to fit your needs).
    Screenshot of creating a MySQL user.
  3. Flush the database privileges with the command

    FLUSH PRIVILEGES;
  4. Exit out of the MySQL prompt with the command

    exit

Now we need to grant the newly created wordpress_db_user permission to access the newly created wordpress_db database. This is accomplished with the following steps:

  1. Access the MySQL prompt with the command

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

    Grant the user access with the following command

    GRANT ALL PRIVILEGES ON wordpress_db.* TO 'wordpress_db_user'@'localhost' IDENTIFIED BY ‘L!f3W!r3’;.
    (substitute wordpress_db, wordpress_db_user, and L!f3W!r3 with a database, username and password to fit your needs).
    Screenshot of granting a MySQL user permission to access a database.
  3. Flush the database privileges with the command

    FLUSH PRIVILEGES;
  4. Exit out of the MySQL prompt with the command

    exit

At this point, the local user wordpress_db_user has full access to the wordpress_db database. So when you go to install Wordpress (or whatever server software you intend to install), you can use wordpress_db_user as the database username and L!f3W!r3 as the password.

Granting Remote Access

Screenshot of granting remote access to a database.

There’s one problem. The above permissions only work for the wordpress_db_user on the local machine. What if you’re database is housed on a remote server? For that, you need to alter the GRANT ALL PRIVILEGES command. Let’s say the machine you’re installing Wordpress (or whatever third-party server software) on is at IP address 192.168.1.100. In order to grant wordpress_db_user permission to access the database from that machine, the new GRANT ALL PRIVILEGES command would look like this:

GRANT ALL PRIVILEGES ON wordpress_db.* TO ‘wordpress_db_user’@’192.168.1.100’ IDENTIFIED BY ‘L!f3W!r3’;

As you can see, instead of granting full access to the wordpress_db on localhost, what we’ve done is grant the wordpress_db_user user on remote machine 192.168.1.100 full access to the wordpress_db database. That command will make it possible for you to install Wordpress (or whatever third-party server software you need) on the server at IP address 192.168.1.100 and have it access the wordpress_db MySQL database, as the wordpress_db_user.