Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!
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 dae...@daevid.com 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=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!
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 joh...@pixelated.netwrote: 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 dae...@daevid.com 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=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Thanks Suresh Kuna MySQL DBA
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.netwrote: 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
RE: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!
-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.netwrote: 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
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 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
RE: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!
-Original Message- From: Jesper Wisborg Krogh [mailto:jes...@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... develo...@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_u...@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=arch...@jab.org
