MySQL’s security model is not as elaborate as other popular databases. It’s missing quite a lot.<\/p>\n
I wish to point out what I think are some very disturbing security holes, which may affect the database integrity.<\/p>\n
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.<\/p>\n
I will consider three such issues.<\/p>\n
We will assume a database with two tables, and two users.<\/p>\n
\nGRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;\r\nGRANT SELECT, INSERT, UPDATE, DELETE ON `w2`.* TO 'w2user'@'%';<\/pre>\n<\/blockquote>\nWe 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.<\/p>\n
\nDROP DATABASE IF EXISTS w2;\r\nCREATE DATABASE w2;\r\nUSE w2;\r\n\r\nDROP TABLE IF EXISTS city;\r\nDROP TABLE IF EXISTS country;\r\n\r\nCREATE TABLE country (\r\n country_id int(11) not null auto_increment,\r\n name varchar(32) NOT NULL,\r\n PRIMARY KEY (country_id)\r\n)ENGINE=INNODB;\r\n\r\nCREATE TABLE city (\r\n city_id int(11) NOT NULL auto_increment,\r\n name varchar(32) NOT NULL,\r\n country_id int(11) not null ,\r\n PRIMARY KEY (city_id),\r\n INDEX country_id (country_id),\r\n FOREIGN KEY (country_id) REFERENCES country(country_id)\r\n ON DELETE CASCADE\r\n\r\n)ENGINE=INNODB;\r\n\r\nINSERT INTO country (country_id, name) values (1, 'gbr');\r\nINSERT INTO country (country_id, name) values (2, 'usa');\r\n\r\nINSERT INTO city (name, country_id) values ('london',1);\r\nINSERT INTO city (name, country_id) values ('liverpool',1);\r\nINSERT INTO city (name, country_id) values ('birmingham',1);\r\nINSERT INTO city (name, country_id) values ('ny',2);\r\nINSERT INTO city (name, country_id) values ('boston',2);<\/pre>\n<\/blockquote>\nBoth tables are InnoDB, to support transactions and foreign keys.<\/p>\n
Obviously, ‘root’ is allowed to do anything. But what harm can our unprivileged ‘w2user’ do?<\/p>\n
FOREIGN_KEY_CHECKS<\/h4>\n
The following INSERT<\/strong> should fail:<\/p>\n
\nINSERT INTO city (name, country_id) values ('no_city',1234567);<\/pre>\n<\/blockquote>\nBut look at the following:<\/p>\n
\nmysql> SELECT CURRENT_USER();\r\n+----------------+\r\n| CURRENT_USER() |\r\n+----------------+\r\n| w2user@% |\r\n+----------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> SET FOREIGN_KEY_CHECKS=0;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> INSERT INTO city (name, country_id) values ('no_city',1234567);\r\nQuery OK, 1 row affected (0.01 sec)\r\n<\/span>\r\nmysql> SET FOREIGN_KEY_CHECKS=1;\r\nQuery OK, 0 rows affected (0.00 sec)<\/pre>\n<\/blockquote>\nWhat 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?<\/p>\n
\nmysql> SELECT * FROM city;\r\n+---------+------------+------------+\r\n| city_id | name | country_id |\r\n+---------+------------+------------+\r\n| 1 | london | 1 |\r\n| 2 | liverpool | 1 |\r\n| 3 | birmingham | 1 |\r\n| 4 | ny | 2 |\r\n| 5 | boston | 2 |\r\n| 6 | no_city | 1234567<\/span> |\r\n+---------+------------+------------+\r\n6 rows in set (0.01 sec)<\/pre>\n<\/blockquote>\nYes, it did.<\/p>\n
Disabling FK checks is handy when importing large data from dump, or from CSV, when it is known<\/em> 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.<\/p>\n
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.<\/p>\n
tx_isolation<\/h4>\n
When using InnoDB, we can choose one of four isolation levels:<\/p>\n
\n
- READ-UNCOMMITTED<\/li>\n
- READ COMMITTED<\/li>\n
- REPEATABLE-READ (default):<\/li>\n
- SERIALIZABLE<\/li>\n<\/ul>\n
In READ-UNCOMMITTED<\/strong>, 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.<\/p>\n
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.<\/p>\n
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<\/strong> isolation, for example.<\/p>\n
But our w2user may decide to lower<\/em> her session’s isolation level below the global one. That is, MySQL may be configured to work at REPEATABLE-READ<\/strong>, but w2user is allowed to:<\/p>\n
\nmysql> SELECT CURRENT_USER();\r\n+----------------+\r\n| CURRENT_USER() |\r\n+----------------+\r\n| w2user@% |\r\n+----------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> SET tx_isolation='READ-UNCOMMITTED';\r\nQuery OK, 0 rows affected (0.00 sec)<\/pre>\n<\/blockquote>\nOur ‘root’ user does the following:<\/p>\n
\nmysql> SELECT CURRENT_USER();\r\n+----------------+\r\n| CURRENT_USER() |\r\n+----------------+\r\n| root@localhost |\r\n+----------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> START TRANSACTION;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> INSERT INTO country (name) VALUES ('nowhere');\r\nQuery OK, 1 row affected (0.00 sec)<\/pre>\n<\/blockquote>\nWhile the transaction is still open, w2user can:<\/p>\n
\nmysql> SELECT CURRENT_USER();\r\n+----------------+\r\n| CURRENT_USER() |\r\n+----------------+\r\n| w2user@% |\r\n+----------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> SELECT * FROM country;\r\n+------------+---------+\r\n| country_id | name |\r\n+------------+---------+\r\n| 1 | gbr |\r\n| 2 | usa |\r\n| 3 | nowhere <\/span>|\r\n+------------+---------+\r\n3 rows in set (0.00 sec)<\/pre>\n<\/blockquote>\nw2user used the READ-UNCOMMITED<\/strong>, 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<\/strong> level.<\/p>\n
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.<\/p>\n
Working with the REPEATABLE-READ<\/strong> isolation level should guarantee me some privacy<\/em> 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.<\/p>\n
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.<\/p>\n
sql_mode<\/h4>\n
I’ve written about sql_mode<\/strong> before, and here’s an example for a data integrity issue caused by weak security:<\/p>\n
In our example, sql_mode<\/strong> is set to ‘TRADITONAL<\/strong>‘, which maps to:<\/p>\n
\nSTRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER<\/pre>\n<\/blockquote>\nLet’s add a TIMESTAMP<\/strong> column to the country table:<\/p>\n
\nALTER TABLE country ADD COLUMN ts TIMESTAMP NOT NULL;<\/pre>\n<\/blockquote>\nWe now try to set a ‘0’ value for the time stamps (as user root):<\/p>\n
\nmysql> UPDATE country SET ts=NOW();\r\nQuery OK, 2 rows affected (0.42 sec)\r\nRows matched: 2 Changed: 2 Warnings: 0\r\n\r\nmysql> UPDATE country SET ts=0;\r\nERROR 1292 (22007): Incorrect datetime value: '0' for column 'ts' at row 1<\/pre>\n<\/blockquote>\nWe got the error becuase of the NO_ZERO_DATE<\/strong> part of our sql_mode<\/strong>.<\/p>\n
But, again, look at what w2user can do:<\/p>\n
\nmysql> SELECT @@sql_mode;\r\n+-------------------------------------------------------------------------------------------------------------------------------+\r\n| @@sql_mode |\r\n+-------------------------------------------------------------------------------------------------------------------------------+\r\n| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |\r\n+-------------------------------------------------------------------------------------------------------------------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> SET sql_mode='';\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> UPDATE country SET ts=0;\r\nQuery OK, 2 rows affected (0.01 sec)\r\nRows matched: 2 Changed: 2 Warnings: 0\r\n<\/span>\r\nmysql> SELECT * FROM country;\r\n+------------+------+---------------------+\r\n| country_id | name | ts |\r\n+------------+------+---------------------+\r\n| 1 | gbr | 0000-00-00 00:00:00<\/span> |\r\n| 2 | usa | 0000-00-00 00:00:00<\/span> |\r\n+------------+------+---------------------+\r\n2 rows in set (0.00 sec)<\/pre>\n<\/blockquote>\nSo, are ‘0’ values allowed for timestamps in our database or not? Turns out any simple user may decide differently.<\/p>\n
See my earlier posts here<\/a> and here<\/a>. Roland Bouman<\/a> also offers suggestions<\/a> for fixing this issue.<\/p>\n
Conclusion<\/h4>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[11,16,35],"class_list":["post-472","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-configuration","tag-security","tag-sql_mode"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-7C","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/472","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=472"}],"version-history":[{"count":22,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/472\/revisions"}],"predecessor-version":[{"id":530,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/472\/revisions\/530"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=472"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=472"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=472"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}