>Description: Any LOCK TABLES command (both READ and WRITE), executed from a non-root MySQL user, would fail, giving a 'select command denied' error message. This showed up as Bugzilla being unable to update a bug's state, since locking the necessary tables would fail every time.
Unfortunately, bandwidth limitations prevent me from building a recent snapshot of the 4.0.x branch from BitKeeper sources, as Alexander Keremidarski <[EMAIL PROTECTED]> suggested in a private discussion. Thus, I am unable to check whether the problem is still present in recent versions of MySQL. The 'Web access to the MySQL BitKeeper repository' link in the '1.6.4 Useful MySQL-related links' section of the MySQL manual seems not to work: "Error 503: Can't find project root". >How-To-Repeat: With a 4.0.1 server and client, execute the following commands: Script started on Fri Jun 14 12:04:26 2002 Setting up interactive shell params.. [roam@straylight:p6 ~]$ 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.1-alpha SSL is not in use Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database locktest; Query OK, 1 row affected (0.02 sec) mysql> grant all on locktest.* to 'lockt'@'localhost' identified by 'lockp'; Query OK, 0 rows affected (0.03 sec) mysql> use locktest; Database changed mysql> create table t(id integer auto_increment not null primary key); Query OK, 0 rows affected (0.06 sec) mysql> insert into t values (); Query OK, 1 row affected (0.07 sec) mysql> insert into t values (); Query OK, 1 row affected (0.04 sec) mysql> select * from t; +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.00 sec) mysql> quit Bye [roam@straylight:p6 ~]$ mysql -u lockt -p locktest Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 to server version: 4.0.1-alpha SSL is not in use Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select * from t; +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.00 sec) mysql> lock tables t write; ERROR 1142: select command denied to user: 'lockt@localhost' for table 't' mysql> \q Bye [roam@straylight:p6 ~]$ exit exit Script done on Fri Jun 14 12:06:21 2002 The 'select command denied' was the one that should not have come up :) After applying the below fix, stopping, rebuilding, reinstalling and starting the server, and reconnecting to the same database: Script started on Fri Jun 14 12:12:48 2002 Setting up interactive shell params.. [roam@straylight:p6 ~]$ mysql -u lockt -p locktest Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.1-alpha SSL is not in use Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select * from t; +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.05 sec) mysql> lock tables t write; Query OK, 0 rows affected (0.00 sec) mysql> insert into t values (), (); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> lock tables t read; Query OK, 0 rows affected (0.09 sec) mysql> unlock tables; Query OK, 0 rows affected (0.01 sec) mysql> select * from t; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | +----+ 4 rows in set (0.00 sec) mysql> \q Bye [roam@straylight:p6 ~]$ exit exit Script done on Fri Jun 14 12:13:28 2002 As you can see, the fix allows the server to process the LOCK TABLES command successfully. >Fix: The problem seems to be in sql/sql_parse.cc, in the mysql_execute_command() function. The processing of SQLCOM_LOCK_TABLES calls check_grant(), which calls table_hash_search(). It would seem that table_hash_search() attempts to search a hash that is only initialized by a check_table_access() invocation. All the other command processing blocks within mysql_execute_command() call check_table_access() before check_grant(); adding this call to the SQLCOM_LOCK_TABLES processing block fixes the problem. --- sql/sql_parse.cc.orig Thu Jun 13 17:47:19 2002 +++ sql/sql_parse.cc Thu Jun 13 18:29:52 2002 @@ -2020,6 +2020,8 @@ } if (check_db_used(thd,tables) || end_active_trans(thd)) goto error; + if (check_table_access(thd, SELECT_ACL, tables)) + goto error; if (grant_option && check_grant(thd,SELECT_ACL | INSERT_ACL | UPDATE_ACL | DELETE_ACL,tables)) goto error; thd->in_lock_tables=1; >Submitter-Id: >Originator: Peter Pentchev <[EMAIL PROTECTED]> >Organization: >MySQL support: none >Synopsis: [PATCH] LOCK TABLES missing a needed check in 4.0.1 >Severity: serious >Priority: low >Category: mysql >Class: sw-bug >Release: mysql-4.0.1-alpha (FreeBSD port: mysql-server-4.0.1) >Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 4.0.1-alpha, for >portbld-freebsd4.6 on i386 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.1-alpha Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 2 hours 49 min 0 sec Threads: 1 Questions: 48 Slow queries: 0 Opens: 19 Flush tables: 2 Open tables: 19 Queries per second avg: 0.005 >Environment: System: FreeBSD straylight.oblivion.bg 4.6-RC FreeBSD 4.6-RC #3: Mon Jun 3 13:59:15 EEST 2002 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/RINGWORLD i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.3 20010315 (release) [FreeBSD] Compilation info: CC='cc' CFLAGS='-O -pipe ' CXX='cc' CXXFLAGS='-O -pipe -felide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 6320636 4 ήνθ 11:11 /usr/lib/libc.a lrwxr-xr-x 1 root wheel 9 4 ήνθ 11:11 /usr/lib/libc.so -> libc.so.4 -r--r--r-- 1 root wheel 579476 4 ήνθ 11:11 /usr/lib/libc.so.4 Configure command: ./configure --localstatedir=/var/db/mysql --without-debug --without-readline --without-bench --without-extra-tools --with-libwrap --with-openssl --with-low-memory '--with-comment=FreeBSD port: mysql-server-4.0.1' --enable-assembler --with-berkeley-db --with-charset=cp1251 --with-extra-charsets=all --prefix=/usr/local i386-portbld-freebsd4.6 Perl: This is perl, version 5.005_03 built for i386-freebsd --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php