MySQL Bugs: #27629: Possible security flaw in INFORMATION

Bug #27629	Possible security flaw in INFORMATION_SCHEMA and SHOW statements
Submitted:	3 Apr 2007 22:46	Modified:	13 Oct 2010 15:02
Reporter:	Alexander Nozdrin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.0.40, 5.1, 5.2	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
According to The Manual:
(http://dev.mysql.com/doc/refman/5.0/en/information-schema.html)
"Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA.ROUTINES table), users who have insufficient privileges will see NULL."

The problem is that under some circumstances, INFORMATION_SCHEMA and
SHOW statements show the information, that the user is not permitted
to see. I've managed to find this problem with triggers and columns.

How to repeat:
mysql> create database db1;
mysql> create table db1.t1(a int, b int, c int);
mysql> create trigger db1.t1_ai after insert on db1.t1
         for each row
           set @a = new.a + new.b + new.c;
mysql> grant select(b) on db1.t1 to u1@localhost;
mysql> create table db1.t2(s char(10));

-- Login as u1@localhost; current database: db1

mysql> show triggers\G
*************************** 1. row ***************************
  Trigger: t1_ai
    Event: INSERT
    Table: t1
Statement: set @a = new.a + new.b + new.c
   Timing: AFTER
  Created: NULL
 sql_mode:
  Definer: root@localhost
1 row in set (0.01 sec)

mysql> select * from information_schema.triggers\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: NULL
            TRIGGER_SCHEMA: db1
              TRIGGER_NAME: t1_ai
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: NULL
       EVENT_OBJECT_SCHEMA: db1
        EVENT_OBJECT_TABLE: t1
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: set @a = new.a + new.b + new.c
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: NULL
                  SQL_MODE:
                   DEFINER: root@localhost
1 row in set (0.00 sec)

So, here the user sees the columns in the trigger definition,
which are not allowed to him/her in normal circumstances.

mysql> show columns from db1.t1\G
*************************** 1. row ***************************
  Field: a
   Type: int(11)
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 2. row ***************************
  Field: b
   Type: int(11)
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 3. row ***************************
  Field: c
   Type: int(11)
   Null: YES
    Key:
Default: NULL
  Extra:
3 rows in set (0.00 sec)

mysql> select * from information_schema.columns where table_name='t1'\G
*************************** 1. row ***************************
           TABLE_CATALOG: NULL
            TABLE_SCHEMA: db1
              TABLE_NAME: t1
             COLUMN_NAME: b
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 10
           NUMERIC_SCALE: 0
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: int(11)
              COLUMN_KEY:
                   EXTRA:
              PRIVILEGES: select
          COLUMN_COMMENT:
1 row in set (0.00 sec)

mysql> select * from information_schema.columns where table_schema='db1'\G
*************************** 1. row ***************************
           TABLE_CATALOG: NULL
            TABLE_SCHEMA: db1
              TABLE_NAME: t1
             COLUMN_NAME: b
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 10
           NUMERIC_SCALE: 0
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: int(11)
              COLUMN_KEY:
                   EXTRA:
              PRIVILEGES: select
          COLUMN_COMMENT:
1 row in set (0.01 sec)

So, here the bug is in SHOW COLUMNS statement only -- it shows columns
'a' and 'b', which are not exposed to the user. INFORMATION_SCHEMA works
properly though.

Thank you for the report.

Verified as described. All versions are affected.

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/commits/25662

ChangeSet@1.2457, 2007-04-28 14:51:49+05:00, gluh@mysql.com +3 -0
  Bug#27629 Possible security flaw in INFORMATION_SCHEMA and SHOW statements
  added SUPER_ACL check for I_S.TRIGGERS

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/commits/25668

ChangeSet@1.2490, 2007-04-28 16:47:37+05:00, gluh@mysql.com +2 -0
  Bug#27629 Possible security flaw in INFORMATION_SCHEMA and SHOW statements(addon for 5.1)
  added TRIGGER_ACL check for I_S.TRIGGERS

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/commits/32726

ChangeSet@1.2497, 2007-08-20 11:23:08+05:00, gluh@mysql.com +4 -0
  Bug#27629 Possible security flaw in INFORMATION_SCHEMA and SHOW statements
  added SUPER_ACL check for I_S.TRIGGERS

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/commits/32809

ChangeSet@1.2573, 2007-08-21 17:55:49+05:00, gluh@mysql.com +2 -0
  Bug#27629 Possible security flaw in INFORMATION_SCHEMA and SHOW statements(addon for 5.1)
  added TRIGGER_ACL check for I_S.TRIGGERS

Pushed into 5.1.22-beta

Pushed into 5.0.48

Noted in 5.0.48, 5.1.22 changelogs.

Some SHOW statements and INFORMATION_SCHEMA queries could expose
information not allowed by the user's access privileges.

This was pushed to 5.1.23, not 5.1.22.

There is still a small issue here. Assume a user with (for instance) a SUPER privilege on *.*, but no TRIGGER privilege:

---------
mysql> SHOW GRANTS;
+-----------------------------------+
| Grants for user4@%                |
+-----------------------------------+
| GRANT SUPER ON *.* TO 'user4'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLES IN db1;
Empty set (0.00 sec)

mysql> SHOW TABLE STATUS IN db1 LIKE 't2';
Empty set (0.00 sec)

mysql> USE db1;
ERROR 1044 (42000): Access denied for user 'user4'@'%' to database 'db1'
---------...

This user cannot do anything in regards viewing tables using SHOW. However, an I_S query for the same does work at this time:

...---------
mysql> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t2'\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: db1
     TABLE_NAME: t2
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 0
MAX_DATA_LENGTH: 3096224743817215
   INDEX_LENGTH: 1024
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2010-03-23 15:18:27
    UPDATE_TIME: 2010-03-23 15:18:27
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.00 sec)
---------

Testing the same with only the USAGE privilege, there is no output:

---------
mysql> SHOW GRANTS;
+-----------------------------------+
| Grants for user5@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'user5'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t2'\G
Empty set (0.00 sec)

mysql> SHOW TABLE STATUS IN db1 LIKE 't2';
ERROR 1044 (42000): Access denied for user 'user5'@'%' to database 'db1'
---------

And, looking at the first example again, when this user would have TRIGGER, but no other privileges, the user can still view the overall data of tables (even those that do not relate to the trigger at all):

---------
mysql> SHOW GRANTS;
+-------------------------------------+
| Grants for user4@%                  |
+-------------------------------------+
| GRANT TRIGGER ON *.* TO 'user4'@'%' |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 't2'\G
*************************** 1. row ***************************
           Name: t2
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 3096224743817215
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2010-03-23 15:18:27
    Update_time: 2010-03-23 15:18:27
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t2'\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: db1
     TABLE_NAME: t2
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 0
MAX_DATA_LENGTH: 3096224743817215
   INDEX_LENGTH: 1024
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2010-03-23 15:18:27
    UPDATE_TIME: 2010-03-23 15:18:27
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.00 sec)

mysql> SHOW TRIGGERS;
+---------+--------+-------+--------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| Trigger | Event  | Table | Statement                      | Timing | Created | sql_mode | Definer        | character_set_client | collation_connection | Database Collation |
+---------+--------+-------+--------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| t1_ai   | INSERT | t1    | set @a = new.a + new.b + new.c | AFTER  | NULL    |          | root@127.0.0.1 | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
+---------+--------+-------+--------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)
---------

So, there are several inconsistencies:

#1 Querying for table information works, when a user has the TRIGGER privilege while it should not.
#2 Having the SUPER privilege allows querying of I_S, but not obtain the same from SHOW.

Roel, 
please report a separate bug.
Please never reopen a closed bug to complain about a related issue, file a new bug and corsslink the two.