List:General Discussion« Previous MessageNext Message »
From:Jesper Wisborg Krogh Date:October 15 2010 9:32pm
Subject:Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!
View as plain text  
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  
> <johnny@stripped>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
Thread
How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!Daevid Vincent15 Oct
  • Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!Johnny Withers15 Oct
    • Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!Suresh Kuna15 Oct
      • Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!Jesper Wisborg Krogh15 Oct
        • RE: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!Daevid Vincent15 Oct
          • Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!Jesper Wisborg Krogh16 Oct
            • RE: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!Daevid Vincent16 Oct