Securing Mysql and Creating Users – Final
This tutorial is based on the previous installation of MySQL 5.7 on CentOS 7.5, which can be found here and shows how to secure the database, add users and grant permissions.
1. We will now use the implemented secure installer of MySQL 5.7 to harden our installation.
To get started, we have to type in
As soon as we hit enter, we will be asked for the root-password, which we got beforehand, with the command
sudo grep ‘temporary password’ /var/log/mysqld.log
After pasting it, we will be asked to create a new root-password. After typing it twice, we will have an output of the estimated strength of the password. If we are confident with the strength, we can skip the step for another new root password with any key except ‘y’.
The next step will be the removal of anonymous users from the database. It is common practice to remove anonymous access, unless absolutely required. To achieve this, confirm with ‘y’.
Afterwards you will be asked if remote access of the root account to the database shall be disabled and connection shall only be permitted from the local machine. As we don’t want someone to guess our root-password, we are going to confirm with ‘y’.
Up next will be the removal of the test database. As we are going to build a productive server, we won’t need it anymore and confirm this step with ‘y’ as well.
The final step of the secure installation is reloading the privilege tables. Maybe we took a break in setting things up, and something happened in the background already. With this step, all of our previous made decisions will be loaded into the database immediately.
After confirming the last step with y, MySQL will state the success of this operation.
Your initial database is now hardened against the most common attacks.
2. As we don’t want to constantly use our MySQL root-user, we are going to create an additional database and an additional user.
To do so, we need to connect to our MySQL database first.
sudo mysql –u root –p
You will now be prompted for the password, which you set in the previous step. If you managed to connect successfully, you will see the following output:
You are now in the MySQL-shell and can send commands to your database.
Let’s create another database first. In the MySQL-shell type
CREATE DATABASE corefinity_test;
MySQL will confirm the success of this command. This database will be used for all following examples.
Now we need to create a user, who has all privileges on this database and all it’s tables. In MySQL shell type
GRANT ALL PRIVILEGES ON corefinity_test.* TO ‘corefinity’@’localhost’ IDENTIFIED BY ‘011Corefinity_rules!!’;
This will create the database user corefinity with the password ‘corefinity_rules’, granting all privileges on the previously created database corefinity_test.
Then flush the privileges with
so that the user has immediate access and close the connection with ‘exit’.
To verify that everything worked, we can now try to connect to our database with our corefinity user by typing
sudo mysql -u corefinity –p
and the password ‘corefinity_rules’. We now have access to a specific database with a new user.
You can further restrict the access to databases, by just assigning specific rights (SELECT,UPDATE, etc.) to users, or even just giving the permission to read specific tables.