Summary

The MySQL user management mechanism is powerful, flexible, and often misused. The mysql database contains the various permission tables that allow access to be controlled based on user, host, and the action being performed. You can update the tables directly with SQL statements (in which case flushing the tables activates the changes) or through the more convenient GRANT and REVOKE statements.

The first task in a new installation should be to issue a root password. Until then, anyone can connect as root and have full access to everything. You can use mysqladmin, the SET statement, or GRANT to do this.

MySQL allows SSL connections for added security. This is not installed by default because it has performance implications.

You also learned some general principles for securing your data:

• Never issue a user the root password. They should always be connecting with another username.

• Never give anyone access to the user table, even for reading. Just viewing the encrypted password is enough to potentially allow a user full access.

• Always issue the minimum permissions you can. Issuing minimum permissions means that the user table contains N for all columns.

• For critical data, it must be possible to trace changes made by individuals. In general, people interact with the database through an application. The burden for managing access on an individual level then usually falls on the application.

• Ensure you cannot connect as the root user without a password from any server.

• Passwords should never be stored in plain text and should not be dictionary words.

• Check the user privileges every now and again and make sure that no one has granted anyone else inappropriate privileges.