"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
