How to protect MySQL and MARIADB database security in Linux VPS

Offers: Zstack cloud computing

Content Introduction

There are a large number of SQL database language implementations on Linux and Unix-like systems. MySQL and mariadb are two of the most popular options.

However, as with most software, these tools can be exposed to security risks if they are not properly configured. In this tutorial, we'll learn how to keep it safe with a series of basic steps.

For simple and intuitive content, we will use the MySQL server on Ubuntu 12.04 vs instances. But these techniques also apply to other Linux distributions as well as maradb.

Initial setup

MySQL will upgrade the first set of security measures during installation, that is, the need to set the root password.

sudo apt-get install mysql-server?????????????????????????? Configuring mysql-server-5.5??????????????????????????? ?  While not mandatory, it's highly recommended that and set a password for the? ?                                               MySQL administrative "root" user.  ?                                                                                 ?  ? ?                  If This field was left blank, the password would not be changed.  ?                                                                                 ?  ? ?  New password for the MySQL "root" User:?                                                                                 ?  ? ?  _______________________________________________________________________________ ?                                                                                 ?  ?                                                                         ?  ?                                       ?                                          ? ???????????????????????????????????????????????????????????????????????????????????

You can then set the root password, but here we set it directly.

After the installation is complete, we need to run several sets of scripts. First, we use the "mysql_install_db" script to create a set of directory layouts for our database.

sudo mysql_install_db

Next, run the "mysql_secure_installation" script. We can follow the prompts to remove each of the default options to eliminate the security risks it may pose to the production environment.

sudo mysql_secure_installation

Here we need to enter the root password set in the installation. Next, we need to answer a few questions, first, whether the root password needs to be changed.

Here we are not set up separately.

For other problems, you can press "Y" (yes) all the way.

As a result, no one will be able to log on to MySQL by default, the Administrator account Telnet mechanism is disabled, a partially unsafe test database is removed, and the currently running MySQL instance is updated to apply the above changes.

Safety precautions

To protect the security of MySQL and other types of systems, the most important thing is to provide access only when necessary. Specifically, data security can also be attributed to the tradeoff between convenience and security.

In this tutorial, we will be as biased as possible to the security side.

Secure security with MY.CNF files

The main configuration file for MySQL is "my.cnf", which is located in the "/etc/mysql/" directory on Ubuntu and is located in the "/etc/" directory on other VPS.

We changed some of the settings in this file to lock the MySQL instance.

Open the file as root, and if you are using a different system than this tutorial, you can change its path by using the following command:

sudo nano /etc/mysql/my.cnf

This first checks the "bind-address" setting, which is located in the "[Mysqld]" section. This setting should be set to a local closed-loop network device, or "127.0.0.1".

bind-address = 127.0.0.1

This ensures that MySQL does not receive any connections outside of the local device.

If you need to access this database from other devices, consider SSH access for database queries, local administration, and sending results via SSH tunneling.

The next thing we want to fix is to allow access to the underlying file system inside MySQL. This can lead to security issues and should therefore be closed when it is not necessary.

Within the same section of the file, add the following directives to disable the ability to load local files:

local-infile=0

If you have ample storage space and the database itself is small, you can log more information to detect abnormal activity.

Too-frequent records can have a performance impact, so you need to weigh this.

You can set the log variable within the same "[Mysqld]" section:

log=/var/log/mysql-logfile

Make sure that the MySQL log, error log, and MySQL log directory are not exposed externally:

sudo ls -l /var/log/mysql*-rw-r----- 1 mysql adm    0 Jul 23 18:06 /var/log/mysql.err-rw-r----- 1 mysql adm    0 Jul 23 18:06 /var/log/mysql.log/var/log/mysql:total 28-rw-rw---- 1 mysql adm 20694 Jul 23 19:17 error.log

Protect MySQL in-house

You can improve MySQL security in a variety of ways.

We need to enter the command in the MySQL prompt, so log in first.

mysql -u root -p

At this point we need to enter the root password previously set.

Protect passwords from host associations

First, make sure that there is no password or no host associated user in MySQL:

SELECT User,Host,Password FROM mysql.user;+------------------+-----------+-------------------------------------------+| user             | host      | password                                  |+------------------+-----------+-------------------------------------------+| root             | localhost | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 || demo-user        | %         |                                           || root             | 127.0.0.1 | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 || root             | ::1       | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 || debian-sys-maint | localhost | *ECE81E38F064E50419F3074004A8352B6A683390 |+------------------+----------- +-------------------------------------------+5 rows in set (0.00 sec)

As you can see, in our example, the user "Demo-user" has no password and is valid regardless of the host. This is obviously not very safe.

We can use the following command to set a password for it, you can change the "NewPassword" to the content of any password you need.

UPDATE mysql.user SET Password=PASSWORD(‘newPassWord‘) WHERE User="demo-user";

If we check the user table again, we will see that the demo user already has the password:

SELECT User,Host,Password FROM mysql.user;+------------------+-----------+-------------------------------------------+| user             | host      | password                                  |+------------------+-----------+-------------------------------------------+| root             | localhost | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 || demo-user        | %         | *D8DECEC305209EEFEC43008E1D420E1AA06B19E0 || root             | 127.0.0.1 | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 || root             | ::1       | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 || debian-sys-maint | localhost | *ECE81E38F064E50419F3074004A8352B6A683390 |+------------------+-----------+-------------------------------------------+5 rows in set (0.00 sec)

If you look at the "Host" field, you will find that we still have a "%", and this wildcard used by itself represents any host. We need to change it to "localhost":

UPDATE mysql.user SET Host=‘localhost‘ WHERE User="demo-user";

Check again to see that the user table now has the correct field settings.

SELECT User,Host,Password FROM mysql.user;

If our table contains any blank users, you should remove them.

To remove it, we can use the following command to remove a blank user from the Access table:

DELETE FROM mysql.user WHERE User="";

After the user table has been modified, we need to enter the following command to apply the new permission settings:

FLUSH PRIVILEGES;

Implementing an application-specific user

As with the use of isolated users running processes in Linux, MySQL can also use a similar isolation mechanism for protection.

Each app that uses MySQL should have its own users, and it will only have the necessary database access to meet its own operational needs.

When configuring a new app to use MySQL, we should create a database based on its needs:

create database testDB;Query OK, 1 row affected (0.00 sec)

Next, we should create a user to manage the database and assign only the necessary permissions to it. Different applications have different settings, some of which may require more permissions.

Create a new user using the following command:

CREATE USER ‘demo-user‘@‘localhost‘ IDENTIFIED BY ‘password‘;

We can use the following command to specify permissions for new users in the new table. For more information, see How to create a new user and corresponding permissions in MySQL:

GRANT SELECT,UPDATE,DELETE ON testDB.* TO ‘demo-user‘@‘localhost‘;

In this example, if a permission callback is required for the account, you can:

REVOKE UPDATE ON testDB.* FROM ‘demo-user‘@‘localhost‘;

If we need full permissions for a particular database, then:

GRANT ALL ON testDB.* TO ‘demo-user‘@‘localhost‘;

To display the user's current permissions, you must first implement the specified permissions with the flush privileges command. We can then query the permissions that the user currently has:

FLUSH PRIVILEGES;show grants for ‘demo-user‘@‘localhost‘;+------------------------------------------------------------------------------------------------------------------+| Grants for [email protected]                                                                                   |+------------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘demo-user‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19‘ || GRANT SELECT, UPDATE, DELETE ON `testDB`.* TO ‘demo-user‘@‘localhost‘                                            |+------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

When you are done with any changes, be sure to remember to refresh the permissions.

Change root User

You also need to change the root login name. If an attacker tries to log on as root MySQL, additional steps are required to locate the user name.

We can change the root login information with the following command:

rename user ‘root‘@‘localhost‘ to ‘newAdminUser‘@‘localhost‘;

We can view its changes with the same query command as the user database:

select user,host,password from mysql.user;

Refresh the permissions again to apply the changes:

FLUSH PRIVILEGES;

Note that you need to log in to MySQL with the newly created user name and perform administrative tasks:

mysql -u newAdminUser -p

Summarize

Although today's content is not exhaustive, you should be able to take a look at the common thinking of database security.

You can get more configuration and security information through the MySQL and MARIADB website. In addition, the various applications you choose to use will also provide relevant safety recommendations.

This article originates from Digitalocean Community. English original: How to Secure MySQL and MariaDB Databases in a Linux VPS by Justin Ellingwood

Translation: DIRADW

How to protect MySQL and MARIADB database security in Linux VPS