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:
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

[6 May 2005 10:14] Disha
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 

The section 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 "".
[6 May 2005 11:48] Hartmut Holzgraefe
"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)
[6 May 2005 12:46] Trudy Pelzer
is required by standard SQL.
[6 May 2005 13:27] Hartmut Holzgraefe
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)
[7 Jun 2005 4:56] Antony Curtis
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.
[7 Jun 2005 4:59] Bugs System
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:

[2 Aug 2005 16:26] Michael Widenius
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
[3 Aug 2005 17:00] Jon Stephens
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

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.