Bug #6116 SET time_zone := ... requires access to mysql.time_zone* tables
Submitted: 15 Oct 2004 15:28 Modified: 11 Nov 2004 11:55
Reporter: Carsten Pedersen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.x OS:Any (Any)
Assigned to: Dmitry Lenev CPU Architecture:Any

[15 Oct 2004 15:28] Carsten Pedersen
Description:
Doing "SET time_zone  := ... " requires access to mysql.time_zone* tables even when they are not utilized, i.e. when using the "+hh:mm" format rather than e.g. "Europe/Amsterdam".

Many admins will not relish having to give *any* access to the mysql database, so avoiding this while at the same time allowing the user to set the local time zones is desirable.

How to repeat:
For a non-privileged connection (one that does not have access to the mysql database):

mysql> set time_zone := '+00:00';
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'

Suggested fix:
Ignore the mysql.* tables for requests that do not require them in order to set the discern the time zone, i.e. having the "+hh:mm" format.

Alternatively, use those same tables at a privilege levels higher than that of the connection.
[21 Oct 2004 18:19] Dmitry Lenev
ChangeSet
  1.2107 04/10/21 22:18:00 dlenev@brandersnatch.localdomain +5 -0
  Fix for bug #6116 "SET time_zone := ... requires access to
  mysql.time_zone* tables".
[22 Oct 2004 19:14] Dmitry Lenev
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html
[10 Nov 2004 17:38] Reid Madsen
When do you expect to release the next version of 4.1.x that incorporates this fix?

Further, am I to understand from the fix that the CONVERT_TZ(time, from_tz, to_tz) function will be usable by non-priviledged connections?

Reid Madsen
[11 Nov 2004 11:55] Dmitry Lenev
Hi, Reid!

Fix was incorporated into release 4.1.7 which is availiable since end of October.

And yes you are right, this fix allows usage of CONVERT_TZ() function and setting of per-connection @@time_zone variable to unpriviliged user.

Thank you for your interest in MySQL (and particularly this feature :) ) !