MySQL Bugs: #51380: granting privileges to nonexisting database objects

Bug #51380	granting privileges to nonexisting database objects
Submitted:	22 Feb 2010 10:08	Modified:	24 Feb 2010 16:29
Reporter:	Axel Schwenke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:	5.1 (probably all)	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
The documentation on when MySQL tolerates GRANT statements for nonexisting database objects does not match the server behavior.

Note: the server behaves quite erratically here. IMHO :)

Quote from http://dev.mysql.com/doc/refman/5.1/en/grant.html:

"MySQL enables you to grant privileges even on database objects that do not exist. In such cases, the privileges to be granted must include the CREATE  privilege. This behavior is by design, and is intended to enable the database administrator to prepare user accounts and privileges for database objects that are to be created at a later time."

The second sentence is just wrong.

How to repeat:
mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0,01 sec)

mysql>grant SELECT, INSERT, UPDATE, DELETE ON foo.bar to axel;
ERROR 1146 (42S02): Table 'foo.bar' doesn't exist

mysql>grant SELECT, INSERT, UPDATE, DELETE ON foo.* to axel;
Query OK, 0 rows affected (0,00 sec)

Note: the GRANT is accepted for nonexisting database `foo` even without including the CREATE privilege.

Suggested fix:
Make the documentation match the actual behavior of the server!

Axel, have you verified that this isn't a server bug?

Paul,

it's hard to judge if some behavior is a bug when I don't know what's the intended behavior ;) 

If we assume that a database (= schema) is not considered a database object, then the documentation matches the server behavior. In that case the documentation then lacks a hint in that direction: "we don't check the existence of the target database when granting privileges on database level".

Even if "database object" is the wrong term, I prefer to see consistent behavior for all things that privileges can be granted on. It looks like the current server behavior *is* inconsistent in that respect. Be it. But the documentation should reflect that.

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 products.

Revised description:

MySQL enables you to grant privileges on databases or tables that do
not exist. For tables, the privileges to be granted must include the
CREATE privilege. This behavior is by design, and is intended to
enable the database administrator to prepare user accounts and
privileges for databases or tables that are to be created at a later
time.