Bug #88666 I_S FILES : all rows are displayed whatever the user privileges
Submitted: 27 Nov 2017 15:51 Modified: 29 Nov 2017 13:57
Reporter: jocelyn fournier Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:5.7 OS:Any
Assigned to:
Tags: information_schema, privileges

[27 Nov 2017 15:51] jocelyn fournier
Description:
Hi,

The INFORMATION_SCHEMA.FILES content is not restricted whatever the privilege of a given user.
It gives access to information about all DB/Tables stored on the DB server.

How to repeat:
With root : 

mysql -uroot 

DROP DATABASE IF EXISTS test1;
DROP DATABASE IF EXISTS test2;
CREATE DATABASE test1;
use test1;
CREATE TABLE test1 (t int);
CREATE DATABASE test2;
use test2;
CREATE TABLE test2 (t int);
GRANT SELECT ON test1.* TO 'test1'@'localhost' IDENTIFIED BY 'test';

With test1 user : 

mysql -utest1 -ptest
SHOW DATABASES
*************************** 1. row ***************************
Database: information_schema
*************************** 2. row ***************************
Database: test1
2 rows in set (0.00 sec)

SELECT * FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%test2%'\G
*************************** 1. row ***************************
             FILE_ID: 27
           FILE_NAME: ./test2/test2.ibd
           FILE_TYPE: TABLESPACE
     TABLESPACE_NAME: innodb_file_per_table_27
       TABLE_CATALOG: 
        TABLE_SCHEMA: NULL
          TABLE_NAME: NULL
  LOGFILE_GROUP_NAME: NULL
LOGFILE_GROUP_NUMBER: NULL
              ENGINE: InnoDB
       FULLTEXT_KEYS: NULL
        DELETED_ROWS: NULL
        UPDATE_COUNT: NULL
        FREE_EXTENTS: 0
       TOTAL_EXTENTS: 0
         EXTENT_SIZE: 1048576
        INITIAL_SIZE: 65536
        MAXIMUM_SIZE: NULL
     AUTOEXTEND_SIZE: 1048576
       CREATION_TIME: NULL
    LAST_UPDATE_TIME: NULL
    LAST_ACCESS_TIME: NULL
        RECOVER_TIME: NULL
 TRANSACTION_COUNTER: NULL
             VERSION: NULL
          ROW_FORMAT: NULL
          TABLE_ROWS: NULL
      AVG_ROW_LENGTH: NULL
         DATA_LENGTH: NULL
     MAX_DATA_LENGTH: NULL
        INDEX_LENGTH: NULL
           DATA_FREE: 0
         CREATE_TIME: NULL
         UPDATE_TIME: NULL
          CHECK_TIME: NULL
            CHECKSUM: NULL
              STATUS: NORMAL
               EXTRA: NULL
1 row in set (0.00 sec)

SELECT COUNT(*) FROM INFORMATION_SCHEMA.FILES\G
*************************** 1. row ***************************
COUNT(*): 25
1 row in set (0.00 sec)

Moreover, because of this bug mysqldump performance for a specific user / db can be really poor on server with a lot of tables, because it uses the query
SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('db_name'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME

and the I_S.FILES returns all the tables of the server.

Thanks !
  Jocelyn Fournier

Suggested fix:
Only returns the infos from tables a user has access.
[27 Nov 2017 15:52] jocelyn fournier
updating tags
[29 Nov 2017 8:44] jocelyn fournier
changing the category
[29 Nov 2017 13:42] Sinisa Milivojevic
Hi Jocelyn,

I completely understand your report and I would like to thank you for it. However, this behaviour is by design. Design is probably not ideal, but this does not make this report a bug. However, this would make a perfect candidate for the feature request.

A feature would extend privileges to the output provided from I_S tables. Do you agree ?????
[29 Nov 2017 13:50] jocelyn fournier
Hi Sinisa!

Well I would expect the I_S FILES to only dump infos regarding tablespaces accessible by the user, and not all the tablespaces. I mean the I_S TABLES for example restricts its output to only tables accessible by the user, so why do we have a different behaviour for I_S FILES? :)

  Jocelyn
[29 Nov 2017 13:57] Sinisa Milivojevic
Jocelyn, mon ami,

Tu a raison !!!!