The Database Hacker's Handbook: Defending Database Servers

Examining the Logical Database Architecture

This section covers the following:

• Schemas, tables, views, and so on.
• Does the database support batched queries?
• Does the database support procedures, functions, and triggers?

MySQL Logical Database Architecture

MySQL has a relatively simple default system schema. The MySQL database contains the following tables:

mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| help_category   |
| help_keyword    |
| help_relation   |
| help_topic      |
| host            |
| tables_priv     |
| user            |
+-----------------+

Tables in MySQL that are created with the default MyISAM storage engine are stored by default in separate files, three files per table. For each database, there is a directory beneath the MySQL root directory with the same name as the database. Within this directory, there are normally three files per table, <tablename>.frm (the definition of the structure of the table), <tablename>.MYI (which contains details of any indexes available on the table), and <tablename>.MYD (which contains the actual data for the table).

This file-per-table approach leads to a peculiarity that is almost unique to MySQL. On most platforms, the files that make up the tables are not held locked, with the exception of the mysql/user.myd and .myi files. This means that, should users gain the ability to modify the table files, they are effectively modifying the table data itself. Also, on most platforms it is possible ...