privileges table: interpretation of _ changed
Dear mySQl community, In the following I do not only list a problem, others might also run in, but I also want to point out a stangeness of one of your fixes. Recently there was a change in 3.23.49 (on Debian/Woody): * Applied patch for a bug that wrongly interpreted '_' in conjunction with the GRANT PRIVILEGES command as wildcard instead as literal character giving an unprivileged mysql user the possibility to grant himself rights on tables he has no privileges on. [CAN-2004-0957, http://bugs.mysql.com/3933] (Thanks to Sean Finney for creating the patch from the RedHat backportingand the MySQL bitkeeper changeset). Closes: #285276, #296674 Du to this, some of our users with permissions to db xyz00\\_% did not have any rights on such db name patterns anymore: mysql GRANT ALL ON xyz00_abc.* TO xyz00_foo; ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'xyz00_abc' We fixed this by giving rights to db xyz00_% instead of xyz00\_% by applying: echo UPDATE mysql.db SET Db = concat(User,'_%') \ WHERE Db like '_\\_\\%' | mysql echo FLUSH PRIVILEGES | mysql So much, if somebody else runs into the same problem. But I don't think that the change was ok. Why not interpreting _ as a wildcard character, but %? And I hope you will not also disable % here. I might have misunderstood the error report, but if somebody grants rights to dbs including _, it should be interpreted as a wildcard. If \_ is used, it should be a _ character with no wildcard functionality. Who is wrong? Michael -- Michael Hönnig|Boytinstr. 10|D-22143 Hamburg | http://michael.hoennig.de http://www.skype.org:mhoennig|http://www.jabber.org:[EMAIL PROTECTED] business networking: http://www.openbc.com/go/invuid/Michael_Hoennig GPG KeyID EC5C271A -- 9DC0 53EC 1549 DA84 A939 15CC C0B7 8FBF EC5C 271A -- Hostsharing eG / Boytinstr. 10 / D-22143 Hamburg phone+fax: +49 700 HOSTSHARING (= +49 700 46787427) Homepage: http://www.hostsharing.net Networking: http://www.openbc.com/go/invuid/Michael_Hoennig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where to report a bug? (was: mysql from localhost vs. remote)
Hi Benjamin, INSERT INTO mysql.db VALUES ('%','xyz00_%','xyz00','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); Has the second line anything to do with the behaviour? It shouldn't as you use xyz00 below and user field will match only the user name 'xyz00_%'. nope, has nothing to do with the issue. But the second field is db, not user. Thus, the statement is correct by itself. local mysql -h192.168.121.34 -uxyz00 -p1234 -e 'select user()' +--+ | user() | +--+ | [EMAIL PROTECTED] | +--+ It works fine for me, you see? And no, I have not any other entry in the privilege tables, that would allow user xyz00@localhost to connect. Thanks for eveluating this issue so thorowly. The interesting part is, why does it display for you xyz00@localhost, whilst it displays an IP for me? Are you sure you copied the correct error message? If so, my first guess would be that your hosts config is mixed up a bit. You might be right, because it works today for me too! I just wanted to try it again to check the error message, and now it suddenly works. I will do some more tests, but it seems, the problem was somewhere else. We had another, similar, issue two days ago. FTP logfiles showed localhost for the client when FTP was used via SSH tunnel. Not it is the hostname. I will check with the other hostmasters, if somebody changed something. Anyhow, xyz00@localhost should match the first of the both lines in the user table. That was my point, it SHOULD have worked anyway, but it didn't. Doubling the user entries is not a good solution, by my opionon, because it means that we have to maintain double rights and passwords. But only in the user table. You can use the hosts table to tell MySQL that two (or more) host should be viewed as a group and avoid doubling entries in the other ones this way. I have not understood this yet, but I will check up with my colleagues (I am not the mysql expert as you can easily see, but our mysql expert is unavailable these days). Thanks for your help! Michael -- Hostsharing eG / c/o Michael Hönnig / Boytinstr. 10 / D-22143 Hamburg phone:+49/40/67581419 / mobile:+49/177/3787491 / fax:++49/40/67581426 http://www.hostsharing.net --- Webhosting Spielregeln selbst gemacht - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql from localhost vs. remote
Hi mysql list members, We use the following statements to setup users and rigts for a mysql server which is accessible locally and via internet: INSERT INTO mysql.user VALUES ('%','xyz00',PASSWORD('...'), 'N','N','N','N','N','N','N','N','N','N','N','N','N','N'); INSERT INTO mysql.db VALUES ('%','xyz00_%','xyz00','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); The server runs on 66.70.34.150:3307. We can connect via socket locally and via Host:Port remote: remote-system mysql -h 66.70.34.150 -P 3307 -u xyz00 -p = works local-system mysql -S /var/run/mysql-ext/mysql.sock -u yxz00 -p = works But we can NOT connect locally by host:port: local-system mysql -h 66.70.34.150 -O 3307 -u xyz00 -p = ERROR 1045: Access denied for user: 'xyz00@localhost' (Using password: YES) On a mysqld which is accessible only locally the both INSERT statements above would have localhost instead of %. Why does localhost not qualify for %? Do we have to double the entries, having separate ones for localhost? It does not make sense, does it? Thanks Michael -- Hostsharing eG / c/o Michael Hönnig / Boytinstr. 10 / D-22143 Hamburg phone:+49/40/67581419 / mobile:+49/177/3787491 / fax:++49/40/67581426 http://www.hostsharing.net --- Webhosting Spielregeln selbst gemacht - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql from localhost vs. remote
Hi Dimitry, But we can NOT connect locally by host:port: local-system mysql -h 66.70.34.150 -O 3307 -u xyz00 -p = ERROR 1045: Access denied for user: 'xyz00@localhost' (Using password: YES) On a mysqld which is accessible only locally the both INSERT statements above would have localhost instead of %. Why does localhost not qualify for %? Probably because you already have entry for 'localhost','%' or something to that effect. nope, there is not such an entry. But IF we add it, we can login. This just means that we have to insert two entries (% and localhost) each. It looks clumsy to me. Michael -- Hostsharing eG / c/o Michael Hönnig / Boytinstr. 10 / D-22143 Hamburg phone:+49/40/67581419 / mobile:+49/177/3787491 / fax:++49/40/67581426 http://www.hostsharing.net --- Webhosting Spielregeln selbst gemacht - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql from localhost vs. remote
Hi Ireneusz, Hi I see: local-system mysql -h 66.70.34.150 -O 3307 -u xyz00 -p hmm. I always use: mysql -u xyz -p -h ... -P 3306 Is the -O option in the right place. Maybe it sould be -P like PORT. Isn't?? sorry, typo in my mail. -P of course. Alles Gute wünscht Michael -- Hostsharing eG / c/o Michael Hönnig / Boytinstr. 10 / D-22143 Hamburg phone:+49/40/67581419 / mobile:+49/177/3787491 / fax:++49/40/67581426 http://www.hostsharing.net --- Webhosting Spielregeln selbst gemacht - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
where to report a bug? (was: mysql from localhost vs. remote)
Hi mysql list members, Meanwhile I am pretty sure that this strange behaviour is a bug. But obviously I am blind. Where can mysql bugs be reportet? The strange behaviour is (I asked about it yestereday): We use the following statements to setup users and rigts for a mysql server which is accessible locally and via internet: INSERT INTO mysql.user VALUES ('%','xyz00',PASSWORD('...'), 'N','N','N','N','N','N','N','N','N','N','N','N','N','N'); INSERT INTO mysql.db VALUES ('%','xyz00_%','xyz00','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); The server runs on 66.70.34.150:3307. We can connect via socket locally and via Host:Port remote: remote-system mysql -h 66.70.34.150 -P 3307 -u xyz00 -p = works local-system mysql -S /var/run/mysql-ext/mysql.sock -u yxz00 -p = works But we can NOT connect locally by host:port: local-system mysql -h 66.70.34.150 -P 3307 -u xyz00 -p = ERROR 1045: Access denied for user: 'xyz00@localhost' (Using password: YES) On a mysqld which is accessible only locally the both INSERT statements above would have localhost instead of %. The bug is, in my opionon, that localhost does not qualify for %? Doubling the user entries is not a good solution, by my opionon, because it means that we have to maintain double rights and passwords. Ok, is there a bugreport possibility for mysql? Thanks Michael -- Hostsharing eG / c/o Michael Hönnig / Boytinstr. 10 / D-22143 Hamburg phone:+49/40/67581419 / mobile:+49/177/3787491 / fax:++49/40/67581426 http://www.hostsharing.net --- Webhosting Spielregeln selbst gemacht - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php