Bug #14726 Crash when mysqldump encounters a view while privileges limited.
Submitted: 7 Nov 2005 23:26 Modified: 2 Dec 2005 3:43
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.15 OS:FreeBSD (FreeBSD 5.4)
Assigned to: Oleksandr Byelkin

[7 Nov 2005 23:26] [ name withheld ]
Description:
mysqldump causes mysql to crash when it encounters a view while running under restricted user.

Command line: 

mysqldump --user=SomeUserName --password=SomePassword --host=127.0.0.1 --quote-names --databases MyDBName

When running as root, everything is OK.

When running as SomeUserName, and that user has every single privilege granted, i.e. GRANT SELECT ,UPDATE ,DELETE ,CREATE ,DROP ,RELOAD ,SHUTDOWN ,PROCESS,FILE ,REFERENCES ,INDEX ,ALTER ,SHOW DATABASES ,SUPER ,CREATE TEMPORARY TABLES ,LOCK TABLES ,EXECUTE ,REPLICATION SLAVE ,REPLICATION CLIENT ON * . * TO 'SomeUserName '@ 'localhost' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ; -- then everything is OK as well.

When running as a SomeUserName, and that user has AT LEAST ONE of the above list missing, mysql crashes:

--------------------------------
<..... dump of tables goes here, everything is fine, until...>
mysqldump: mysqldump: Couldn't execute 'show create table `v_distances`': Lost connection to MySQL server during query (2013)
--------------------------------

How to repeat:
0) Create a view in the database `MyDBName`;

1) run the command as root:

> mysqldump --host=127.0.0.1 --quote-names --databases MyDBName 

Ensure that everything is OK.

2) Run the following query: GRANT SELECT ,UPDATE ,DELETE ,CREATE ,DROP ,RELOAD ,SHUTDOWN ,PROCESS,FILE ,REFERENCES ,INDEX ,ALTER ,SHOW DATABASES ,SUPER ,CREATE TEMPORARY TABLES ,LOCK TABLES ,EXECUTE ,REPLICATION SLAVE ,REPLICATION CLIENT ON * . * TO 'SomeUserName '@ 'localhost' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ;

3) Run the command as any user:

> mysqldump --user=SomeUserName --password=SomePassword --host=127.0.0.1 --quote-names --databases MyDBName

Ensure that everything is OK.

4) Run the following query:

REVOKE ALL PRIVILEGES ON * . * FROM 'SomeUserName'@'localhost';
REVOKE GRANT OPTION ON * . * FROM 'SomeUserName'@ 'localhost';
GRANT SELECT ,
LOCK TABLES ON * . *
TO 'SomeUserName '@ 'localhost'
WITH MAX_QUERIES_PER_HOUR 0
MAX_CONNECTIONS_PER_HOUR 0
MAX_UPDATES_PER_HOUR 0 ;

5) Run the command as any user:

> mysqldump --user=SomeUserName --password=SomePassword --host=127.0.0.1 --quote-names --databases MyDBName

Observe the crash.
[8 Nov 2005 1:07] Miguel Solorzano
I was unable to repeat the behavior reported (crash) with current BK source:

mysqldump: mysqldump: Couldn't execute 'show create table `tbv1`': SHOW VIEW command denied to user 'SomeUserName'@'localhost' for table 'tbv1' (1142)

however I got the below log error message and for this reason I am marking
this bug as verified for further analyze:

[New Thread 1132456880 (LWP 8163)]
Error: Freeing unallocated data at line 241, 'sql_base.cc'
[10 Nov 2005 12:12] Martin Tsachev
Got the same on Gentoo Linux.

Recompiled with debug and got:

mysqldump: mysqldump: Couldn't execute 'show create table `t_cron`': SHOW VIEW command denied to user 'backup'@'localhost' for table 't_cron' (1142)

Error: Freeing unallocated data at line 241, 'sql_base.cc'
[10 Nov 2005 12:14] Martin Tsachev
PS. I hanged when it was compiled with -fomit-frame-pointers and without debug, my system's init.d script can't kill mysql in this case and I have to manually kill it.
[12 Nov 2005 17:32] Oleksandr Byelkin
The key thing is LOCK TABLES. Bug can be repeated by the following mysql-test script:
connect (root,localhost,root,,test);
connection root;
--disable_warnings
create database mysqltest;
--enable_warnings

use mysqltest;
CREATE TABLE t1 (i INT);
CREATE VIEW  v1 AS SELECT * FROM t1;
SHOW CREATE VIEW v1;
GRANT SELECT, LOCK TABLES ON *.* TO mysqltest_1@localhost;

connect (user1,localhost,mysqltest_1,,test);
connection user1;

use mysqltest;
LOCK TABLES v1 READ;
-- error 1142
SHOW CREATE TABLE v1;
[12 Nov 2005 22:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32219
[21 Nov 2005 19:11] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32488
[21 Nov 2005 22:01] Oleksandr Byelkin
Thank you for bugreport! Bugfix is pushed to the 5.0.17 version repository.
[2 Dec 2005 3:43] Paul Dubois
Noted in 5.0.17 changelog.
[17 Jul 2006 23:16] Rachel McConnell
Seeing this or a very similar behavior on:

$ mysql -V
mysql  Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i486) using readline 5.1

Test case:
$ mysql -u root

> create database viewtest;
> use viewtest;
> CREATE TABLE `t1` (`i` int(11) default NULL) ENGINE=InnoDB;
> CREATE VIEW `v1` AS select `t1`.`i` AS `i` from `t1`;
> grant select, lock tables on viewtest.* to backup;
> \q

$ mysqldump --opt -u backup viewtest > viewtest.sql
mysqldump: mysqldump: Couldn't execute 'show create table `v1`': SHOW VIEW command denied to user 'backup'@'localhost' for table 'v1' (1142)

Request reopening this bug.
[30 Aug 2006 7:39] Josef Lechner
I probably have found this problem again on MySQL 5.0.24 again, so I returned back to 5.0.22 again and no problem found. It happended on a little complicated system "cascade" of views. I can send structure definitions if needed.

Regards, Josef Lechner, josef.lechner@centrum.cz
[11 May 2008 14:52] Kai Schlachter
Just ran into the same problem with 5.0.51a:

server:~ # mysqldump -V
mysqldump  Ver 10.11 Distrib 5.0.51a, for pc-linux-gnu (i686)
server:~ # mysqldump --all-databases -p > mysql_backup 
Enter password: 
mysqldump: Couldn't execute 'show create table `v_benutzer_rolle`': SHOW VIEW command denied to user 'root'@'localhost' for table 'v_benutzer_rolle' (1142)

Any suggestions? I already checked the permissions. But I can't set more than 'all priviledges' for the root user ...