How to Create Users And Grant Permissions In MySQL

This article explains how to create users and grant permissions in MySQL. Instructions apply to all platforms including Windows, Mac, and Linux.

Create a MySQL Database

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
   

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"

3. Flush the database privileges with the command

   FLUSH PRIVILEGES;
   

4. Exit out of the MySQL prompt with the command

   quit
   

Create a User in MySQL

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.exe -u -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"

3. Flush the database privileges with the command

4. Exit out of the MySQL prompt with the command

Grant Permission in MySQL

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.exe –u –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

3. Flush the database privileges with the command

   FLUSH PRIVILEGES;
   

4. Exit out of the MySQL prompt with the command

   quit
   

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

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.