MySQL Bugs: #25863: No database selected error, but documentation says * for global allowed

Bug #25863	No database selected error, but documentation says * for global allowed
Submitted:	26 Jan 2007 10:45	Modified:	12 Nov 2009 18:39
Reporter:	Stephen Gornick	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0.27	OS:	Windows (Windows XP Pro SP2)
Assigned to:	Jon Olav Hauglid	CPU Architecture:	Any
Tags:	error, global, grant, No database selected

Description:
If I connect without specifying a default schema, and then attempt to grant permissions globally, I am given an error message "No database selected".
However this is allowed in the Linux version of query-browser.  Additionally, the documentation specifically says:

"If you specify ON * and you have not selected a default database, the privileges granted are global."

http://dev.mysql.com/doc/refman/5.0/en/grant.html

How to repeat:
Launch query browser.
In the Query Browser connect dialog box, enter:
  Serer Host: localhost
  User: root
  Password: [myrootpwd]
  Default Schema: [leave blank]
Click OK button.

In the query entry field, 
  GRANT ALL ON * TO STEVE;
Click Execute button.

Status bar will show: 
  "The query could not be executed."
The Messages window at bottom will show under Description column:
  "No database selected"

Suggested fix:
Allow global grants even when no schema is selected, just like the Linux version of the query browser does.

Thank you for the bug report. This is server or documentation issue,
the same happens with the mysql.exe client. Changing category.

http://dev.mysql.com/doc/refman/5.0/en/grant.html

"You can assign global privileges by using ON *.* syntax or database-level privileges by using ON db_name.*  syntax. If you specify ON * and you have selected a default database, the privileges are granted in that database. (Warning: If you specify ON * and you have not selected a default database, the privileges granted are global.)"

C:\mydb>bin\mysql -uroot -P3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.27-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create user STEVE;
Query OK, 0 rows affected (0.13 sec)

mysql> GRANT ALL ON * TO STEVE;
ERROR 1046 (3D000): No database selected
mysql> GRANT ALL ON *.* TO STEVE;
Query OK, 0 rows affected (0.00 sec)

mysql>

Singe Category changed to MySQL Server, changed bug report's Version field to reflect MySQL Server version that this was verified against (5.0.27-community-nt).

Another 'No database selected' regression.

I have the same problem but it appears sometimes. Great part of access with the web site tu the db MySQL works correctly. But sometimes appears this error message. It needs to refresh page or turn back and access again to the same page and, then, it works. May the my.ini configuration have some problem?
The system works on server with Windows 2000. DB is no so big and the max access number is about 100. Thanks

Please re-triage. This is a regression!

This bug was introduced in the fix for Bug#19044 in 5.0.23 and 5.1.12.

After an investigation, it turned out that this introduced "regression" to the documented behavior happens to fix a previously reported Bug#6774 "Replication fails with Wrong usage of DB GRANT and GLOBAL PRIVILEGES".

After a discussion with Sergey Golubchik we concluded that the old behavior,
when '*' had different semantics depending on the current database selection was rather a gotcha than a feature.

Let us document that starting from 5.0.23 '*' in GRANT statement
means 'all objects of the current database', and for it to work, one needs
to have a non-empty current database selected, whereas '*.*' means
a global grant. This change in behavior was introduced as part of the fix for  Bug#19022.

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.

Hello Jon Olav,
could you please add a test case for that one to the test suite?
Thank you,
-kostja

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/commits/75823

2797 Jon Olav Hauglid	2009-06-08
      Bug #25863 No database selected error, but documentation 
                 says * for global allowed
      
      The current behaviour of 'GRANT *' was changed as a part of the fix
      for Bug#19022, Bug#17199 and Bug#18444. To avoid regression, we keep
      the current behavior and update the documentation. 
      Test case added to grant.test.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090617073019-azsawauatv99124t) (version source revid:jon.hauglid@sun.com-20090608082432-o27d1guc3g6qmk5r) (merge vers: 5.4.4-alpha) (pib:11)

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/commits/86323

2891 Jon Olav Hauglid	2009-10-09
      Bug #25863 No database selected error, but documentation 
                 says * for global allowed
      
      The current behaviour of 'GRANT *' was changed as a part of the fix
      for Bug#19022, Bug#17199 and Bug#18444. To avoid regression, we keep
      the current behavior and update the documentation. 
      Test case added to grant.test.

Pushed to mysql-next-mr-runtime (version 5.5)

Test case changes. No changelog entry needed.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (version source revid:alik@ibmvm-20091009114750-utkhs9029vewkluf) (merge vers: 6.0.14-alpha) (pib:13)

Test case changes. No changelog entry needed.

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091103113702-p61dlwc6ml6fxg18) (merge vers: 5.5.0-beta) (pib:13)

Test case changes. No changelog entry needed.