Securing the MariaDB Database Server

30 minutes
  • 5 Learning Objectives

About this Hands-on Lab

It is not enough for a System Administrator to just know how to install and enable a database server service. In today’s world of cyber-security threats, it is very important to also know how to properly secure the database server. It is even essential to know how to create databases, database users, and grant database access to users. In this activity, we will be installing the MariaDB server, configuring the service to be secure, creating a database administrator, creating a database, and granting that user administrative privileges to it.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Install the mariadb and mariadb-server Software Packages

Use YUM to install mariadb-server and mariadb packages:

sudo yum -y install mariadb-server mariadb
Ensure That the mariadb.service Starts and Is Enabled to Start at Boot Time

Use the systemctl command to enable and start the mariadb.service, then check on its status:

sudo systemctl enable mariadb.service --now
sudo systemctl status mariadb.service
Secure the mariadb database server by Setting the root Password, Removing Anonymous Users, Not Allow Remote Root Logins, Removing the Test Databases, and Reload Privileges

Use the mysql_secure_installation to set the the database server’s root password to L12i3n4u5Xrocks. Then follow the prompts to remove anonymous users, disallow remote root logins, remove the test database, and reload the privilege tables immediately:

sudo mysql_secure_installation
Create the Database people and the User dbadmin, Then Grant ‘dbadmin’@’localhost’ Full Access to the people Database

Use the command line database client command mysql to connect to the database as the root user with the password L12i3n4u5Xrocks. Create a database named people, a 'dbadmin'@'localhost' user identified by the password SeQuel2001. Then, grant all rights to the people database to the new user.

mysql -u root -p  # Enter the password when prompted: L12i3n4u5Xrocks
create database people;
create user 'dbadmin'@'localhost' identified by 'SeQeuL2001';
grant all on people.* to 'dbadmin'@'localhost';
Verify the dbadmin User has Full Access to the people Database

Use mysql to log in as the user dbadmin, and provide the SeQeuL2001 password. Use the people database, create and drop a minimal test table, to verify administrative privileges and exit:

mysql -u dbadmin -p
use people;
create table test (id text);
drop table test;

Additional Resources

We have been asked to install a new MariaDB server, and make sure that it is properly secured. We have also been asked to create a people database and a dbadmin user that has full administrative control of it.

We will need to install the mariadb-server and mariadb packages, and secure the server by setting the root password to L12i3n4u5Xrocks and running mysql_secure_installation. When interacting with this program, set anonymous users to be removed, remote root login to be disallowed, the test databases to be removed, and the privilege tables reloaded immediately.

We also need to create a database named people, and a database login for a user named ’dbadmin’@‘localhost’. This user should be granted full rights to manage the people database, and should have a password set to SeQueL2001. Use the mysql client to create this database, the dbadmin user, and to grant the proper privileges. To verify that the configuration is complete, we'll attempt to get administrative access to the people database as the ’dbadmin’@‘localhost’ user.

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!

Get Started
Who’s going to be learning?