MySQL 8 – Security

In previous chapters, we learned about the scalability of MySQL 8 and how to troubleshoot challenges when scaling MySQL 8. Apart from that, we also learned how to make MySQL 8 highly available for use. Nowadays, security is important for any application, right? When we talk about security, it includes account management, roles, privileges, and more. Considering these aspects, we will cover all of these topics in this chapter. This chapter mainly focuses on MySQL 8 database security and its related features. The following topics are covered in this chapter:

• Overview of security for MySQL 8
• Common security issues
• Access control in MySQL 8
• Account management in MySQL 8
• Encryption in MySQL 8
• Security plugins

Overview of security for MySQL 8

The term security is not bound to a specific topic; it covers a wide range of topics related to MySQL 8. Before starting a detailed discussion on it, let's mention some important points related to security:

• Consider security within a database where users and their privileges related to various database objects need to manage.
• Password security for users.
• Security configuration during the installation process, which includes various types of files, such as log files, data files, and many more. These files must be protected for their read/write operations.
• To handle system level failure scenarios, you must have a backup and recovery plan. This includes all the required files, such as database files, configuration files, and many more.
• Manage network security of the system where MySQL 8 was installed, which permits a limited number of hosts...

Common security issues

Before going into detail on complex issues, you must first understand some basic points that will help you prevent misuse or attacks.

General guidelines

In MySQL 8, all connections, queries, and operations performed by the user are based on the Access Control Lists (ACLs) security. The following are some general guidelines related to security:

• Don't allow access to the user table to any user except the root account. Manage user privileges with GRANT and REVOKE statements.
• Use encrypted protocol, such as SSH or SSL, in the case of data transfer over the internet. MySQL 8 supports SSL connections for that.
• Use proper defensive programming techniques at the time when the client is entering data into...

Access control in MySQL 8

Privileges are mainly used to authenticate users and will verify user credentials and check if a user is allowed for the requested operation or not. When we connect with the MySQL 8 server, it will first check the identity of the user by the provided host and user name. After connection, when a request comes in, the system will grant privileges according to the user's identity. Based on this understanding, we can say that access control contains two stages when we try to connect with the MySQL 8 server using the client program:

• Stage 1: The MySQL server will either accept or reject the connection, based on the provided identity
• Stage 2: After getting a connection from the MySQL server, when the user sends a request for performing any operation, the server will check whether sufficient privileges are available for the user or not

There are some...

Account management in MySQL 8

As the name implies, this topic describes how to manage user accounts in MySQL 8. We will describe how to add new accounts, how to remove accounts, how to define usernames and passwords for the accounts, and more.

Add and remove user accounts

MySQL 8 provides two different ways to create accounts:

• Using account management statements: These statements are used to create users and set their privileges; for example, with CREATE USER and GRANT statements, which inform the server to perform modifications on the grant table

• Using manipulation of grant tables: Using INSERT, UPDATE, and DELETE statements, we can manipulate the grant table

Out of these two approaches, account management statements...

Encryption in MySQL 8

When there is a need to transfer data over the network, it is a must to use encryption for the connection. If you are using unencrypted data, then someone who has access to the network can easily watch all of your traffic and can see what data is transferred between the client and server. To protect your data over the network, use encryption. Make sure the encryption algorithm used contains security elements to protect your connection from known attacks, like changing the order of a message or replay twice on data. Based on your application requirements, you can choose either an encrypted or unencrypted type connection. MySQL 8 performs encryption per connection using Transport Layer Security (TLS) protocol.

Configuring MySQL 8 to use encrypted connections

...

Security plugins

MySQL 8 provides several plugins to implement security. These plugins provide various features related to authentication protocols, password validation, secure storage, and much more. Let's discuss the various types of plugins in detail.

Authentication plugins

The following list of authentication plugins, with their details:

• Native pluggable authentication: To implement native authentication, MySQL 8 uses the mysql_native_password plugin. This plugin uses a common name in server and client both the side and inbuilt provided by MySQL 8 for both the server and client program.

• SHA-256 pluggable authentication

To implement SHA-256 hashing MySQL 8 provides two different plugins:

1. sha256_password: This...

Summary

In this chapter, we started with an overview of security, and then the ride began with the MySQL 8 security related features. First we discussed some common security issues, then we showed how to assign privileges and how to manage access control in MySQL 8. Encryption was also covered in this chapter, to secure your sensitive data. And finally, we covered some important security plugins, which are useful to implement security in MySQL 8.

It's now time to move on to our next chapter, where we will be configuring MySQL 8 for optimization. For optimization, we will cover different areas of the database, such as optimizing queries, optimizing tables, optimizing buffering and caching, and much more. Apart from server configuration, it also covers how to configure a client for optimization.