How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!

I'm pulling my hair out. How do I GRANT the SELECT ability to ANY USER for
the very specific mysql.time_zone_name table?? I don't want to GRANT it to
every individual user manually, I want one single GRANT that encompasses
every user simultaneously.

I've tried all of these, and they all are valid in mySQL but none of them
actually have the desired result.

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)

Here are the results:

SELECT * FROM mysql.time_zone_name LIMIT 0, 5000

Error Code : 1142
SELECT command denied to user 'daevid'@'mycompany.com' for table
'time_zone_name'


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!

--001485f03dbc9cc2dd0492a89ed4
Content-Type: text/plain; charset=ISO-8859-1

I think this is one of those times you would update the mysql.user table
directly, then flush privileges.

JW


On Thu, Oct 14, 2010 at 6:46 PM, Daevid Vincent wrote:

> I'm pulling my hair out. How do I GRANT the SELECT ability to ANY USER for
> the very specific mysql.time_zone_name table?? I don't want to GRANT it to
> every individual user manually, I want one single GRANT that encompasses
> every user simultaneously.
>
> I've tried all of these, and they all are valid in mySQL but none of them
> actually have the desired result.
>
> 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)
>
> Here are the results:
>
> SELECT * FROM mysql.time_zone_name LIMIT 0, 5000
>
> Error Code : 1142
> SELECT command denied to user 'daevid'@'mycompany.com' for table
> 'time_zone_name'
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--001485f03dbc9cc2dd0492a89ed4--

Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!

--001636426f6fba351d0492a8e7fd
Content-Type: text/plain; charset=ISO-8859-1

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.

On Fri, Oct 15, 2010 at 7:57 PM, Johnny Withers wrote:

> I think this is one of those times you would update the mysql.user table
> directly, then flush privileges.
>
> JW
>
>
> On Thu, Oct 14, 2010 at 6:46 PM, Daevid Vincent wrote:
>
> > I'm pulling my hair out. How do I GRANT the SELECT ability to ANY USER
> for
> > the very specific mysql.time_zone_name table?? I don't want to GRANT it
> to
> > every individual user manually, I want one single GRANT that encompasses
> > every user simultaneously.
> >
> > I've tried all of these, and they all are valid in mySQL but none of them
> > actually have the desired result.
> >
> > 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)
> >
> > Here are the results:
> >
> > SELECT * FROM mysql.time_zone_name LIMIT 0, 5000
> >
> > Error Code : 1142
> > SELECT command denied to user 'daevid'@'mycompany.com' for table
> > 'time_zone_name'
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
> >
> >
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@pixelated.net
>



--
Thanks
Suresh Kuna
MySQL DBA

--001636426f6fba351d0492a8e7fd--

Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!

--Apple-Mail-1--366484060
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

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
> 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
--Apple-Mail-1--366484060--

RE: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!

> -----Original Message-----
> From: Jesper Wisborg Krogh [mailto:jesper@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
> > 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?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!

Hi,

On 16/10/2010, at 8:50 AM, Daevid Vincent wrote:

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

What I mean is that you have to explicitly give the grant to each
user that should be allowed to query the table. You can't run one
grant that automatically will apply to all users.

So if you have three users user_a@localhost, user_a@192.168.1.1, and
user_b@localhost you have to run:

GRANT SELECT ON `mysql`.`time_zone_name` TO 'user_a'@localhost;
GRANT SELECT ON `mysql`.`time_zone_name` TO 'user_a'@192.168.1.1;
GRANT SELECT ON `mysql`.`time_zone_name` TO 'user_b'@localhost;

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

Sorry, I'm not sure what you mean. Unless a username and host
combination matches a record in the mysql.user table, then the user
cannot log into the server.

$ mysql -u random_user -p
Enter password:
ERROR 1045 (28000): Access denied for user
'random_user'@'localhost' (using password: NO)

$ mysql -u random_user -p
Enter password:
ERROR 1045 (28000): Access denied for user
'random_user'@'localhost' (using password: YES)

>
> 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'

That is because the

GRANT SELECT ON `mysql`.`time_zone_name` TO '%';

statement only gives the privilege to query the time_zone_name table
to users who use the '%'@'%' account when logging in.

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

I will recommend you to drop all the users that can log in from
arbitrary hosts or with arbitrary usernames. E.g. the
diagnostics@10.10.10.% account is a much better way to create a user
rather than the diagnostics@% account. The latter will allow the
diagnostics user to login from anywhere, whereas
diagnostics@10.10.10.% restricts the login to a small subnet.

Hope that helps.

Jesper

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!

> -----Original Message-----
> From: Jesper Wisborg Krogh [mailto:jesper@noggin.com.au]
> Sent: Friday, October 15, 2010 5:54 PM
> To: MY SQL Mailing list
> Subject: Re: How do I GRANT SELECT to mysql.time_zone_name
> for ANYONE?!
>
> > Any user can get into mysql, it's what they can do after that's the
> > interesting part.
>
> Sorry, I'm not sure what you mean. Unless a username and host
> combination matches a record in the mysql.user table, then the user
> cannot log into the server.
>
> $ mysql -u random_user -p
> Enter password:
> ERROR 1045 (28000): Access denied for user
> 'random_user'@'localhost' (using password: NO)
>
> $ mysql -u random_user -p
> Enter password:
> ERROR 1045 (28000): Access denied for user
> 'random_user'@'localhost' (using password: YES)

Don't use the -p password prompt and you get into mysql...

developer@mypse:~$ mysql -u random_user
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2393
Server version: 5.0.51a-3ubuntu5.7 (Ubuntu)
(random_user@localhost) [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org