MySQL security: data integrity issues

MySQL’s security model is not as elaborate as other popular databases. It’s missing quite a lot.

I wish to point out what I think are some very disturbing security holes, which may affect the database integrity.

This post is not about Roles, Kerberos, IPs and such. It’s about simple MySQL features, which allow common, unprivileged users, to break data integrity by using unprotected session variables.

I will consider three such issues.

We will assume a database with two tables, and two users.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT SELECT, INSERT, UPDATE, DELETE ON `w2`.* TO 'w2user'@'%';

We have one ‘root’ user, and one very simple ‘w2user’, which can’t be accused of having too many privileges. The schema, with some sample data, follows.

DROP DATABASE IF EXISTS w2;
CREATE DATABASE w2;
USE w2;

DROP TABLE IF EXISTS city;
DROP TABLE IF EXISTS country;

CREATE TABLE country (
  country_id int(11) not null auto_increment,
  name varchar(32) NOT NULL,
  PRIMARY KEY  (country_id)
)ENGINE=INNODB;

CREATE TABLE city (
  city_id int(11) NOT NULL auto_increment,
  name varchar(32) NOT NULL,
  country_id int(11) not null ,
  PRIMARY KEY  (city_id),
  INDEX country_id (country_id),
  FOREIGN KEY (country_id) REFERENCES country(country_id)
                      ON DELETE CASCADE

)ENGINE=INNODB;

INSERT INTO country (country_id, name) values (1, 'gbr');
INSERT INTO country (country_id, name) values (2, 'usa');

INSERT INTO city (name, country_id) values ('london',1);
INSERT INTO city (name, country_id) values ('liverpool',1);
INSERT INTO city (name, country_id) values ('birmingham',1);
INSERT INTO city (name, country_id) values ('ny',2);
INSERT INTO city (name, country_id) values ('boston',2);

Both tables are InnoDB, to support transactions and foreign keys.

Obviously, ‘root’ is allowed to do anything. But what harm can our unprivileged ‘w2user’ do?

FOREIGN_KEY_CHECKS

The following INSERT should fail:

INSERT INTO city (name, country_id) values ('no_city',1234567);

But look at the following:

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| w2user@%       |
+----------------+
1 row in set (0.00 sec)

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO city (name, country_id) values ('no_city',1234567);
Query OK, 1 row affected (0.01 sec)

mysql> SET FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.00 sec)

What was that? w2user was allowed to temporarily disable foreign key checks, insert an otherwise invalid row, then re-enable checks, and no error was thrown? Wait, did the row really get inserted?

mysql> SELECT * FROM city;
+---------+------------+------------+
| city_id | name       | country_id |
+---------+------------+------------+
|       1 | london     |          1 |
|       2 | liverpool  |          1 |
|       3 | birmingham |          1 |
|       4 | ny         |          2 |
|       5 | boston     |          2 |
|       6 | no_city    |    1234567 |
+---------+------------+------------+
6 rows in set (0.01 sec)

Yes, it did.

Disabling FK checks is handy when importing large data from dump, or from CSV, when it is known to be valid. For example, when restoring a backup created with mysqldump, FK checks can be safely disabled since dumped data must have been valid. Disabling checks helps in reducing import time.

But I don’t think normal users should be allowed to set the FOREIGN_KEY_CHECKS variable. This should be restricted to users with the SUPER privilege.

tx_isolation

When using InnoDB, we can choose one of four isolation levels:

• READ-UNCOMMITTED
• READ COMMITTED
• REPEATABLE-READ (default):
• SERIALIZABLE

In READ-UNCOMMITTED, a transaction can read other open transactions uncommitted data. It’s usually not a good idea to use this isolation level when working with transactional engines, since it undermines the very foundation of using transactions.

But MySQL, and through it, InnoDB, allow a strange thing: the transaction isolation level can be modified on the run. I consider this to be peculiar and undesired. An isolation level imposes an application logic, which should not be changed. But MySQL also allows different isolation level on a per-connection basis.

Every session can work on a different isolation level. This may be a good idea, when a session wishes to be stricter than the rest of the code, by using the SERIALIZABLE isolation, for example.

But our w2user may decide to lower her session’s isolation level below the global one. That is, MySQL may be configured to work at REPEATABLE-READ, but w2user is allowed to:

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| w2user@%       |
+----------------+
1 row in set (0.00 sec)

mysql> SET tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)

Our ‘root’ user does the following:

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO country (name) VALUES ('nowhere');
Query OK, 1 row affected (0.00 sec)

While the transaction is still open, w2user can:

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| w2user@%       |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM country;
+------------+---------+
| country_id | name    |
+------------+---------+
|          1 | gbr     |
|          2 | usa     |
|          3 | nowhere |
+------------+---------+
3 rows in set (0.00 sec)

w2user used the READ-UNCOMMITED, hence was allowed to see the (soon to be rolled back?) ‘nowhere’ country. But that country was inserted by a session using the REPEATABLE-READ level.

Each session confirms to its isolation level rules, and the complaint is not about that. The complaint is with the fact that there’s a mess in our database.

Working with the REPEATABLE-READ isolation level should guarantee me some privacy in my transaction. My transaction may choose to delete all rows from a table, only to fill them back again, and none (a small white lie here, since locking is also involved) is the wiser. The privacy notion is so inherent, that it’s shocking to learn that any other connection can knowingly choose to ignore my privacy and see any changes I make. This is why I consider this as a security breach, and not just some isolation nuance.

In my opinion, the isolation level should not be dynamic at all. It must not be changed while the database is running. Perhaps I’m missing some interesting scenario where it would be desired, but the majority of applications would not find this feature beneficial.

sql_mode

I’ve written about sql_mode before, and here’s an example for a data integrity issue caused by weak security:

In our example, sql_mode is set to ‘TRADITONAL‘, which maps to:

STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER

Let’s add a TIMESTAMP column to the country table:

ALTER TABLE country ADD COLUMN ts TIMESTAMP NOT NULL;

We now try to set a ‘0’ value for the time stamps (as user root):

mysql> UPDATE country SET ts=NOW();
Query OK, 2 rows affected (0.42 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> UPDATE country SET ts=0;
ERROR 1292 (22007): Incorrect datetime value: '0' for column 'ts' at row 1

We got the error becuase of the NO_ZERO_DATE part of our sql_mode.

But, again, look at what w2user can do:

mysql> SELECT @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE country SET ts=0;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM country;
+------------+------+---------------------+
| country_id | name | ts                  |
+------------+------+---------------------+
|          1 | gbr  | 0000-00-00 00:00:00 |
|          2 | usa  | 0000-00-00 00:00:00 |
+------------+------+---------------------+
2 rows in set (0.00 sec)

So, are ‘0’ values allowed for timestamps in our database or not? Turns out any simple user may decide differently.

See my earlier posts here and here. Roland Bouman also offers suggestions for fixing this issue.

Conclusion

The above three examples show how simple users can break data integrity due to very permissive MySQL logic. Even when the database is carfully tuned and secured, there’s no way to prevent non privileged users from damaging its integrity.