List:General Discussion« Previous MessageNext Message »
From:Jesper Wisborg Krogh Date:October 16 2010 12:54am
Subject:Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!
View as plain text  
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@stripped, 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@stripped.% 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@stripped.% restricts the login to a small subnet.

Hope that helps.

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