Skip to content

# MySQL security: data integrity issues

MySQL Configuration Security sql_mode January 21, 2009

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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
8
Leave a Reply

8 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
4 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Nils

Are there official bug reports for that? I guess they must have been around for a long time.

Guest
Roland Bouman

Hi Shlomi, nice post as always. And, some comments (as always too 😉 #1 “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.” I think this is only partially a matter of privileges. IMO, the gravest problem with disabling foreign key checks is that re-enabling does not check for violations. The second biggest beef I have with it is that it is all-or-nothing. I would like to do it on a constraint level, or otherwise table level. IMO, those 2 are the real WTFs. I… Read more »

Author
shlomi

@Nils,

There are several bug reports for sql_mode, none that I know of for FOREIGN_KEY_CHECKS, and I’m not sure how to approach the tx_isolation issue, as this is clearly a feature, not a bug 🙁

I’ll try and make a good argument and file a bug on the FOREIGN_KEY_CHECKS issue.

Guest
Mike

If you weren’t allowed to disable foreign keys as a user you wouldn’t be able to import data as anyone other than root.

Author
shlomi

@Roland, As always, thanks for your comments, #1 I’ve also got a utility which relates to foreign keys, and which DELETEs or does a SET NULL upon violation. It’s part of a small toolkit I will soon release. #2 “It’s not confined to MySQL or InnoDB – it’s defined in the standard.” That’s interesting, I didn’t know that. But it doesn’t change anything. Many things in the standard are not implemented in MySQL, and vice versa. “start transaction statement ::= START TRANSACTION [transaction characteristics]” In MySQL it goes by SET TRANSACTION … While looking at it, I was surprised to… Read more »

Author
shlomi

@Mike,
“you wouldn’t be able to import data as anyone other than root”
1. Nothing would prevent you from importing data, you would just be denied disabling the foreign keys. Importing would be slower.
2. Any user with the SUPER privilige would have been able to do that, not only ‘root’.

Shlomi

Guest
Roland Bouman

Hi Shlomi, nice to hear your working on that tool! I’ll be glad to check it out. Now, my main point re. transaction isolation levels is not so much whether it is or is not standard. My reaction was mainly against your specific wording: “But MySQL, and through it, InnoDB, allow a strange thing” which to me seemed to imply it is specific to MySQL. It is not – standard SQL allows it. More importantly though, at the end of the day, the isolation levels have got nothing to do with data integrity. Even though you can see inconsistent states… Read more »

Author
shlomi

Hi Roland,

“My reaction was mainly against your specific wording…”
I accept.

“at the end of the day, the isolation levels have got nothing to do with data integrity…”
Consider: if I were to INSERT some data depending on current state (i.e. depending on SELECT resukt), then by reading uncommitted data I lose integrity. Maybe it’s more of application integrity, not database integrity.

Shlomi

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.