Re: mysql database, user table, two root accounts
On 9 Jan 2004, at 22:43, Michael Stassen wrote: As [EMAIL PROTECTED] and [EMAIL PROTECTED] are separate entries in the user table, each with its own password and privileges, they are 2 separate root accounts from mysql's perspective. You could choose to think of them as the same account by keeping their settings the same, or you could choose to think of them as separate root accounts, possibly with separate settings. You could, for example, give root fewer privs when connecting externally than via localhost. Many people, myself included, eliminate [EMAIL PROTECTED] altogether, so that the root user can only connect from localhost, or replace the % with something more limiting (say [EMAIL PROTECTED]). Ask yourself which IPs should be allowed to administer mysql as root and act accordingly. How does MySQL decide which entry to use when authenticating? Eg. if you've two host entries; one '192.%' and the other '192.168.%' - and you connect from 192.168.100.12, which row gets chosen? Perhaps it's the more exact match? i.e. 192.168.% But what if there isn't a more exact match... i.e. choose between '192.%' or '%.168.%' What if there are two entries - 'localhost' and '127.0.0.1' ? -- Regards, Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql database, user table, two root accounts
Steve Folly wrote: On 9 Jan 2004, at 22:43, Michael Stassen wrote: As [EMAIL PROTECTED] and [EMAIL PROTECTED] are separate entries in the user table, each with its own password and privileges, they are 2 separate root accounts from mysql's perspective. You could choose to think of them as the same account by keeping their settings the same, or you could choose to think of them as separate root accounts, possibly with separate settings. You could, for example, give root fewer privs when connecting externally than via localhost. Many people, myself included, eliminate [EMAIL PROTECTED] altogether, so that the root user can only connect from localhost, or replace the % with something more limiting (say [EMAIL PROTECTED]). Ask yourself which IPs should be allowed to administer mysql as root and act accordingly. How does MySQL decide which entry to use when authenticating? This is documented in the manual http://www.mysql.com/doc/en/Connection_access.html. The basic idea is that mysql sorts the user table from most specific to least, with host taking precedence over user. Eg. if you've two host entries; one '192.%' and the other '192.168.%' - and you connect from 192.168.100.12, which row gets chosen? As I understand it, 192.168.% is more specific than 192.%, so 192.168.100.12 would match 192.168.% Perhaps it's the more exact match? i.e. 192.168.% That's my understanding. But what if there isn't a more exact match... i.e. choose between '192.%' or '%.168.%' Well, I can't imagine why you would put %.168.% in for host. If you did, I think 192.% would be more specific than %.168.%, but the manual is unclear on that. I suppose you could try it and see. What if there are two entries - 'localhost' and '127.0.0.1' ? To mysql, those are not the same. localhost is a unix socket connection, 127.0.0.1 is a TCP/IP connection. So, mysql -u username -p would connect as [EMAIL PROTECTED], but mysql -h 127.0.0.1 -u username -p would connect as [EMAIL PROTECTED] Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql database, user table, two root accounts
On 10 Jan 2004, at 17:47, Michael Stassen wrote: Eg. if you've two host entries; one '192.%' and the other '192.168.%' - and you connect from 192.168.100.12, which row gets chosen? As I understand it, 192.168.% is more specific than 192.%, so 192.168.100.12 would match 192.168.% My thoughts exactly. But what if there isn't a more exact match... i.e. choose between '192.%' or '%.168.%' Well, I can't imagine why you would put %.168.% in for host. If you did, I think 192.% would be more specific than %.168.%, but the manual is unclear on that. I suppose you could try it and see. True, I can't imagine why you would want to use %.168.% either; I was just curious. I've just tried it myself... (OK, so I was lazy before! :) - MySQL appears to prefer 192.% over %.168.% What if there are two entries - 'localhost' and '127.0.0.1' ? To mysql, those are not the same. localhost is a unix socket connection, 127.0.0.1 is a TCP/IP connection. So, mysql -u username -p would connect as [EMAIL PROTECTED], but mysql -h 127.0.0.1 -u username -p would connect as [EMAIL PROTECTED] Makes sense. Thanks very much; I was just curious! Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql database, user table, two root accounts
That's not two root accounts. What that means is this: The first line defines privileges for root connecting from localhost The second line defines privileges for root connecting from any remote host. Hence the %. It implies [EMAIL PROTECTED] Hope this helps. Arjun Subramanian Georgia Tech Station 32003 Atlanta GA 30332 Cell: +404.429.5513 http://www.arjunweb.com -Original Message- From: Leo Donahue [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 2:47 PM To: [EMAIL PROTECTED] Subject: mysql database, user table, two root accounts I am less than 24 hours new to MySql. I have executed the following sql scripts: use mysql; delete from user where User=''; delete from db where User=''; flush privileges; select host, user, password from user; The last sql query yields the following: hostuserpassword - localhost roothexadecimal values. % rootnothing here. Why are there two root accounts? Thanks, ld -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql database, user table, two root accounts
Yes, this helps thank you. -Original Message- From: Arjun Subramanian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 2:16 PM To: 'Leo Donahue'; [EMAIL PROTECTED] Subject: RE: mysql database, user table, two root accounts That's not two root accounts. What that means is this: The first line defines privileges for root connecting from localhost The second line defines privileges for root connecting from any remote host. Hence the %. It implies [EMAIL PROTECTED] Hope this helps. Arjun Subramanian Georgia Tech Station 32003 Atlanta GA 30332 Cell: +404.429.5513 http://www.arjunweb.com -Original Message- From: Leo Donahue [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 2:47 PM To: [EMAIL PROTECTED] Subject: mysql database, user table, two root accounts I am less than 24 hours new to MySql. I have executed the following sql scripts: use mysql; delete from user where User=''; delete from db where User=''; flush privileges; select host, user, password from user; The last sql query yields the following: hostuserpassword - localhost roothexadecimal values. % rootnothing here. Why are there two root accounts? Thanks, ld -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql database, user table, two root accounts
As [EMAIL PROTECTED] and [EMAIL PROTECTED] are separate entries in the user table, each with its own password and privileges, they are 2 separate root accounts from mysql's perspective. You could choose to think of them as the same account by keeping their settings the same, or you could choose to think of them as separate root accounts, possibly with separate settings. You could, for example, give root fewer privs when connecting externally than via localhost. Many people, myself included, eliminate [EMAIL PROTECTED] altogether, so that the root user can only connect from localhost, or replace the % with something more limiting (say [EMAIL PROTECTED]). Ask yourself which IPs should be allowed to administer mysql as root and act accordingly. In any case, the [EMAIL PROTECTED] entry you quoted below has no password! To be safe, you should immediately assign it a password or drop it. See http://www.mysql.com/doc/en/Privileges.html and http://www.mysql.com/doc/en/User_Account_Management.html for more. Michael Leo Donahue wrote: Yes, this helps thank you. -Original Message- From: Arjun Subramanian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 2:16 PM To: 'Leo Donahue'; [EMAIL PROTECTED] Subject: RE: mysql database, user table, two root accounts That's not two root accounts. What that means is this: The first line defines privileges for root connecting from localhost The second line defines privileges for root connecting from any remote host. Hence the %. It implies [EMAIL PROTECTED] Hope this helps. Arjun Subramanian Georgia Tech Station 32003 Atlanta GA 30332 Cell: +404.429.5513 http://www.arjunweb.com -Original Message- From: Leo Donahue [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 2:47 PM To: [EMAIL PROTECTED] Subject: mysql database, user table, two root accounts I am less than 24 hours new to MySql. I have executed the following sql scripts: use mysql; delete from user where User=''; delete from db where User=''; flush privileges; select host, user, password from user; The last sql query yields the following: hostuserpassword - localhost roothexadecimal values. % rootnothing here. Why are there two root accounts? Thanks, ld -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]