This article explains how to create users and grant permissions in MySQL. Instructions apply to all platforms including Windows, Mac, and Linux.
What to Know
- Enter CREATE USER ‘wordpress_db_user’@’localhost’ IDENTIFIED BY ‘L!f3W!r3’;Substitute wordpress_db with database name and L!f3W!r3 with preferred user name.Next, enter GRANT ALL PRIVILEGES ON wordpress_db.* TO > enter database and user name.
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_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”
- Flush the database privileges with the command
- FLUSH PRIVILEGES;
- 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:
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;
Flush the database privileges with the command
FLUSH PRIVILEGES;
Exit out of the MySQL prompt with the command
quit
- Access the MySQL prompt with the command
- mysql.exe -u -p
- 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”
- Flush the database privileges with the command
- 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:
Access the MySQL prompt with the command
mysql.exe -u -p
Create the user with the command
CREATE USER ‘wordpress_db_user’@’localhost’ IDENTIFIED BY ‘L!f3W!r3’;
- Access the MySQL prompt with the command
- mysql.exe –u –p
- 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
- Flush the database privileges with the command
- FLUSH PRIVILEGES;
- 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.
mysql.exe –u –p
Grant the user access with the following command
GRANT ALL PRIVILEGES ON wordpress_db.* TO ‘wordpress_db_user’@’localhost’ IDENTIFIED BY ‘L!f3W!r3’;.
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.
Get the Latest Tech News Delivered Every Day