Virtual Tech Self-Study Message Board › Lesson 09, MySQL Marinate, Season 2
|Sheeri Kritzer C.||
Notes/errata/updates for Chapter 9:
See the official book errata at http://tahaghoghi.com... - Chapter 9 includes pages 297 - 350.
In the fourth paragraph of this chapter, starting with "Most applications don't need superuser privileges for day-to-day activities" they give you some reasons why you want to create users without the SUPER privilege. There are actually better reasons than the book gives, which are at the MySQL Manual page for the SUPER privilege
In the section "Creating and Using New Users" (p. 300) they say "There's no limit on password length, but we recommend using eight or fewer characters because this avoids problems with system libraries on some platforms." You should ignore this, this book was written in 2006 and modern system libraries can handle more than 8 characters in a password. Also ignore it when they say the same thing in the section "Understanding and Changing Passwords" (p. 324).
Also in that section at the very end, it talks about using % as a host wildcard character. I want to point out that if there are no ACL's set for a given host, MySQL will reject connections from that host – even "telnet host 3306" will fail. So if you avoid using %, you are actually slightly more secure.
In the "Anonymous Users" section (p. 315), one fact that is not mentioned is that for all users, including the anonymous user, any database that starts with "test" can be accessed and manipulated. So an anonymous user can create tables in the "test" database (or even "test_application") and fill it full of data, causing a denial of service when the disk eventually fills up. This fact is mentioned later in the chapter in the "Default User Configuration" section under "Linux and Mac OS X", but it should be known earlier.
The "mysqlaccess" utility described in the section of that name (p. 320) is usually not used. These days, folks prefer the pt-show-grants tool. You can see a blog post with some examples.
In the section on "Removing Users" (p. 324), it says that if all the privileges are revoked, and a user only has GRANT USAGE, "This means the user can still connect, but has no privileges when she does." This is untrue, as mentioned before, everyone can access and manipulate databases starting with "test".
The section "Managing Privileges with SQL" is deprecated (p. 339-346, up to and including "Activating Privileges"). It used to be, back when this was written, that few people used the GRANT statements and more people directly manipulated the tables. These days, it's the other way around, and due to problems like SQL injection, there are safeguards in place - for example, if you change the host of a user with an ALTER TABLE on the mysql.user table, the user will have all privileges dropped. Just about the only thing direct querying is used for is to find who has the Super_priv variable set to 'Y' in the user table.
Supplemental material: I have a presentation on security which includes ACLs and there are accompanying PDF slides.
This space is reserved for updates and questions about Chapter 9 of the Learning MySQL book being used in the MySQL Marinate virtual self-study course. If you want, you can go to the Master Discussion List for MySQL Marinate Season 2.
Edited by Sheeri Kritzer Cabral on Jul 8, 2013 7:31 PM