Advanced MySQL CHAPTER 11 246 Using MySQL PART II In this chapter, we’ll cover some more advanced MySQL topics including advanced privileges, security, and optimization. The topics we’ll cover are • Understanding the privilege system in detail • Making your MySQL database secure • Getting more information about databases • Speeding things up with indexes • Optimization tips • Different table types Understanding the Privilege System in Detail Previously (in Chapter 8, “Creating Your Web Database”) we looked at setting up users and granting them privileges. We did this with the GRANT command. If you’re going to administer a MySQL database, it can be useful to understand. | Advanced MySQL CHAPTER 11 Using MySQL 246 Part II In this chapter we ll cover some more advanced MySQL topics including advanced privileges security and optimization. The topics we ll cover are Understanding the privilege system in detail Making your MySQL database secure Getting more information about databases Speeding things up with indexes Optimization tips Different table types Understanding the Privilege System in Detail Previously in Chapter 8 Creating Your Web Database we looked at setting up users and granting them privileges. We did this with the GRANT command. If you re going to administer a MySQL database it can be useful to understand exactly what GRANT does and how it works. When you issue a GRANT statement it affects tables in the special database called mysql. Privilege information is stored in five tables in this database. Given this when granting privileges on databases you should be cautious about granting access to the mysql database. One side note is that the GRANT command is only available from MySQL version onward. We can look at what s in the mysql database by logging in as an administrator and typing use mysql If you do this you can then view the tables in this database by typing show tables as usual. The results you get will look something like this . I Tables in mysql . columns_priv db host tables_priv user . Advanced MySQL 247 Chapter 11 Each of these tables stores information about privileges. They are sometimes called grant tables. These tables vary in their specific function but all serve the same general function which is to determine what users are and are not allowed to do. Each of them contains two types of fields scope fields which identify the user host and part of a database and privilege fields which identify which actions can be performed by that user in that scope. The user table is used to decide whether a user can connect to the MySQL server and whether she has any administrator privileges. The db and host tables .
