Hi
On 16/10/2010, at 1:47 AM, Suresh Kuna wrote:
Hey Daevid,
As this time zone table won't change once it is set up. Do a copy
of the
table data into another database and give grants to it.
Copy the data is not a good solution. First of all, time zone data
does change. Secondly if you need to use functions such as CONVERT_TZ
() I believe you need access to the time zone tables in the mysql
database.
On Fri, Oct 15, 2010 at 7:57 PM, Johnny Withers
<joh...@pixelated.net>wrote:
I think this is one of those times you would update the mysql.user
table
directly, then flush privileges.
You can grant access to the time zone tables just as you would do to
any other table.
GRANT SELECT ON `mysql`.`time_zone_name` TO '%';
GRANT SELECT ON `mysql`.`time_zone_name` TO '%'@'%';
GRANT SELECT ON `mysql`.`time_zone_name` TO ''@'%';
GRANT SELECT ON `mysql`.`time_zone_name` TO '';
GRANT SELECT ON `mysql`.`time_zone_name`; (this one fails)
As mentioned above, granting access to the time zone tables works
exactly as it does for all other tables, so. e.g. granting SELECT to
'%' will not allow everybody to do a SELECT on the table, but rather
allow users logging in as the '%'@'%' user to select from the
mysql.time_zone_name table. If the users used in the above GRANT
statements don't exist, they will also end up being created. This
means that you suddenly might have opened access to the database for
a user called '%' from everywhere (although they only can select from
the time_zone_name table). Note that the new user can login without
using a password.
(none)> SELECT User, Host FROM mysql.user;
+----------+-----------+
| User | Host |
+----------+-----------+
| root | localhost |
| testuser | localhost |
+----------+-----------+
2 rows in set (0.37 sec)
(none)> GRANT SELECT ON `mysql`.`time_zone_name` TO '%';
Query OK, 0 rows affected (0.18 sec)
(none)> SELECT User, Host FROM mysql.user;
+----------+-----------+
| User | Host |
+----------+-----------+
| % | % |
| root | localhost |
| testuser | localhost |
+----------+-----------+
3 rows in set (0.00 sec)
$ mysql -u % -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
...
Jesper