Software & Apps Apps How to Create Users And Grant Permissions In MySQL Share your database with other servers and users Share Pin Email Print PixabayOwner Apps Best Apps By Jack Wallen Writer Jack Wallen is a former Lifewire writer, an award-winning writer for TechRepublic and Linux.com, and the voice of The Android Expert. our editorial process LinkedIn Jack Wallen Updated February 12, 2020 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_dbUser: 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: Access the MySQL prompt with the command mysql -u root -p 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). Flush the database privileges with the command . Exit out of the MySQL prompt with the command 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: Access the MySQL prompt with the command 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). Flush the database privileges with the command Exit out of the MySQL prompt with the command 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: Access the MySQL prompt with the command 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). Flush the database privileges with the command Exit out of the MySQL prompt with the command 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.