MySQL security: data integrity issues

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.

tags: , ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

8 Comments to "MySQL security: data integrity issues"

  1. Nils wrote:

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

  2. Roland Bouman wrote:

    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 would like for an Oracle-like ENABLE/DISABLE syntax on a constraint level. Then, the privilege system could be enhanced to allow or disallow the user doing ENABLE/DISABLE for particular constraints (the ones that the user is in charge of)

    BTW - I have a utility that may be of help checking for violations:
    http://forge.mysql.com/tools/tool.php?id=11

    #2 "it undermines the very foundation of using transactions."

    I don't agree. Reading uncommitted data does not do anybody harm - that is nobody but the current transaction. And even then the worst thing that can happen is that the current transaction thinks it can commit some inconsistent data, which is subsequently refused by the server. Data integrity is thus never compromised. (by this I mean that no database constraint will ever be violated, the only thing that might happen is that some transactions may run into an error more often, but I can't feel that is a data integrity problem, just an application problem).

    #3 "But MySQL, and through it, InnoDB, allow a strange thing: the transaction isolation level can be modified on the run."

    It's not confined to MySQL or InnoDB - it's defined in the standard.

    start transaction statement ::= START TRANSACTION [transaction characteristics]
    transaction characteristics ::= transaction mode*
    transaction mode ::= isolation level | transaction access mode | diagnostics size

    (See iso 9075:2003, 16.8)

  3. shlomi wrote:

    @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.

  4. Mike wrote:

    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.

  5. shlomi wrote:

    @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 encounter the following statements:
    START TRANSACTION WITH CONSISTENT SNAPSHOT;
    COMMIT AND CHAIN;
    Wasn't aware of their existence, they were new to me.

    Shlomi

  6. shlomi wrote:

    @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

  7. Roland Bouman wrote:

    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 if you really want to, you cannot commit one. Hence, it is not a data integrity issue - at least not from the POV of the database as a whole.

    The relaxed isolation levels were never born as a feature. What happened is that people discovered it is pretty hard to implement full ACID and at the same time not kill performance (that said I don't think that it matters a lot for InnoDB as long as you do not use SERIALIZABLE). Relaxing isolation is simply a way to allow some things to be a lot faster, so the practical solution is to quantize levels of isolation. As long as no data integrity is violated at commit time, I cannot see the flaw in it.

    kind regards,

    Roland

  8. shlomi wrote:

    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

Leave Your Comment

 
Powered by Wordpress and MySQL. Theme by openark.org