The common mistake that newbies make when using MySQL is using the same username and password for all of their connected applications. Generally, I’ve seen the default “root” user used as the user in an application like WordPress with permissions to modify all databases. This is bad.
Before we proceed, we want to login as the root MySQL user (the super admin). We achieve this by typing: mysql -u root -p – you will be asked for the root user password. Once you are in, this is where the magic happens. We are now logged in as the MySQL super-boss.
Create a new MySQL Database
This is going to be a unique one use database for our web application. If we were installing a todo application using WordPress, we might want to call our database wptododb.
CREATE DATABASE wptododb;
Query OK, 1 row affected (0.00 sec)
We now have our newly created database. Replace “wptododb” with whatever name is relevant to you. Make it unique and descriptive, but short and punctual.
Create a new MySQL User and Assign Privileges
Creating unique users for every web application you have is a GOOD habit to get into. This means we can grant our unique user permission to only access the database it needs, if we were hacked, the damage would be contained to this one user and database.
Assuming you are logged in as the MySQL root user still we will now create a new user. Because we are creating a user for our imaginary WordPress todo application, we will use the database name and add “_user” to the end. This isn’t a strict rule and you can call this user whatever you want.
Create the MySQL user
CREATE USER wptododb_user@localhost;
Query OK, 0 rows affected (0.00 sec)We now have a user created, but no permissions or password (yet). Lets create a unique password for the user now.
Set a unique password for the MySQL user
SET PASSWORD FOR wptododb_user@localhost = PASSWORD("kjldksf9878937498273lkkkv");
Query OK, 0 rows affected (0.00 sec)Now we have our newly created user with a strong password, we have to assign privileges to our database we created earlier. It is worth pointing out that the value in the IDENTIFIED BY ” is the password we used earlier. We have to use it again to assign permission changes to this user.
Grant database privileges for the MySQL user
GRANT ALL PRIVILEGES ON wptododb.* TO wptododb_user@localhost IDENTIFIED BY 'kjldksf9878937498273lkkkv';
Query OK, 0 rows affected (0.00 sec)Conclusion
That’s it. You’ve got a new MySQL database, you’ve created a unique user and password, then you gave the user permission to only modify this one database. Congratulations, you’ve just leveled up your security skills.
