MySQL Cookbook, 3rd Edition

Chapter 23. Security

Introduction

This chapter covers security-related topics:

• The mysql.user table that contains MySQL account information

• Statements for managing MySQL user accounts

• Password strength checking and policy

• Password expiration

• Finding and fixing insecure accounts

• Finding and removing anonymous accounts and accounts that permit connections from many hosts

If you like, you can skip over the initial section that describes the mysql.user table, but I think you’ll find that reading it will help you better understand later sections, which often discuss how SQL operations map onto underlying changes in that table.

Scripts shown in this chapter are located in the routines directory of the recipes distribution.

Note

Whether you use the MySQL 5.5, 5.6, or 5.7 release series, it is best to use a recent version within the series. Changes to the authentication system occur in early development versions that may produce results that differ from the descriptions here.

Note

Many of the techniques shown here require administrative access, such as the ability to modify tables in the mysql system database or use statements that require the SUPER privilege. For this reason, to carry out the operations described here, connect to the server as root rather than as cbuser.

Understanding the mysql.user Table

MySQL stores user account information in tables in the mysql system database. The user table is the most important because it contains account names and credentials. To see its structure, use this statement: ...