MySQL Bugs: #14726: Crash when mysqldump encounters a view while privileges limited.

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	CPU Architecture:	Any

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.

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'

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'

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.

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;

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

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

Thank you for bugreport! Bugfix is pushed to the 5.0.17 version repository.

Noted in 5.0.17 changelog.

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.

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

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 ...