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 use...@localhost, use...@192.168.1.1, and
use...@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...
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'
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
diagnost...@10.10.10.% account is a much better way to create a user
rather than the diagnost...@% account. The latter will allow the
diagnostics user to login from anywhere, whereas
diagnost...@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=arch...@jab.org
