Re: How to add a host so 'root' can connect to the server from it
Sorry, Sean, I think I forgot in the emails to you to mention that the MySQL server is running on a Solaris machine, I am trying to connect the database from a XP machine which is the one I am using. I tried the same program in other Solaris machine, it works fine. I also noticed that the other Solaris machines are in the same subnet as the one in which Mysql server is running, all of them have 129.173.23.*, but the XP machine is different: 129.173.105.*, could it be possible the firewall on the Solaris system (I already turned off the firewall on the XP when I tried to connect to the mysql server.) block the connection from the XP to the port 3306 though I can use ssh or putty, winscp from the XP machine to remotely log in those Solaris machines. Again, thank you very much for your time and patience. I do appreciate it. Xiaobo > Its been a while since i've dealt much with MySQL permissions, but do you > need > to explicitly state 'localhost' as the machine in some circumstances. > > The other thing i thought of is a guess, as i don't program Java, but have > had > an analogous problem using Perl. It it possible that Java is dealing with > the > passwords using the old password scheme and the server has the passwords > in > the new scheme (or the other way around)? > > Just a couple guesses that are probably incorrect. > > On Thursday 03 November 2005 16:02, [EMAIL PROTECTED] wrote: >> Answers intermixed. See below >> >> "Xiaobo Chen" <[EMAIL PROTECTED]> wrote on 11/03/2005 04:28:08 PM: >> > Thank so much for the detailed explaination. I do appreciate it. >> > >> > It's more clear now. But I still have a question: >> > >> > I do see 'root' after: select user,host from mysql.user; >> > >> > Then I did this as you said: >> > >> > GRANT SELECT ON mydatabase.* TO [EMAIL PROTECTED] IDENTIFIED BY >> >> 'mypassword'; >> >> > then I issue: >> > >> > select * from mysql.user where user='root'; >> > >> > I found the select previlege is still 'N'. >> > >> > Besided this, how do I know 'mydatabase' from those tables in 'mysql' >> > database that 'mydatabase' is allowed to be connected by 'root' from >> the >> > IP. I am confusing here because the 'user' table only give the >> >> association >> >> > betweem 'host' and user 'root' in the Mysql server. But where is the >> > database association? >> > >> > wait a minute, yes, I see. When I issue: >> > >> > select * from mysql.db where user='root'; >> > >> > I did see the association and the SELECT_priv is 'Y' there. >> >> That's correct, the `user` table controls GLOBAL permissions. The `db` >> table controls database-specific permissions (there can be multiple >> databases on any server). >> >> > But, in the java program running in the local XP machine still can NOT >> > connect to the database existing in the other Solaris machine!! >> > >> > I tried in the local XP machine: >> > >> > telnet theserver 3306 >> > >> > I failed. >> > >> > I guess this is why I can not connect to the server. Could be it >> >> possible >> >> > that the Solaris machine deny any request from the PC to the port >> 3306? >> >> I >> >> > can ssh to the server, or using winscp. >> >> There are several possibilities here: >> XP is denying outbound connections to your Solaris machine (quite >> likely) >> Solaris is denying connections from your XP machine (not likely) >> Firewalls, routers, or proxy servers between the XP and Solaris machines >> are blocking the connection attempt. >> >> If you were able to connect to the MySQL server, your MySQL error would >> say that you "could not authenticate" not "could not connect" >> >> > I am really confused here. Is it a mysql issue or the system >> >> configuration >> >> > issue on the server? >> >> I think it's a system configuration issue on the XP machine. I believe >> the >> XP firewall is getting in your way. >> >> > Btw, I can run the same java program from other machine in Solaris >> >> system >> >> > to connect the database as the user 'root'. Does this imply that it's >> > administration issue? >> >> Network administration, not MySQL administration (yet). >> >> > Thanks for your kind help and patience. >> > >> > Xiaobo >> >> My pleasure! >> Shawn Green >> Database Administrator >> Unimin Corporation - Spruce Pine >> >> > > -- > Sean Peters > Senior Programmer, WIREData Inc. > [EMAIL PROTECTED] > > "The software required Windows 2000 or better, so i Downloaded Linux" > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to add a host so 'root' can connect to the server from it
Thanks for your speedy reply. I already turn off the firewall in the XP machine. I have no idea about the system sonfiguration on the Solaris machine. I did this experiment as well: I installed Mysql 4.1.5 on XP machine, then use the same java program to connect to the 'test' data base from the installation. It works because I didn't network to anywhere I guess. Maybe, I should bring this to the system administrtor. It looking like a networking issue. Again, thank you very much for your patience and help. Xiaobo > Answers intermixed. See below > > "Xiaobo Chen" <[EMAIL PROTECTED]> wrote on 11/03/2005 04:28:08 PM: > >> Thank so much for the detailed explaination. I do appreciate it. >> >> It's more clear now. But I still have a question: >> >> I do see 'root' after: select user,host from mysql.user; >> >> Then I did this as you said: >> >> GRANT SELECT ON mydatabase.* TO [EMAIL PROTECTED] IDENTIFIED BY > 'mypassword'; >> >> then I issue: >> >> select * from mysql.user where user='root'; >> >> I found the select previlege is still 'N'. >> >> Besided this, how do I know 'mydatabase' from those tables in 'mysql' >> database that 'mydatabase' is allowed to be connected by 'root' from the >> IP. I am confusing here because the 'user' table only give the > association >> betweem 'host' and user 'root' in the Mysql server. But where is the >> database association? >> >> wait a minute, yes, I see. When I issue: >> >> select * from mysql.db where user='root'; >> >> I did see the association and the SELECT_priv is 'Y' there. >> > > That's correct, the `user` table controls GLOBAL permissions. The `db` > table controls database-specific permissions (there can be multiple > databases on any server). > > >> But, in the java program running in the local XP machine still can NOT >> connect to the database existing in the other Solaris machine!! >> >> I tried in the local XP machine: >> >> telnet theserver 3306 >> >> I failed. >> >> I guess this is why I can not connect to the server. Could be it > possible >> that the Solaris machine deny any request from the PC to the port 3306? > I >> can ssh to the server, or using winscp. > > There are several possibilities here: > XP is denying outbound connections to your Solaris machine (quite likely) > Solaris is denying connections from your XP machine (not likely) > Firewalls, routers, or proxy servers between the XP and Solaris machines > are blocking the connection attempt. > > If you were able to connect to the MySQL server, your MySQL error would > say that you "could not authenticate" not "could not connect" > > >> >> I am really confused here. Is it a mysql issue or the system > configuration >> issue on the server? > > I think it's a system configuration issue on the XP machine. I believe the > XP firewall is getting in your way. > >> >> Btw, I can run the same java program from other machine in Solaris > system >> to connect the database as the user 'root'. Does this imply that it's >> administration issue? > > Network administration, not MySQL administration (yet). > >> >> Thanks for your kind help and patience. >> >> Xiaobo >> >> > > > My pleasure! > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to add a host so 'root' can connect to the server from it
Its been a while since i've dealt much with MySQL permissions, but do you need to explicitly state 'localhost' as the machine in some circumstances. The other thing i thought of is a guess, as i don't program Java, but have had an analogous problem using Perl. It it possible that Java is dealing with the passwords using the old password scheme and the server has the passwords in the new scheme (or the other way around)? Just a couple guesses that are probably incorrect. On Thursday 03 November 2005 16:02, [EMAIL PROTECTED] wrote: > Answers intermixed. See below > > "Xiaobo Chen" <[EMAIL PROTECTED]> wrote on 11/03/2005 04:28:08 PM: > > Thank so much for the detailed explaination. I do appreciate it. > > > > It's more clear now. But I still have a question: > > > > I do see 'root' after: select user,host from mysql.user; > > > > Then I did this as you said: > > > > GRANT SELECT ON mydatabase.* TO [EMAIL PROTECTED] IDENTIFIED BY > > 'mypassword'; > > > then I issue: > > > > select * from mysql.user where user='root'; > > > > I found the select previlege is still 'N'. > > > > Besided this, how do I know 'mydatabase' from those tables in 'mysql' > > database that 'mydatabase' is allowed to be connected by 'root' from the > > IP. I am confusing here because the 'user' table only give the > > association > > > betweem 'host' and user 'root' in the Mysql server. But where is the > > database association? > > > > wait a minute, yes, I see. When I issue: > > > > select * from mysql.db where user='root'; > > > > I did see the association and the SELECT_priv is 'Y' there. > > That's correct, the `user` table controls GLOBAL permissions. The `db` > table controls database-specific permissions (there can be multiple > databases on any server). > > > But, in the java program running in the local XP machine still can NOT > > connect to the database existing in the other Solaris machine!! > > > > I tried in the local XP machine: > > > > telnet theserver 3306 > > > > I failed. > > > > I guess this is why I can not connect to the server. Could be it > > possible > > > that the Solaris machine deny any request from the PC to the port 3306? > > I > > > can ssh to the server, or using winscp. > > There are several possibilities here: > XP is denying outbound connections to your Solaris machine (quite likely) > Solaris is denying connections from your XP machine (not likely) > Firewalls, routers, or proxy servers between the XP and Solaris machines > are blocking the connection attempt. > > If you were able to connect to the MySQL server, your MySQL error would > say that you "could not authenticate" not "could not connect" > > > I am really confused here. Is it a mysql issue or the system > > configuration > > > issue on the server? > > I think it's a system configuration issue on the XP machine. I believe the > XP firewall is getting in your way. > > > Btw, I can run the same java program from other machine in Solaris > > system > > > to connect the database as the user 'root'. Does this imply that it's > > administration issue? > > Network administration, not MySQL administration (yet). > > > Thanks for your kind help and patience. > > > > Xiaobo > > My pleasure! > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > -- Sean Peters Senior Programmer, WIREData Inc. [EMAIL PROTECTED] "The software required Windows 2000 or better, so i Downloaded Linux" -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to add a host so 'root' can connect to the server from it
Answers intermixed. See below "Xiaobo Chen" <[EMAIL PROTECTED]> wrote on 11/03/2005 04:28:08 PM: > Thank so much for the detailed explaination. I do appreciate it. > > It's more clear now. But I still have a question: > > I do see 'root' after: select user,host from mysql.user; > > Then I did this as you said: > > GRANT SELECT ON mydatabase.* TO [EMAIL PROTECTED] IDENTIFIED BY 'mypassword'; > > then I issue: > > select * from mysql.user where user='root'; > > I found the select previlege is still 'N'. > > Besided this, how do I know 'mydatabase' from those tables in 'mysql' > database that 'mydatabase' is allowed to be connected by 'root' from the > IP. I am confusing here because the 'user' table only give the association > betweem 'host' and user 'root' in the Mysql server. But where is the > database association? > > wait a minute, yes, I see. When I issue: > > select * from mysql.db where user='root'; > > I did see the association and the SELECT_priv is 'Y' there. > That's correct, the `user` table controls GLOBAL permissions. The `db` table controls database-specific permissions (there can be multiple databases on any server). > But, in the java program running in the local XP machine still can NOT > connect to the database existing in the other Solaris machine!! > > I tried in the local XP machine: > > telnet theserver 3306 > > I failed. > > I guess this is why I can not connect to the server. Could be it possible > that the Solaris machine deny any request from the PC to the port 3306? I > can ssh to the server, or using winscp. There are several possibilities here: XP is denying outbound connections to your Solaris machine (quite likely) Solaris is denying connections from your XP machine (not likely) Firewalls, routers, or proxy servers between the XP and Solaris machines are blocking the connection attempt. If you were able to connect to the MySQL server, your MySQL error would say that you "could not authenticate" not "could not connect" > > I am really confused here. Is it a mysql issue or the system configuration > issue on the server? I think it's a system configuration issue on the XP machine. I believe the XP firewall is getting in your way. > > Btw, I can run the same java program from other machine in Solaris system > to connect the database as the user 'root'. Does this imply that it's > administration issue? Network administration, not MySQL administration (yet). > > Thanks for your kind help and patience. > > Xiaobo > > My pleasure! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: How to add a host so 'root' can connect to the server from it
Thank so much for the detailed explaination. I do appreciate it. It's more clear now. But I still have a question: I do see 'root' after: select user,host from mysql.user; Then I did this as you said: GRANT SELECT ON mydatabase.* TO [EMAIL PROTECTED] IDENTIFIED BY 'mypassword'; then I issue: select * from mysql.user where user='root'; I found the select previlege is still 'N'. Besided this, how do I know 'mydatabase' from those tables in 'mysql' database that 'mydatabase' is allowed to be connected by 'root' from the IP. I am confusing here because the 'user' table only give the association betweem 'host' and user 'root' in the Mysql server. But where is the database association? wait a minute, yes, I see. When I issue: select * from mysql.db where user='root'; I did see the association and the SELECT_priv is 'Y' there. But, in the java program running in the local XP machine still can NOT connect to the database existing in the other Solaris machine!! I tried in the local XP machine: telnet theserver 3306 I failed. I guess this is why I can not connect to the server. Could be it possible that the Solaris machine deny any request from the PC to the port 3306? I can ssh to the server, or using winscp. I am really confused here. Is it a mysql issue or the system configuration issue on the server? Btw, I can run the same java program from other machine in Solaris system to connect the database as the user 'root'. Does this imply that it's administration issue? Thanks for your kind help and patience. Xiaobo > "Xiaobo Chen" <[EMAIL PROTECTED]> wrote on 11/03/2005 02:54:16 PM: > >> I have a question related to connection control. >> >> If I want to connect to one database A in the server from another >> computer. I am using 'root' and it password. But how shall I add the > host >> in which table of which database (mysql?) so that the user 'root' can >> connect to the database? >> >> A little explaination is that, I used 'root' in a java program which use >> JDBC and tried to connect a database A in the server. And the program is >> run in a XP machine. In that XP machine, there is no user called 'root'. >> >> I read through this link: >> >> http://dev.mysql.com/doc/refman/5.0/en/connection-access.html >> >> I still didn't know how to do it. Anyone can help me? >> >> Xiaobo >> > > You are correct in saying that the XP machine doesn't have a user called > 'root'. The fact that your database server's operating system (I assume > you are hosting your database on a LINUX or UNIX server) has a user called > 'root' is only confusing. The only 'root' you need to worry about is the > one defined as a user WITHIN MySQL. > > run this query > > SELECT * from mysql.user; > > Can you see a user called 'root' in the results? There may be other users > listed there, too. MySQL security is based mostly on the values of the > columns `Host` and `User`. Look at part of a sample `user` table: > > localhost>select Host,User from mysql.user; > +---++ > | Host | User | > +---++ > | % | odbctest | > | 192.168.% | sgreen | > | % | slave | > | localhost | ChartReader| > | localhost | cpwapp2| > | localhost | dataimporter | > | localhost | datareader | > | localhost | odbctest | > | localhost | root | > +---++ > 8 rows in set (0.03 sec) > > For this server, the user 'root' can only log in from the local machine > (the computer running the MySQL daemon). Why? That is the only value > allowed by its `Host` entry. The user 'sgreen' cannot login from the local > machine. However, that user can try to login from any machine on the > 192.168.xx.xx subnet. The user 'odbctest' can login to the MySQL server > from either the local machine or from any outside address. > > Here are some valid login combinations: > -- > Username - location of that user > -- > sgreen - 192.168.1.17 > odbctest - 192.168.1.17 > root - localhost (from the machine hosting the MySQL server) > > Here are some invalid (disallowed) login combinations: > -- > Username - location of that user > -- > sgreen - 10.10.1.45 > root - 192.168.1.1 > cpwapp2 - 10.10.1.45 > > You manage the contents of the `user` table (and a few others, too) with > the GRANT command and the REVOKE command. If a user attempts to login from > an address they do not have permission to use (you limit the usable > addresses with the GRANT statement you used you define the user account > within MySQL) they will not be able to connect to the MySQL server. > > For example, > *I wanted to create a new user account for the login 'idiotuser' > *AND only allow this login to select data from any table in the `safety` > database (and no others) > *AND their log
Re: How to add a host so 'root' can connect to the server from it
"Xiaobo Chen" <[EMAIL PROTECTED]> wrote on 11/03/2005 02:54:16 PM: > I have a question related to connection control. > > If I want to connect to one database A in the server from another > computer. I am using 'root' and it password. But how shall I add the host > in which table of which database (mysql?) so that the user 'root' can > connect to the database? > > A little explaination is that, I used 'root' in a java program which use > JDBC and tried to connect a database A in the server. And the program is > run in a XP machine. In that XP machine, there is no user called 'root'. > > I read through this link: > > http://dev.mysql.com/doc/refman/5.0/en/connection-access.html > > I still didn't know how to do it. Anyone can help me? > > Xiaobo > You are correct in saying that the XP machine doesn't have a user called 'root'. The fact that your database server's operating system (I assume you are hosting your database on a LINUX or UNIX server) has a user called 'root' is only confusing. The only 'root' you need to worry about is the one defined as a user WITHIN MySQL. run this query SELECT * from mysql.user; Can you see a user called 'root' in the results? There may be other users listed there, too. MySQL security is based mostly on the values of the columns `Host` and `User`. Look at part of a sample `user` table: localhost>select Host,User from mysql.user; +---++ | Host | User | +---++ | % | odbctest | | 192.168.% | sgreen | | % | slave | | localhost | ChartReader| | localhost | cpwapp2| | localhost | dataimporter | | localhost | datareader | | localhost | odbctest | | localhost | root | +---++ 8 rows in set (0.03 sec) For this server, the user 'root' can only log in from the local machine (the computer running the MySQL daemon). Why? That is the only value allowed by its `Host` entry. The user 'sgreen' cannot login from the local machine. However, that user can try to login from any machine on the 192.168.xx.xx subnet. The user 'odbctest' can login to the MySQL server from either the local machine or from any outside address. Here are some valid login combinations: -- Username - location of that user -- sgreen - 192.168.1.17 odbctest - 192.168.1.17 root - localhost (from the machine hosting the MySQL server) Here are some invalid (disallowed) login combinations: -- Username - location of that user -- sgreen - 10.10.1.45 root - 192.168.1.1 cpwapp2 - 10.10.1.45 You manage the contents of the `user` table (and a few others, too) with the GRANT command and the REVOKE command. If a user attempts to login from an address they do not have permission to use (you limit the usable addresses with the GRANT statement you used you define the user account within MySQL) they will not be able to connect to the MySQL server. For example, *I wanted to create a new user account for the login 'idiotuser' *AND only allow this login to select data from any table in the `safety` database (and no others) *AND their login password is to be 'dunce' *AND I only want 'idiotuser' to be able to connect from one machine (IP address 192.168.20.2) I would use the follwing GRANT statement: GRANT SELECT ON safety.* TO [EMAIL PROTECTED] IDENTIFIED BY 'dunce'; None of what I just talked about has anything to do with the Operating System user 'root'. The permissions for that user belong to the OS, not MySQL. You do not use OS permissions to authenticate with a MySQL server. You have to use MySQL user accounts to authenticate with a MySQL server. additional reading: http://dev.mysql.com/doc/refman/5.0/en/privileges.html http://dev.mysql.com/doc/refman/5.0/en/grant.html Let me know if I helped or just made it more confusing... Shawn Green Database Administrator Unimin Corporation - Spruce Pine
