MySQL Bugs: #29237: Privilege system inconsistent

Bug #29237	Privilege system inconsistent
Submitted:	20 Jun 2007 13:51	Modified:	25 Jun 2007 9:06
Reporter:	Max Mether	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.1.19, 5.0-BK	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
The privileges for tables have inconsistencies. 

If you try to grant an individual privilege on a table the server will check whether the table exists or not. The same is not true for "ALL PRIVILEGES" nor for  database level privileges. And of course if a table is dropped the privileges remain

How to repeat:

Pre-reqs:
1) No user privs@localhost exists
2) No db sgrmf exists
3) I'm logged in as root

mysql> create user privs@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for privs@localhost;
+-------------------------------------------+
| Grants for privs@localhost                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO 'privs'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> grant select on *.* to privs@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on sgrmf.* to privs@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> grant delete on sgrmf.* to privs@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> grant delete on sgrmf.sgrmf to privs@localhost;
ERROR 1146 (42S02): Table 'sgrmf.sgrmf' doesn't exist
mysql> grant select on sgrmf.sgrmf to privs@localhost;
ERROR 1146 (42S02): Table 'sgrmf.sgrmf' doesn't exist
mysql> grant all on sgrmf.sgrmf to privs@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for privs@localhost;
+----------------------------------------------------------------+
| Grants for privs@localhost                                     |
+----------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'privs'@'localhost'                     |
| GRANT SELECT, DELETE ON `sgrmf`.* TO 'privs'@'localhost'       |
| GRANT ALL PRIVILEGES ON `sgrmf`.`sgrmf` TO 'privs'@'localhost' |
+----------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> revoke all on sgrmf.sgrmf from privs@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> create database sgrmf;
Query OK, 1 row affected (0.01 sec)

mysql> create table sgrmf.sgrmf (a int);
Query OK, 0 rows affected (0.08 sec)

mysql> grant select on sgrmf.sgrmf to privs@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> drop database sgrmf;
Query OK, 1 row affected (0.11 sec)

mysql> grant delete on sgrmf.sgrmf to privs@localhost;
ERROR 1146 (42S02): Table 'sgrmf.sgrmf' doesn't exist
mysql> show grants for privs@localhost;
+----------------------------------------------------------+
| Grants for privs@localhost                               |
+----------------------------------------------------------+
| GRANT SELECT ON *.* TO 'privs'@'localhost'               |
| GRANT SELECT, DELETE ON `sgrmf`.* TO 'privs'@'localhost' |
| GRANT SELECT ON `sgrmf`.`sgrmf` TO 'privs'@'localhost'   |
+----------------------------------------------------------+
3 rows in set (0.00 sec)

Suggested fix:
Stop checking that tables exists when granting individual table privileges.

Thank you for a bug report. What is a bug here is:

1. Inconsistency of GRANT SELECT vs. GRANT ALL PRIVILEGES for individual table.
2. Remaining table-level grants after table dropped.

That is:

mysql> grant select on test.nonexistant to user1;
ERROR 1146 (42S02): Table 'test.nonexistant' doesn't exist
mysql> grant all on test.nonexistant to user1;
Query OK, 0 rows affected (0.09 sec)

mysql> show grants for user1;
+-------------------------------------------------------------+
| Grants for user1@%                                          |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'%'                           |
| GRANT ALL PRIVILEGES ON `test`.`nonexistant` TO 'user1'@'%' |
+-------------------------------------------------------------+
2 rows in set (0.02 sec)

The above is a bug #1, I think, because of inconsistency.

mysql> create table test.nonexistant (c1 int);
Query OK, 0 rows affected (0.14 sec)

mysql> drop table test.nonexistant;
Query OK, 0 rows affected (0.03 sec)

mysql> show grants for user1;
+-------------------------------------------------------------+
| Grants for user1@%                                          |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'%'                           |
| GRANT ALL PRIVILEGES ON `test`.`nonexistant` TO 'user1'@'%' |
+-------------------------------------------------------------+
2 rows in set (0.00 sec)

And the above is a bug #2, because there is no point in table-level privileges being stored when table is already dropped.

I think that 2) has been the case with MySQL since early days (3.23).  I thought it was a MySQL 'feature' that a table could be recreated and table-level privileges would 'resurrect', as the 'tables_privileges' table still has an entry.

I would agree with the comment on 2) not beeing a bug. Also note that 2) is the same for all other levels as well (global, db etc).

I still thin it would be nice if privileges on any objects were dropped when the object itself is dropped.

And I also think that privileges on 'lower level' (ex: table) should be dropped if the same privilege is created on 'higher level (ex: database).

I can imagine different scenarios where a user is given a privilege by mistake that he should not due to this 'non-intelligent' privilege handling in MySQL.

I can think of cases where removing the privileges would not be very good. 

The easiest example would be a corrupt table. The table is corrupt, so it needs to be deleted and restored. While doing this all users would loose their privileges to that table if the privileges were removed while dropping the table. 

So I think that it should be ok to have privileges on non-existing objects. 

IMHO the best case againts this is when you rename objects, be it tables, dbs or whatever. Logicillay the privileges should follow. However I think it's easier to be aware of this deficiency than the above one. 

In all cases 1) is a bug that should be fixed.

Valeriy's second observation about "no point of keeping grants on an object after an object has been dropped" is invalid, at best it should be documented.

Only inconsistency will be addressed in the server.

i accept that it is 'not a bug' that upper-level and lower-level privileges exist simutaneously.

Still I request a utility to 'clean up'

* 'orphaned' privleges (privileges to an object that does not exist any more)

* 'redundant' privileges (privileges existing on a lower level when a identical privlege exist on a upper level)

* 'empty' privileges (for instance if 1) a row in `proc_priv` column of the `mysql`.`procs.priv`table contains an empty string or 2) the `mysql`.`user` table contains a row with "N" for all privileges available).

.. and I think that should be implemented in the server itself!

Is this a new 'feature request' ?