MySQL Bugs: #13202: SELECT * INTO OUTFILE ... FROM information

Bug #13202	SELECT * INTO OUTFILE ... FROM information_schema.schemata now fails
Submitted:	14 Sep 2005 22:44	Modified:	18 Oct 2005 11:52
Reporter:	Carsten Segieth
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	5.0.13	OS:	Linux (Linux)
Assigned to:	Sergey Gluhov	Target Version:

Description:
Using a fresh compile from today lets 

SELECT *
INTO OUTFILE '../tmp/out.innodb.file'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY 'n'
  FROM information_schema.schemata LIMIT 0, 5;

fail with

ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'

With a compile from last weekend or earlier this was successfull.

How to repeat:
Use new compiled version (at least containing "ChangeSet@1.1954, 2005-09-14
14:54:48+04:00, petr@mysql.com", which was the newest ChS on my machine) and start

SELECT *
INTO OUTFILE '../tmp/out.innodb.file'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY 'n'
  FROM information_schema.schemata LIMIT 0, 5;

with the default connection. You may also start the attached test file and check against
the result file. They have been created using 5.0.12-beta on Win32.

Suggested fix:
Return to old behaviour.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot -pmyuser
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.13-beta-valgrind-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT *
    -> INTO OUTFILE '../home/miguel/out.innodb.file'
    ->   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    ->   LINES TERMINATED BY 'n'
    ->   FROM information_schema.schemata LIMIT 0, 5;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database
'information_schema'
mysql> select * from information_schema.schemata;
+--------------+--------------------+----------------------------+------------------------
+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME
| SQL_PATH |
+--------------+--------------------+----------------------------+------------------------
+----------+
| NULL         | information_schema | utf8                       | utf8_general_ci       
| NULL     |
| NULL         | dbg                | latin1                     | latin1_swedish_ci     
| NULL     |
| NULL         | dbtest1            | latin1                     | latin1_swedish_ci     
| NULL     |
| NULL         | feed_engine        | latin1                     | latin1_swedish_ci     
| NULL     |
| NULL         | gh                 | latin1                     | latin1_swedish_ci     
| NULL     |
| NULL         | jkl                | latin1                     | latin1_swedish_ci     
| NULL     |
| NULL         | ju                 | latin1                     | latin1_swedish_ci     
| NULL     |
| NULL         | kl                 | latin1                     | latin1_swedish_ci     
| NULL     |
| NULL         | kr                 | latin1                     | latin1_swedish_ci     
| NULL     |
| NULL         | mysql              | latin1                     | latin1_swedish_ci     
| NULL     |
| NULL         | new_crash          | latin1                     | latin1_swedish_ci     
| NULL     |
| NULL         | scott              | latin1                     | latin1_swedish_ci     
| NULL     |
| NULL         | test               | latin1                     | latin1_swedish_ci     
| NULL     |
| NULL         | ty                 | latin1                     | latin1_swedish_ci     
| NULL     |
| NULL         | xxx                | latin1                     | latin1_swedish_ci     
| NULL     |
+--------------+--------------------+----------------------------+------------------------
+----------+
15 rows in set (0.01 sec)

mysql> show grants for 'root'@'localhost';
+-----------------------------------------------------------------------------------------
-----------------------------------------------+
| Grants for root@localhost                                                               
                                              |
+-----------------------------------------------------------------------------------------
-----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD
'*CBA73BBE5D9AF59311C3F4D7E8C20AA847F7B188' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------
-----------------------------------------------+
1 row in set (0.00 sec)

mysql> 

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.12-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT *
    -> INTO OUTFILE 'c:/temp/out.innodb.file'
    ->   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    ->   LINES TERMINATED BY 'n'
    ->   FROM information_schema.schemata LIMIT 0, 5;
Query OK, 3 rows affected (0.59 sec)

Caused by ChangeSet 1.1943.3.1. SELECT_ACL|FILE_ACL [lex->exchange!=0] passed to
check_table_access() but the function fails in:
    if (tables->schema_table && 
        (want_access & ~(SELECT_ACL | EXTRA_ACL)))
    {
      if (!no_errors)
        my_error(ER_DBACCESS_DENIED_ERROR, MYF(0),
                 thd->priv_user, thd->priv_host,
                 information_schema_name.str);
      return TRUE;
    }

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/30037

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/30677

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/30807

Fixed in 5.0.15-rc

Added to changelog 5.0.15.