MySQL Bugs: #10406: Grant all command does not give error though table does not exist.

Bug #10406	Grant all command does not give error though table does not exist.
Submitted:	6 May 2005 10:14	Modified:	3 Aug 2005 17:00
Reporter:	Disha	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.11, 5.0.4 Beta	OS:	Windows (Windows 2003)
Assigned to:	Antony Curtis	CPU Architecture:	Any

Description:
If a grant all command is executed on a table that does not exists, no error is displayed. This behavior contradicts to the description provided in the product help section 13.5.1.3. 

The section 13.5.1.3 states as: "MySQL allows you to grant database-level privileges even if the database doesn't exist, to make it easy to prepare for database use. However, MySQL currently does not allow you to grant table-level or column-level privileges if the table doesn't exist."

How to repeat:
1. Start the MySQL client and connect to the database with valid user and password.
2. Set the delimiter to //
3. Create a new database and a new user as follows:
   create database db1//
   create user u1 identified by 'test'//

4. Now execute a grant command as follows:
   grant all privileges on db1.tb1 to u1//
NOTE: tb1 table does not exist.

Expected Results: 
1. Appropriate error should be displayed as the table does not exist.

Actual Results: 
1. No error is displayed and the command is executed without error. This behavior contradicts to the help document section "13.5.1.3".

"ALL PRIVILEGES" is actually a database level grant, trying to grant SELECT on a nonexisting table failes as expected.

IMHO trying to set global or database level privileges on a single table should be rejected.

(verified on 4.1.11/Linux)

GRANT ALL [PRIVILEGES] ON <table> TO <user>
is required by standard SQL.

Ok, i was wrong on that (tricked by the anonymous user again), but still:

GRANT ALL        ON db.nonexistant_tab TO ...  -- this works
GRANT SELECT ON db.nonexistant_tab TO ...  -- this fails as documented

mysql> drop table if exists test.nonexistant_tab;
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> grant select on test.nonexistant_tab to foobar@localhost;
ERROR 1146 (42S02): Table 'test.nonexistant_tab' doesn't exist

mysql> grant all    on test.nonexistant_tab to foobar@localhost;
Query OK, 0 rows affected (0,00 sec)

Behaviour may be intentional feature as ALL privileges includes CREATE privilege which is permitted on an non-existing table.

Have developed patch&test case but it may be unnecessary as the current behaviour does appear to be deliberate.

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

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is not a bug in the server but in the documentation.

MySQL allows you to do a privilege on a not existing table if you use the CREATE privilege.
As the ALL PRIVILEGES include the CREATE privilege, we should not give an error for this

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Updated the SQL Syntax chapter of the Manual. 

Note that it is possible to grant privileges on any database object that doesn't actually exist, so long as the privileges to be granted include the appropriate CREATE privilege (or ALL).

Bug closed.