>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
