Anyone user with USE privilege can create a database in MySQL. :-( Reproducible: Always Steps to Reproduce: CREATE DATABASE `test` ; Database test has been created. GRANT USAGE ON * . * TO 'test'@'localhost' IDENTIFIED BY '****' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ; You have added a new user. GRANT ALL PRIVILEGES ON `test` . * TO 'test'@'localhost'; Now login as test. User has no privileges to create a DB. ----- GRANT USAGE ON * . * TO 'test'@'localhost' IDENTIFIED BY '****' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ; You have added a new user. CREATE DATABASE `test_test` ; Database test_test has been created. GRANT ALL PRIVILEGES ON `test_test` . * TO 'test'@'localhost'; You have updated the privileges for 'test'@'localhost'. Now login as test. Actual Results: CREATE DATABASE `test?test` ; Database test?test has been created. drwx------ 2 mysql mysql 48 Feb 24 11:06 test?test drwx------ 2 mysql mysql 48 Feb 24 11:03 test_test Now the user can create any tables in this DB and do whatever with it. Expected Results: Don
Anyone user with USE privilege can create a database in MySQL. :-( Reproducible: Always Steps to Reproduce: CREATE DATABASE `test` ; Database test has been created. GRANT USAGE ON * . * TO 'test'@'localhost' IDENTIFIED BY '****' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ; You have added a new user. GRANT ALL PRIVILEGES ON `test` . * TO 'test'@'localhost'; Now login as test. User has no privileges to create a DB. ----- GRANT USAGE ON * . * TO 'test'@'localhost' IDENTIFIED BY '****' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ; You have added a new user. CREATE DATABASE `test_test` ; Database test_test has been created. GRANT ALL PRIVILEGES ON `test_test` . * TO 'test'@'localhost'; You have updated the privileges for 'test'@'localhost'. Now login as test. Actual Results: CREATE DATABASE `test?test` ; Database test?test has been created. drwx------ 2 mysql mysql 48 Feb 24 11:06 test?test drwx------ 2 mysql mysql 48 Feb 24 11:03 test_test Now the user can create any tables in this DB and do whatever with it. Expected Results: Don´t allow user to create databases without required privileges! # emerge info Portage 2.0.51-r15 (default-linux/x86/2004.3, gcc-3.3.5, glibc-2.3.4.20040808-r1, 2.6.9-gentoo-r13 i686) ================================================================= System uname: 2.6.9-gentoo-r13 i686 AMD Athlon(tm) XP 2200+ Gentoo Base System version 1.6.9 Python: dev-lang/python-2.3.4-r1 [2.3.4 (#2, Feb 7 2005, 10:01:40)] dev-lang/python: 2.3.4-r1 sys-devel/autoconf: 2.59-r6, 2.13 sys-devel/automake: 1.7.9-r1, 1.8.5-r3, 1.5, 1.4_p6, 1.6.3, 1.9.4 sys-devel/binutils: 2.15.92.0.2-r1 sys-devel/libtool: 1.5.10-r4 virtual/os-headers: 2.6.8.1-r2 ACCEPT_KEYWORDS="x86" AUTOCLEAN="yes" CFLAGS="-O3 -march=athlon-xp -pipe -fomit-frame-pointer" CHOST="i686-pc-linux-gnu" CONFIG_PROTECT="/etc /opt/glftpd/etc /usr/kde/2/share/config /usr/kde/3/share/config /usr/share/config /var/bind /var/qmail/control" CONFIG_PROTECT_MASK="/etc/gconf /etc/terminfo /etc/env.d" CXXFLAGS="-O3 -march=athlon-xp -pipe -fomit-frame-pointer" DISTDIR="/usr/portage/distfiles" FEATURES="autoaddcvs autoconfig ccache collision-protect distlocks makecheck sandbox sfperms" GENTOO_MIRRORS="http://www.gigaload.org/gentoo.org/ http://gentoo.mirror.sdv.fr" MAKEOPTS="-j2" PKGDIR="/usr/portage/packages" PORTAGE_TMPDIR="/var/tmp" PORTDIR="/usr/portage" PORTDIR_OVERLAY="/usr/local/portage" SYNC="rsync://rsync.gentoo.org/gentoo-portage" USE="x86 3dnow acpi apache2 apm arts avi berkdb bitmap-fonts crypt curl emboss encode f77 fbcon firebird font-server foomaticdb fortran gd gd-external gdbm gif gpm gtk2 imap imlib innodb java jpeg junit libg++ libwww mad maildir mikmod mmx motif mpeg mysql ncurses nls nptl odbc oggvorbis opengl oss pam pdflib perl png pnp postgres python quicktime readline sasl sdl slang snmp socks5 spell sqlite sse ssl svga tcpd tiff truetype truetype-fonts type1-fonts unicode xml xml2 xmms xv zlib" Unset: ASFLAGS, CBUILD, CTARGET, LANG, LC_ALL, LDFLAGS
Please, could someone look at this? I have a webhosting server and phpMyAdmin even offers the name of the database that can be created. People are clicking on Create button and I have to delete tens of databases every day that are cluttering backups. Database naming scheme is dbname_domain_tld, so this is an extremely annoying problem. I don
Please, could someone look at this? I have a webhosting server and phpMyAdmin even offers the name of the database that can be created. People are clicking on Create button and I have to delete tens of databases every day that are cluttering backups. Database naming scheme is dbname_domain_tld, so this is an extremely annoying problem. I don´t want people to create unauthorized databases on the server. :-( It looks like MySQL stopped converting '_' (underscore) characters in DB names to escape sequences, I really don´t know.
GRANT USAGE ON * . * TO 'test'@'localhost' IDENTIFIED BY '****' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ; You have added a new user. GRANT ALL PRIVILEGES ON `test` . * TO 'test'@'localhost'; ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Your giving test ability to create db's right there.. I have no idea why you think this is a bug. Here is what happens when i try.. db root # mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 42638 to server version: 4.1.10-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> GRANT USAGE ON test.* TO 'test'@'localhost' IDENTIFIED BY 'test'; Query OK, 0 rows affected (0.09 sec) mysql> \q Bye db root # mysql -u test -ptest test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 42640 to server version: 4.1.10-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database foo; ERROR 1044 (42000): Access denied for user 'test'@'localhost' to database 'foo' Mysql isnt gonna let a big hole like this out and not patch it quickly.
Jeff, please read again and more carefully. CREATE DATABASE `test` ; --> user _cannot_ create additional databases CREATE DATABASE `test_test` --> use _can_ create additional databases All this with _exactly_ the same privileges granted to the user. You don
Jeff, please read again and more carefully. CREATE DATABASE `test` ; --> user _cannot_ create additional databases CREATE DATABASE `test_test` --> use _can_ create additional databases All this with _exactly_ the same privileges granted to the user. You don´t consider this to be a bug? I do. This _is_ a bug. And also please note that I am granting the user ALL privileges on test.* _only_, not on mysql.* ! He should have USAGE rights _only_ for anything else. Does USAGE privilege include creating databases? Certainly not, at least until now. :-(
Auditors, please check/reproduce/debunk
I see what your saying.. Its did it here too: 4.1.10 db root # mysql -u test -ptest test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 42751 to server version: 4.1.10-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE DATABASE `test_test`; Query OK, 1 row affected (0.03 sec) My appologies, I didnt quite see what your saying..
confirm this bug with mysql-4.0.23-r2 too. $ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.23a Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> grant usage on * . * to test@localhost identified by 'sekrit'; Query OK, 0 rows affected (0.08 sec) mysql> grant all privileges on test.* to test@localhost; Query OK, 0 rows affected (0.03 sec) mysql> quit Bye langthang@localhost ~ $ mysql -u test -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.0.23a Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database test?test; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '?test' at line 1 mysql> create database `test?test`; ERROR 1044: Access denied for user: 'test@localhost' to database 'test?test' mysql> quit Bye langthang@localhost ~ $ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 4.0.23a Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database test_test; Query OK, 1 row affected (0.02 sec) mysql> grant all privileges on test_test.* to test@localhost; Query OK, 0 rows affected (0.03 sec) mysql> quit Bye langthang@localhost ~ $ mysql -u test -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 4.0.23a Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database abcd; ERROR 1044: Access denied for user: 'test@localhost' to database 'abcd' mysql> create database test1; ERROR 1044: Access denied for user: 'test@localhost' to database 'test1' mysql> create database test2; ERROR 1044: Access denied for user: 'test@localhost' to database 'test2' mysql> create database test2test; Query OK, 1 row affected (0.02 sec) mysql> create database test3test; Query OK, 1 row affected (0.02 sec) mysql> create database test3test3; ERROR 1044: Access denied for user: 'test@localhost' to database 'test3test3' # ls /var/lib/mysql/ -l drwx------ 2 mysql root 528 Dec 7 15:44 mysql drwx------ 2 mysql root 48 Dec 7 15:44 test drwx------ 2 mysql mysql 48 Mar 1 10:42 test2test drwx------ 2 mysql mysql 48 Mar 1 10:42 test3test drwx------ 2 mysql mysql 48 Mar 1 10:38 test_test
Created attachment 52403 [details, diff] security patch confirmed, we do appear to be vulnerable to this, CAN-2004-0957. users can create databases with similar names to others containing underscores. rebuilding with attached patch prevents this. steps to reproduce: as root: mysql> create database foo_bar; mysql> grant all privileges on foo_bar.* to 'baz'@'localhost'; as baz: mysql> create databse this_wont_work; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'databse this_wont_work' at line 1 mysql> create database this_wont_work; ERROR 1044: Access denied for user: 'baz@localhost' to database 'this_wont_work' mysql> create database foobar; ERROR 1044: Access denied for user: 'baz@localhost' to database 'foobar' mysql> create database fooxbar; Query OK, 1 row affected (0.00 sec)
changing component to vulnerabilities.
Thx everyone. mysql-bugs please provide an updated ebuild.
Do ensure that you do not have the (default) privilege in mysql.db granting all users the right to do whatever they want with databases named with a "test_" prefix.
Comment on attachment 52403 [details, diff] security patch Apologies, this patch is already included in 4.0.22.
Tavis, as far as I can remember, this problem did NOT (almost for sure) exist in 4.0.21. It was fixed upstream - see http://dev.mysql.com/doc/mysql/en/news-4-0-21.html and http://bugs.mysql.com/bug.php?id=3933). I remember that the created DB had the undescore character escaped like 'test\_test'. How come that it is back now and in all versions up to 4.1.x? I am really confused.
Look at select * from mysql.db.. Its very telling of whats going on.
Jeff, excellent point. So I can see that the "old" DBs created in older MySQL version (probably =4.0.21) have their names escaped, those even older (MySQL <=4.0.20 ?) don
Jeff, excellent point. So I can see that the "old" DBs created in older MySQL version (probably =4.0.21) have their names escaped, those even older (MySQL <=4.0.20 ?) don´t, the new ones (MySQL >=4.0.22) don´t have them escaped as well. Hmm.
Jakub: I see, so the problem is a result of the unescaped wildcards from previous versions that need to be purged?
I think so.. I think its a old bug coming back to haunt us. ;)
I didn't really read all of this, but doesn't it sound like http://bugs.mysql.com/bug.php?id=5821 http://dev.mysql.com/doc/mysql/en/grant.html : Note: the '_' and '%' wildcards are allowed when specifying database names in GRANT statements that grant privileges at the global or database levels. This means, for example, that if you want to use a '_' character as part of a database name, you should specify it as '\_' in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern; for example, GRANT ... ON `foo\_bar`.* TO ....
Tavis: No, no. If I create a completely new user and database in 4.0.22-r2, the characters don
Tavis: No, no. If I create a completely new user and database in 4.0.22-r2, the characters don´t get escaped as well and this problem is reproduced. So the bug is back, I actually noticed this on a new database, not an old one.
closing this as invalid since upstream doesn't consider it a bug please reopen if you disagree
Sorry to reopen, but this was done automatically in the previous version and now it is not. This really does not make sense, so MySQL upstream takes this as a bug in one case and as a feature in the other and changes the behaviour arbitrarily? We need consistency, and '_' is a *terrible* choice for a wildcard. This is really confusing and dangerous. :-( Also, what am I supposed to do now? I have hundreds of such databases.
If you have a grant for a DB name `test_test`, then the user with that SHOULD be able to create test2test, test3test, etc. This is by design as the _ is a wildcard, and it has always been the case since before I was using MySQL 3.22. If you don't want them to be able to do this, then all of their grants MUST have the wildcard escaped, and be done as: GRANT ... ON `test\_test`.* TO .... The security advisory of CAN-2004-0957 was a specific case where the the above query was broken, and the wildcard escaping didn't work. To fix your setup, first backup your 'mysql' db, and write a small conversion script with similar conversion commands to the following: USE mysql; # mysql doesn't provide a regex replace :-( UPDATE db SET db=REPLACE(db,'_','\_'); UPDATE db SET db=REPLACE(db,'\\\\','\\'); # you need to do this replacement on every field among the mysql.* tables # where the _ and % wildcards are supported. This should be the full list, but I haven't double checked. host.{host,db} user.{host,user} db.{host,db,user} tables_priv.{host,db,user,table_name} columns_priv.{host,db,user,table_name,column_name} The automatic conversion you talk of where _ and % are escaped by default has NEVER existed in MySQL itself, but has existed in a number of other applications. I believe MySQL's own MySQL-Administrator does it.
OK, I am using phpMyAdmin. This is really only remotely related, but could someone verify the the automatic escaping of undescore character was removed from it recently?
Also - does this apply to table names (table_name)? E.g., if I grant rights to table_name, am I also granting the same rights to table?name (replace ? for any character) or not? Does this apply to usernames? E.g., if I grant privileges to some_user, am I granting the same rights to some?user or not? If so, then this great wildcard "feature" renders e.g. phpMyAdmin almost totally useless. *Me is pulling the rest of my hair out*. :-(((
As to what it applies to, consult the MySQL documentation more! http://dev.mysql.com/doc/mysql/en/request-access.html A corrected list of places where wildcards are supported is: host.{host,db} db.{host,db} user.{host} tables_priv.{host} columns_priv.{host} phpMyAdmin did have a bug with it's wildcard stuff, that was fixed for 2.6.1. http://sourceforge.net/tracker/index.php?func=detail&aid=1056706&group_id=23067&atid=377408
OK, thank you very much, Robin. I have hopefully managed to fix the whole mess now.
