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 to read the password hashes from the mysql/user.MYD file, even while the database is in use.
Another interesting consequence of the file-per-table approach is that it is possible to add tables to the database without needing to execute CREATE TABLE. If you have the files that make up a table, you can normally simply copy them into the appropriate directory and the tables will immediately be available. The same follows for deleting tables, though depending on whether the table is in use, MySQL may lock it. If you have access to the files, you can effectively edit the data ”this shouldn't really be surprising, but its worth remembering because it's much easier to do this in MySQL than in most other DBMS.
Storage Engines
MySQL supports a variety of "storage engines." These components perform the task of physically storing the data within a table (and associated indexes) on the disk. The engines available in MySQL 4.0.20 are
MyISAM (the default)
Merge
Memory
BDB
ISAM
InnoDB
In terms of security they offer different features and have different associated security properties. A brief discussion of the features of each is listed in Table 17-1.
| Engine | Notes |
|---|---|
| MyISAM | This is the default engine. It stores data in three files: <tablename>_.frm (a description of the format of the table), <tablename>.MYD (the actual data), and <tablename>.MYI (any indexes defined on the table). It does not support transactions. |
| Merge | The merge engine was introduced in version 3.23.25. A "merge" table is a collection of MyISAM tables that can be used as though they were a single table. All of the "merged" tables must be identical, meaning that their column definitions must be precisely the same. An additional restriction on merge tables is that all of the tables must be in the same database (though this restriction was removed in version 4.1.1). |
| Memory | This engine allows tables to be created and manipulated in volatile memory. This is obviously very quick to access, but should be used sparingly. The creation of a memory table results in the creation of a <tablename>.frm file in the relevant database directory, but obviously the actual data held in the table will disappear if the MySQL server is stopped . |
| BDB | Berkeley Data Base tables support transactions. They are not supported on every platform, and even on supported platforms the code for the engine may not be present in your binary package. In many respects they are similar to InnoDB tables. |
| ISAM | This is the original MySQL storage engine. Its use is now deprecated, though it may be useful in situations where you have to share data between a new and an old version of MySQL. It has several built-in restrictions, such as a maximum file size of 4GB, a lack of OS binary compatibility, it cannot be used with the BACKUP or RESTORE statements, and so on. |
| InnoDB | InnoDB is the transactional storage engine of choice in MySQL. It is built into MySQL versions 4.0 onward, though it is available in older, 3.23 versions if you change the default configuration slightly. It is the "industrial strength" storage engine for databases that require both transactional and referential integrity, coupled with good performance. According to the MySQL documentation, InnoDB is the storage engine that backs the popular "slashdot" site, which stores over 1TB of data, and another site apparently achieves an average load of 800 inserts /updates per second using this storage engine. |
Filesystem Layout
All table types in MySQL result in the storage of a .frm file in the appropriate database directory. All other file or system interaction is specific to the storage engine in question.
On operating systems that support native symbolic links, such as Linux, it is possible to use symbolic links to relocate the data files in a directory other than the MySQL data directory.
On Windows, MySQL supports customized symbolic link syntax. If you create a file in the data directory named mydatabase.sym, where mydatabase is the desired name of the database, and put the path to a directory into the file, like this:
c:\data\mydatabase
MySQL will use the directory c:\data\mydatabase for the mydatabase database.
Query Batching
MySQL supports query batching from the command line, but appears not to support it at the level of individual calls to execute a string of SQL. For instance, a single call to mysql_query() from a PHP application will not allow submission of multiple queries.
In terms of writing custom code to extend MySQL, versions 3.23 onward support User Defined Functions (UDFs), which are essentially functions implemented in C/C++ that reside in dynamically loadable libraries.
Stored procedures are scheduled for implementation in version 5.1.
Examining Users and Groups
This section covers the following:
-
Where is user and group account information stored?
-
Who are the powerful users?
-
How are passwords encrypted?
-
Cryptographic analysis of hashing algorithms with regard to hash chaining and building optimized password crackers.
The MySQL user privilege system is relatively straightforward and transparent, yet surprisingly powerful. In common with most DBMSes, it is possible to restrict user access on an individual field in a table, as well as to a set of predetermined system privilege levels, governing things like the ability to interact with the filesystem and shut down the database. One of the unusual aspects of the MySQL privilege model is that privilege depends not just on the username and password specified, but also on the host that was used to connect to the database server.
A restriction of the MySQL privilege model (at least in the current production versions) is that it isn't possible to define row-level security. In other words, although you can give users access to specific fields in a table, you can't give them access to specific rows.
The tables that are relevant to user (and host) privileges are user, host, db, tables_priv, and columns_priv. These tables have two purposes: to determine whether users and hosts should be allowed to connect to the server, and whether a given user can perform a given operation (from a given host).
The user table is responsible for the first of these two verifications. The description of the table follows:
mysql> describe mysql.user; +-----------------------+-----------------------------------+------+-----+---------+-------+ Field Type Null Key Default Extra +-----------------------+-----------------------------------+------+-----+---------+-------+ Host varchar(60) PRI User varchar(16) PRI Password varchar(41) Select_priv enum('N','Y') N Insert_priv enum('N','Y') N Update_priv enum('N','Y') N Delete_priv enum('N','Y') N Create_priv enum('N','Y') N Drop_priv enum('N','Y') N Reload_priv enum('N','Y') N Shutdown_priv enum('N','Y') N Process_priv enum('N','Y') N File_priv enum('N','Y') N Grant_priv enum('N','Y') N References_priv enum('N','Y') N Index_priv enum('N','Y') N Alter_priv enum('N','Y') N Show_db_priv enum('N','Y') N Super_priv enum('N','Y') N Create_tmp_table_priv enum('N','Y') N Lock_tables_priv enum('N','Y') N Execute_priv enum('N','Y') N Repl_slave_priv enum('N','Y') N Repl_client_priv enum('N','Y') N ssl_type enum('','ANY','X509','SPECIFIED') ssl_cipher blob x509_issuer blob x509_subject blob max_questions int(11) unsigned 0 max_updates int(11) unsigned 0 max_connections int(11) unsigned 0 +-----------------------+-----------------------------------+------+-----+---------+-------+ 31 rows in set (0.00 sec) The user table contains one row per user entry. The first field, host, contains a wildcard expression that describes the hosts that the user in question is allowed to log on from. The user field is the user's username, and the password field is the user's password hash. This field is 16 characters wide in versions prior to 4.1, and 41 characters wide in version 4.1 and onward. So already you can see that MySQL supports a feature that few other databases do: host verification. The host field can be specified as a fully qualified DNS name (such as client.example.com), a wildcard expression to encompass every host in a DNS domain (such as %.example.com), an IP address (such as 10.1.1.1), or an IP address with a subnet mask (such as 192.58.197.0/255.255.255.0).
The user field can also be an empty string, meaning that any username is valid.
All of the system privilege values are determined by the user table. A brief rundown of these is provided in Table 17-2.
| ALTER | Alter_priv | Change the schema of a table |
| DELETE | Delete_priv | Delete data from a table |
| INDEX | Index_priv | Create an index on a table |
| INSERT | Insert_priv | Insert data into a table |
| SELECT | Select_priv | Select data from a table |
| UPDATE | Update_priv | Update data in a table |
| CREATE | Create_priv | Create databases, tables, or indexes |
| DROP | Drop_priv | Delete databases or tables |
| GRANT | Grant_priv | Grant privileges to databases or tables |
| REFERENCES | References_priv | Databases or tables |
| CREATE TEMPORARY TABLES | Create_tmp_table_priv | Server administration |
| EXECUTE | Execute_priv | Server administration |
| FILE | File_priv | File access on server host |
| LOCK TABLES | Lock_tables_priv | Server administration |
| PROCESS | Process_priv | Server administration |
| RELOAD | Reload_priv | Server administration |
| REPLICATION CLIENT | Repl_client_priv | Server administration |
| REPLICATION SLAVE | Repl_slave_priv | Server administration |
| SHOW DATABASES | Show_db_priv | Server administration |
| SHUTDOWN | Shutdown_priv | Server administration |
| SUPER | Super_priv | Server administration |
Once it has been determined that a user can connect to the server, we move on to the second purpose of the tables ”the verification of whether a user can perform a given operation. The various remaining tables control a user's privileges at various levels of granularity. The coarsest granularity is privileges per database, which are determined by the db table:
mysql> describe mysql.db; +-----------------------+---------------+------+-----+---------+-------+ Field Type Null Key Default Extra +-----------------------+---------------+------+-----+---------+-------+ Host char(60) PRI Db char(64) PRI User char(16) PRI Select_priv enum('N','Y') N Insert_priv enum('N','Y') N Update_priv enum('N','Y') N Delete_priv enum('N','Y') N Create_priv enum('N','Y') N Drop_priv enum('N','Y') N Grant_priv enum('N','Y') N References_priv enum('N','Y') N Index_priv enum('N','Y') N Alter_priv enum('N','Y') N Create_tmp_table_priv enum('N','Y') N Lock_tables_priv enum('N','Y') N +-----------------------+---------------+------+-----+---------+-------+ 15 rows in set (0.56 sec) You can also specify privileges per host, which is useful in situations where you have a trusted network and a less-trusted network connecting to the database ”for example, if a MySQL server is the backend for a web site you might specify that all hosts could select, but only a specific trusted update host could insert, update, and delete.
The hosts table looks like this:
mysql> describe mysql.host; +-----------------------+---------------+------+-----+---------+-------+ Field Type Null Key Default Extra +-----------------------+---------------+------+-----+---------+-------+ Host char(60) PRI Db char(64) PRI Select_priv enum('N','Y') N Insert_priv enum('N','Y') N Update_priv enum('N','Y') N Delete_priv enum('N','Y') N Create_priv enum('N','Y') N Drop_priv enum('N','Y') N Grant_priv enum('N','Y') N References_priv enum('N','Y') N Index_priv enum('N','Y') N Alter_priv enum('N','Y') N Create_tmp_table_priv enum('N','Y') N Lock_tables_priv enum('N','Y') N +-----------------------+---------------+------+-----+---------+-------+ 14 rows in set (0.05 sec) The tables_priv and columns_priv tables describe the privileges available on specific tables (Insert, Update, Delete, Create, Drop, Grant, References, Index, Alter) and columns (Select, Update, Delete, References) to individual users and hosts:
mysql> describe mysql.tables_priv; +-------------+--------------------------------------------------------- --------------------------------------+------+-----+---------+-------+ Field Type Null Key Default Extra +-------------+--------------------------------------------------------- --------------------------------------+------+-----+---------+-------+ Host char(60) PRI Db char(64) PRI User char(16) PRI Table_name char(60) PRI Grantor char(77) MUL Timestamp timestamp YES NULL Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','Referen ces','Index','Alter') Column_priv set('Select','Insert','Update','References') +-------------+--------------------------------------------------------------------------- --------------------+------+-----+---------+-------+ 8 rows in set (0.00 sec) mysql> describe mysql.columns_priv; +-------------+----------------------------------------------+------+-----+---------+-------+ Field Type Null Key Default Extra +-------------+----------------------------------------------+------+-----+---------+-------+ Host char(60) PRI Db char(64) PRI User char(16) PRI Table_name char(64) PRI Column_name char(64) PRI Timestamp timestamp YES NULL Column_priv set('Select','Insert','Update','References') +-------------+----------------------------------------------+------+-----+---------+-------+ 7 rows in set (0.39 sec) All in all, the MySQL privilege model is fairly comprehensive and moderately granular, but as with most database privilege models, there are certain aspects of the behavior of the database that are not subject to access controls, and can be exercised by any user, whatever their privilege level.
The relative power of users is determined by the columns in the mysql.user table, such as GRANT_PRIV, SUPER_PRIV, and so on. There are no default users in MySQL ”or rather, there is, by default, no password protection in MySQL and so every user could be considered to be a super-user.
Passwords are encrypted in different ways depending on their format. If the password is a 41-character string beginning with the character *, then it is the doubly-SHA1- hashed plaintext password, that is
SHA1( SHA1( password ) )
If it is a 16-character string, the password is a proprietary (and weak) MySQL hash of the password.
Both of these password hash formats are easily brute-forced, so the use of long and obscure passwords is encouraged ”at least ten characters in length and containing at least one digit and punctuation symbol.
In addition to the weak password hashing mechanisms, the authentication protocol itself has been known to have security problems in the past. Normally, it is possible to brute-force the password on the basis of a single sniffed successful connection.
EAN: 2147483647
Pages: 156