> -----Original Message-----
> From: Jesper Wisborg Krogh [mailto:jes...@noggin.com.au]
> 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
> > <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
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...
develo...@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.
(develo...@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:
develo...@mypse:~$ mysql -uOMT_Master -pSOMEPASS -hmypse -P3306 agis_core
(omt_mas...@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?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
