Re: Migrating MySQL users
Hello. Have you run 'FLUSH PRIVILEGES'? [EMAIL PROTECTED] wrote: On Sun, Sep 04, 2005 at 10:08:32PM -0400, [EMAIL PROTECTED] wrote: Now I have all the users in the new mysql.user table. But when I try to login, the same credentials do not work. A bit more info on this. This interesting error happens with GRANT: mysql grant all on testing.* to testing@'%' identified by 'somepassword'; ERROR 1133 (42000): Can't find any matching row in the user table mysql select host, user from mysql.user where user='testing'; +--+-+ | host | user| +--+-+ | %| testing | +--+-+ 1 row in set (0.01 sec) But a new user works fine: mysql grant all on testing.* to buttface@'%' identified by 'somepassword'; Query OK, 0 rows affected (0.00 sec) I can update the old user's password: mysql update mysql.user set password=password('somepassword') where user='testing'; Query OK, 1 row affected (0.00 sec) But still cannot login: # mysql -u testing -psomepassword testing ERROR 1045 (28000): Access denied for user 'testing'@'localhost' (using password : YES) Thanks a ton for any suggestions! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrating MySQL users
Hello, First all it is not recommended at all to dump and import user information. Secondly the user authentication system has changed between 4.0 and 4.1 so old passwords from 4.0 wont work. Its given in the docs. Thirdly, MySQL does not store the actual password in its database. It always keeps a hash of the password and all the comparison are done against it. Karam --- [EMAIL PROTECTED] wrote: Hello everyone, I'm working on migrating a number of users to a different database server. mysqldump makes this quite easy, but I've run into a surprising issue with the new server. I imported things like this: # mysqldump --skip-add-drop --all-databases | mysql -h newbox -u root -p Somehow this didn't get the mysql.user table correctly. So I dumped that one seperately like this: # mysqldump --skip-add-drop --skip-extended mysql user | mysql -h newbox -u root -p mysql Now I have all the users in the new mysql.user table. But when I try to login, the same credentials do not work. Both DB servers are 4.1 servers, one happens to be Debian and the other RedHat. Both servers have the wide password field and use old_password in my.cnf. Any reason why these moved accounts are unable to authenticate? -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrating MySQL users
On Mon, Sep 05, 2005 at 12:41:50PM +0300, Gleb Paharenko wrote: Hello. Have you run 'FLUSH PRIVILEGES'? Doh! I got everything else find, but forgot something stupid. Murphy's law, I suppose. Thanks for the prompt! Ross -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrating MySQL users
On Mon, Sep 05, 2005 at 11:00:43AM -0700, Karam Chand wrote: First all it is not recommended at all to dump and import user information. Why not? They are just entries in a database, like any other. How would you propose I move a large batch of users to another database server? Secondly the user authentication system has changed between 4.0 and 4.1 so old passwords from 4.0 wont work. Its given in the docs. Re-read my email: I'm moving from 4.1 - 4.1. Re-read the docs: 4.1 can authenticate against both hash formats. I just forgot to flush privileges, that's all! -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Migrating MySQL users
Hello everyone, I'm working on migrating a number of users to a different database server. mysqldump makes this quite easy, but I've run into a surprising issue with the new server. I imported things like this: # mysqldump --skip-add-drop --all-databases | mysql -h newbox -u root -p Somehow this didn't get the mysql.user table correctly. So I dumped that one seperately like this: # mysqldump --skip-add-drop --skip-extended mysql user | mysql -h newbox -u root -p mysql Now I have all the users in the new mysql.user table. But when I try to login, the same credentials do not work. Both DB servers are 4.1 servers, one happens to be Debian and the other RedHat. Both servers have the wide password field and use old_password in my.cnf. Any reason why these moved accounts are unable to authenticate? -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrating MySQL users
On Sun, Sep 04, 2005 at 10:08:32PM -0400, [EMAIL PROTECTED] wrote: Now I have all the users in the new mysql.user table. But when I try to login, the same credentials do not work. A bit more info on this. This interesting error happens with GRANT: mysql grant all on testing.* to testing@'%' identified by 'somepassword'; ERROR 1133 (42000): Can't find any matching row in the user table mysql select host, user from mysql.user where user='testing'; +--+-+ | host | user| +--+-+ | %| testing | +--+-+ 1 row in set (0.01 sec) But a new user works fine: mysql grant all on testing.* to buttface@'%' identified by 'somepassword'; Query OK, 0 rows affected (0.00 sec) I can update the old user's password: mysql update mysql.user set password=password('somepassword') where user='testing'; Query OK, 1 row affected (0.00 sec) But still cannot login: # mysql -u testing -psomepassword testing ERROR 1045 (28000): Access denied for user 'testing'@'localhost' (using password : YES) Thanks a ton for any suggestions! -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]