From: Jesper Wisborg Krogh (jespernoggin.com.au)
Date: Fri Oct 15 2010 - 19:54:00 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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_alocalhost, user_a192.168.1.1, and
user_blocalhost 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...
>
> developermypse:~$ 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.
> (developerlocalhost) [(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:
>
> developermypse:~$ mysql -uOMT_Master -pSOMEPASS -hmypse -P3306
> agis_core
> (OMT_Mastermypse) [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
diagnostics10.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
diagnostics10.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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]