Manage MySQL
Database Security:
Best Practices & Tips

MySQL is one of the biggest, most popular database management systems to ever see the light of day, and with so much involved data and so many MySQL-based consumer applications, it is absolutely essential to keep it all safe from any sort of accidental leakage or malicious intent. Here are some best practices and tips that will help you shield your MySQL databases from it all.

Below we cover the essentials of database security in MySQL, including the following:

Change the default MySQL port
Drop test databases
Set up SSL connection
Remove all anonymous users
Avoid running MySQL with root-level privileges
Disable the LOAD DATA LOCAL INFILE command
Disable and remove MySQL history logs
Configure allowed IP addresses and hostnames
Row-level security in MySQL explained: Why it matters

Change the default MySQL port

In most cases, default values are the first victims of hacker attacks. That's why it is recommended to change the default MySQL port, create a new superuser, and delete all root accounts.

The default port can be found and modified in the my.ini file, which is typically located in the MySQL installation directory, e.g., C:\ProgramData\MySQL\MySQL Server 8.0\.

To quickly check your current port in dbForge Studio for MySQL, go to the Database menu > Server Variables and find admin_port on the list (see the screenshot for reference).

Consult our detailed guide to MySQL port connection to find out more about port types, commonly used MySQL ports, and connecting to a MySQL port from the command line.

Drop test databases

We don't need to tell you how vital test databases are; however, they may become easy targets because they are available to all users. Once you make sure you don't need a test database anymore, it's safer to delete it.

If necessary, you can get a list of all MySQL databases in order to identify test databases among them.

In dbForge Studio, you can drop a test database by running a corresponding query, or, alternatively, you can find and right-click the required test database in Database Explorer and simply select Delete on the shortcut menu.

Set up SSL connection

SSL and TLS, which stand for Secure Sockets Layer and Transport Layer Security respectively, are protocols that provide the security of communications. SSL and TLS certificates allow web browsers to identify and establish encrypted network connections.

dbForge Studio for MySQL makes it easy to connect to databases over SSL. First, you need to go to the Database menu > New Connection. You will be greeted by the Database Connection Properties dialog, where you can configure your MySQL connection. SSL is configured on the Security tab. Note that the latest TLS 1.3 is supported by the Studio.

It is also worth noting that you can use the SHOW VARIABLES LIKE statement to check the current TLS version and see whether you are connected to MySQL over SSL.

For further insights, refer to our guide to encryption in MySQL.

Remove all anonymous users

The Studio's integrated Security Manager provides you with versatile tools that help manage general security settings, roles, individual users, global privileges, object privileges, and SSL connection settings.

In Security Manager, among other things, you can check the list of users and remove anonymous users in case of need. To access it, go to the Database menu > Security Manager. The list of users can be found on the User List. Right-click the required user on the list to edit, duplicate, or remove them.

Avoid running MySQL with root-level privileges

Root-level privileges mean complete, unlimited access to the system, which makes them a valuable target for hackers. Instead, you can use the same Security Manager to configure privileges for users in the most convenient and flexible way. For instance, you can easily set up privileges for individual database objects on the Object Privileges tab, which you can see in the screenshot.

As an alternative way, you can use the command line to grant or revoke certain privileges on your MySQL databases. To grant different levels of privileges, you can use, for instance, GRANT ALL, GRANT SELECT, and GRANT INSERT commands. Similarly, you can remove existing privileges using the REVOKE command.

Disable the LOAD DATA LOCAL INFILE command

The LOAD DATA LOCAL INFILE command allows loading files from the client's local file system into the database. In case you want to prevent it, you can disable the command by setting local-infile=0 from the command line.

To check whether LOAD DATA LOCAL INFILE is turned off, you can use the same View Server Variables window. Go to the Database menu > Server Variables and find local_infile on the list (see the screenshot for reference). Its Value should be set to OFF.

Disable the LOAD DATA LOCAL INFILE command

Disable and remove MySQL history logs

If you don't want MySQL to remember the commands you previously entered via the mysql prompt (e.g., the commands that may contain passwords), you can clear the history and turn off the logging. To do that, run $ rm ~/.mysql_history. Then you need to set the MYSQL_HISTFILE variable to /dev/null.

If you are using dbForge Studio, take note that it may be keeping track of your query execution history. You can clear and turn off the history by going to the Tools menu > Options. In the Options dialog that opens, proceed to Environment > Documents > Query History and clear the Enable history checkbox. To remove the query history, click Clear Query History.

Configure allowed IP addresses and hostnames

To prevent unauthorized access, you can configure allowed IP addresses and hostnames; for instance, add users under specified IP addresses. To do that, run a corresponding CREATE USER command in the mysql prompt, as shown in the screenshot.

You can also consult our blog post and learn how to allow remote connections to MySQL databases.

Row-level security in MySQL explained: Why it matters

Row-level security (RLS) allows restricting access to specific rows of a table based on the role or permissions of a user. RLS is typically applied to enforce security policies and prevent unauthorized access to sensitive data. Here are the best practices for implementing RLS in MySQL.

Introduce separate user roles for different groups of users
Grant minimum permissions required to perform a task
Use parameterized queries to prevent SQL injections
Monitor access to sensitive data
Encrypt sensitive data
Test your security policies for different scenarios

Conclusion

We hope these tips have been helpful. After all, safety is a necessity, and you can't be too safe with sensitive data. And there is no safer way to develop and manage MySQL and MariaDB databases than dbForge Studio for MySQL, our comprehensive IDE designed to make your daily work a breeze. That said, we gladly invite you to download dbForge Studio for MySQL and enjoy a FREE 30-day trial!

Manage MySQL Database Security: Best Practices & Tips