SECURITY AND USER MANAGEMENT IN MySQL (Article-15).

SECURITY AND USER MANAGEMENT IN MySQL.

SECURITY RISKS  IN MySQL.
When thinking about security within a MySQL installation, you should consider a wide range of possible topics and how they affect the security of your MySQL server and related applications:

• Comes with multiple users or internet connections.
• Must fully protect the entire server host ,not just MySQL.
• Many types of security “attackts”.
• MySQL uses ACLs based security.

–Also support for SSL-encrypted connections.

• MySQL security risks come in many forms.

–Network security risks.

–Operating system security risks.

–FiIesystem security risks.

Secure MySQL Installation

This is the first recommended step after installing MySQL server, towards securing the database server. This script facilitates in improving the security of your MySQL server by asking you to:

• set a password for the root account, if you didn’t set it during installation.
• disable remote root user login by removing root accounts that are accessible from outside the local host.
• remove anonymous-user accounts and test database which by default can be accessed by all users, even anonymous users.

After running it, set the root password and answer the series of questions by entering [Yes/Y] and press [Enter].

Change MYSQL Default Port

The Port variable sets the MySQL port number that will be used to listen on TCP/ IP connections. The default port number is 3306 but you can change it under the [mysqld] section as shown.

Disable LOCAL INFILE in MySQL

As part of security hardening, you need to disable local_infile to prevent access to the underlying filesystem from within MySQL using the following directive under [mysqld] section.

Enable MySQL Logging

Logs are one of the best ways to understand what happens on a server, in case of any attacks, you can easily see any intrusion-related activities from log files. You can enable MySQL logging by adding the following variable under the [mysqld] section.

Set Appropriate Permission on MySQL Files

Ensure that you have appropriate permissions set for all mysql server files and data directories. The /etc/my.conf file should only be writeable to root. This blocks other users from changing database server configurations.

Delete MySQL Shell History

All commands you execute on MySQL shell are stored by the mysql client in a history file: ~/.mysql_history. This can be dangerous, because for any user accounts that you will create, all usernames and passwords typed on the shell will recorded in the history file.

Don’t Run MySQL Commands from Command line.

As you already know, all commands you type on the terminal are stored in a history file, depending on the shell you are using (for example ~/.bash_history for bash). An attacker who manages to gain access to this history file can easily see any passwords recorded there.

It is strongly not recommended to type passwords on the command line, something like this:

When you check the last section of the command history file, you will see the password typed above.

Define Application-Specific Database Users

For each application running on the server, only give access to a user who is in charge of a database for a given application. For example, if you have a wordpress site, create a specific user for the wordpress site database as follows.

Use Additional Security Plugins and Libraries

MySQL includes a number of security plugins for: authenticating attempts by clients to connect to mysql server, password-validation and securing storage for sensitive information, which are all available in the free version.

You can find more here: https://dev.mysql.com/doc/refman/5.7/en/security-plugins.html

Change MySQL Passwords Regularly

This is a common piece of information/application/system security advice. How often you do this will entirely depend on your internal security policy. However, it can prevent “snoopers” who might have been tracking your activity over an long period of time, from gaining access to your mysql server.

Update MySQL Server Package Regularly

It is highly recommended to upgrade mysql/mariadb packages regularly to keep up with security updates and bug fixes, from the vendor’s repository. Normally packages in default operating system repositories are outdated.

After making any changes to the mysql/mariadb server, always restart the service.

MySQL Server User Security

Setting up proper accounts and privileges is imperative.

User security consists of 5 levels of account access privileges.

• Level 1 –USER
• Level 2  –DB
• Level 3 –TABLES
• Level 4 –COLUMNS
• Level 5  –PROCS

How to create a user :

We need to grant privileges to the new user which we have created because the new user would not have privileges. We can use GRANT statement to grant privileges to the user account. Its syntax would be as follows:

Explanation of the above syntax is as follows:

First, specify one or more privileges after the GRANT keyword. If we grant the user multiple privileges, each privilege is separated by a comma.

Next, specify the privilege_level that determines the level at which the privileges apply. MySQL supports global ( *.*), database ( database.*), table ( database.table) and column levels. If we use column privilege level, we must specify one or a list of the comma-separated column after each privilege.

Then, place the user that we want to grant privileges. If the user already exists, the GRANTstatement modifies its privilege. Otherwise, the GRANT statement creates a new user. The optional clause IDENTIFIED BY allows us to set a new password for the user.

After that, we specify whether the user has to connect to the database server over a secure connection such as SSL, X059, etc.

Finally, the optional WITH GRANT OPTION clause allows us to grant other users or remove from other users the privileges that we possess. In addition, we can use the WITH clause to allocate MySQL database server’s resource e.g., to set how many connections or statements that the user can use per hour. This is very helpful in the shared environments such as MySQL shared hosting.

Example:

In the example below we are creating a user and granting privileges to it:

The query below will give us the privileges for the new user account abcd@localhost.

Now, to grant all the privileges to the abcd@localhost user account, we can use the following statement:

Here, the ON *.* clause means all databases and all objects in the databases. The WITH GRANT OPTION allows abcd@localhost to grant privileges to other users.

Now if we will use the SHOW GRANTS statement again, we will see that the privileges of abcd@localhost have been updated.

To Check user privileges

 

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

 

Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform