In this article, Vikram gives us a sneak-peek under the hood of MySQL to see what makes it tick, all the while explaining the various MySQL subsystems and how they interact with each other. This excerpt comes from Chapter two of MySQL: The Complete Reference (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004).
The process of accessing a MySQL database can be broken down into two tasks: connecting to the MySQL server itself, and accessing individual objects, such as tables or columns, in a database. MySQL has built-in security to verify user credentials at both stages.
MySQL manages user authentication through user tables, which check not only that a user has logged on correctly with the proper username and password, but also that the connection is originating from an authorized TCP/IP address.
Once a user is connected, a system administrator can to bestow user-specific privileges on objects and on the actions that can be taken in MySQL. For example, you might allow fred@thiscompany.com to perform only SELECT queries against an inventory table, while allowing anna@thatcompany.net to run INSERT, UPDATE, and DELETE statements against the same table.
Security is also an issue for connections. Passwords and other important data transmitted across a network are vulnerable to interception by any one of the many network packet analyzers available on the market. By default, older versions of MySQL used a rather simple encryption method for usernames and passwords, which might have discouraged the casual hacker but would not have stopped someone who was willing to spend a little time cracking the encryption algorithms. As of version 4.1, a more secure protocol has been implemented that makes MySQL much more difficult to break into, even if a hacker has the ability to sniff and decode network traffic.
The actual data that travels over a network, such as query results, isn’t encrypted and is therefore open to viewing by a hacker. To secure your data, you can use one of the SSH (Secure Shell) protocols; you’ll need to install it on both the client applications and the operating system you’re using. If you’re using MySQL 4.0 or later, you can also use the SSL (Secure Socket Layer) encryption protocol, which can be configured to work from within MySQL, making it safe for use over the Internet or other public network infrastructures.
Remember: this is chapter two of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004). Vikram is the founder of Melonfire, and has had numerous articles featured on Dev Shed. Buy this book now.
/ 30 
