MySQL Bugs: #6116: SET time_zone := ... requires access to mysql.time

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

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.

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

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

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

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