Wildcard grants are not cleaned up correctly

[dveeden]

Bug Report

• Create user u1 and grant select access to t%.*.
• Result: One record in mysql.db with Select_priv set to Y.
• Revoke select grant on t%.*.
• Result: Record in mysql.db with all permission fields set to N.
• Grant select access to tes%.* (anything that's not the exact same prefix)
• Result: Two records in mysql.db, one with Select_priv set to Y the other with all set to N.
• Using SHOW GRANTS FOR... to inspect the grants
• Re-creating the user based on the SHOW GRANTS FOR... output.
• Now there is only one record in mysql.db with the Select_priv field set to Y.

The result is that:

• The left-over record in mysql.db with all fields set to N blocks access to tables.
• This is not visible in the SHOW GRANTS FOR... output.
• This changes access after re-creating a user based on SHOW GRANTS FOR... output.

sql> select * from mysql.db where user='u1' and host='%'\G
Empty set (0.0037 sec)

sql> CREATE USER 'u1'@'%' IDENTIFIED BY 'u1';
Query OK, 0 rows affected (0.0528 sec)

sql> select * from mysql.db where user='u1' and host='%'\G
Empty set (0.0040 sec)

sql> GRANT SELECT ON `t%`.* TO 'u1'@'%';
Query OK, 0 rows affected (0.0330 sec)

sql> select * from mysql.db where user='u1' and host='%'\G
*************************** 1. row ***************************
                 Host: %
                   DB: t%
                 User: u1
          Select_priv: Y
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
1 row in set (0.0061 sec)

sql> REVOKE SELECT ON `t%`.* FROM 'u1'@'%';
Query OK, 0 rows affected (0.0362 sec)

sql> select * from mysql.db where user='u1' and host='%'\G
*************************** 1. row ***************************
                 Host: %
                   DB: t%
                 User: u1
          Select_priv: N
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
1 row in set (0.0059 sec)

sql> GRANT SELECT ON `tes%`.* TO 'u1'@'%';
Query OK, 0 rows affected (0.0364 sec)

sql> select * from mysql.db where user='u1' and host='%'\G
*************************** 1. row ***************************
                 Host: %
                   DB: t%
                 User: u1
          Select_priv: N
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
*************************** 2. row ***************************
                 Host: %
                   DB: tes%
                 User: u1
          Select_priv: Y
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
2 rows in set (0.0050 sec)

sql> show grants for 'u1'@'%';
+------------------------------------+
| Grants for u1@%                    |
+------------------------------------+
| GRANT USAGE ON *.* TO 'u1'@'%'     |
| GRANT SELECT ON tes%.* TO 'u1'@'%' |
+------------------------------------+
2 rows in set (0.0009 sec)

sql> drop user 'u1'@'%';
Query OK, 0 rows affected (0.0514 sec)

sql> CREATE USER 'u1'@'%' IDENTIFIED BY 'u1';
Query OK, 0 rows affected (0.0367 sec)

sql> GRANT SELECT ON `tes%`.* TO 'u1'@'%';
Query OK, 0 rows affected (0.0283 sec)

sql> select * from mysql.db where user='u1' and host='%'\G
*************************** 1. row ***************************
                 Host: %
                   DB: tes%
                 User: u1
          Select_priv: Y
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
1 row in set (0.0053 sec)

This happens with v6.1.1 and v6.3.0

[ti-chi-bot]

@dveeden: The label(s) severity/severe cannot be applied, because the repository doesn't have them.

In response to this:

/severity severe

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[dveeden]

/severity major
/component privilege

[dveeden]

/label affects-6.1
/label affects-6.3
/remove-label may-affects-6.1
/remove-label may-affects-6.3

[ti-chi-bot]

@dveeden: The label(s) severity/severe cannot be applied, because the repository doesn't have them.

In response to this:

/severity severe

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[dveeden]

To compare this with MySQL 8.0.30:

mysql> select * from mysql.db where user='u1' and host='%'\G
Empty set (0.00 sec)

mysql> CREATE USER 'u1'@'%' IDENTIFIED BY 'u1';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from mysql.db where user='u1' and host='%'\G
Empty set (0.00 sec)

mysql> GRANT SELECT ON `t%`.* TO 'u1'@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> select * from mysql.db where user='u1' and host='%'\G
*************************** 1. row ***************************
                 Host: %
                   Db: t%
                 User: u1
          Select_priv: Y
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
1 row in set (0.00 sec)

mysql> REVOKE SELECT ON `t%`.* FROM 'u1'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from mysql.db where user='u1' and host='%'\G
Empty set (0.00 sec)

mysql> GRANT SELECT ON `tes%`.* TO 'u1'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from mysql.db where user='u1' and host='%'\G
*************************** 1. row ***************************
                 Host: %
                   Db: tes%
                 User: u1
          Select_priv: Y
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
1 row in set (0.00 sec)

mysql> show grants for 'u1'@'%';
+--------------------------------------+
| Grants for u1@%                      |
+--------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`%`       |
| GRANT SELECT ON `tes%`.* TO `u1`@`%` |
+--------------------------------------+
2 rows in set (0.00 sec)

[ti-chi-bot]

@dveeden: These labels are not set on the issue: may-affects-4.0, may-affects-5.0, may-affects-5.1.

In response to this:

/label affects-4.0
/label affects-5.0
/label affects-5.1
/label affects 5.2
/label affects 5.3
/label affects 5.4
/label affects 6.0
/label affects 6.2
/remove-label may-affects-4.0
/remove-label may-affects-5.0
/remove-label may-affects-5.1
/remove-label may-affects 5.2
/remove-label may-affects 5.3
/remove-label may-affects 5.4
/remove-label may-affects 6.0
/remove-label may-affects 6.2

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[dveeden]

/label affects-5.2

[dveeden]

/label affects-5.3
/label affects-5.4
/label affects-6.0
/label affects-6.2