List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:October 15 2010 9:50pm
Subject:RE: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!
View as plain text  
> -----Original Message-----
> From: Jesper Wisborg Krogh [mailto:jesper@stripped] 
> Sent: Friday, October 15, 2010 2:33 PM
> To: MY SQL Mailing list
> Subject: Re: How do I GRANT SELECT to mysql.time_zone_name 
> for ANYONE?!
> 
> 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


Thanks for the reply Jesper, but either there isn't a solution in your
response, or I'm missing it?

Any user can get into mysql, it's what they can do after that's the
interesting part.

I used your GRANT example above and get this...

developer@mypse:~$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2275
Server version: 5.0.51a-3ubuntu5.7 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
(developer@localhost) [(none)]> SELECT * FROM mysql.time_zone_name;
+----------------------------------------+--------------+
| Name                                   | Time_zone_id |
+----------------------------------------+--------------+
| Africa/Abidjan                         |            1 |
| Africa/Accra                           |            2 |
| Africa/Addis_Ababa                     |            3 |
| Africa/Algiers                         |            4 |
...

But then when I try an existing user that I use for all my PHP/DB
connections:

developer@mypse:~$ mysql -uOMT_Master -pSOMEPASS -hmypse -P3306 agis_core
(OMT_Master@mypse) [agis_core]> SELECT * FROM mysql.time_zone_name;
ERROR 1142 (42000): SELECT command denied to user
'OMT_Master'@'mydomain.com' for table 'time_zone_name'

Here's the current user's I have in my VM so far:

SELECT `User`, `Host` FROM mysql.user;

User              Host      
----------------  ----------
                  %         
%                 %         
OMT_Master        %         
OMT_Web           %         
View_ReadOnly     %         
developer         %         
diagnostics       %         
diagnostics       10.10.10.%
root              127.0.0.1 
                  localhost 
debian-sys-maint  localhost 
root              localhost 

At this point of frustration, unless someone has a way to do this -- which
seems like it should be a pretty straight forward thing to do -- I'll just
add this particular OMT_Master user to have this particular table's SELECT
GRANT. Or am I missing something?

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