Well I have just about everything working now. I've managed to install apache2 and PHP5 (I don't quite understand what I did wrong the first time but going back to PHP4 and then to PHP5 again got it working).
I've also installed phpMyAdmin and that's working fine too.
However I still can't get ordinary users to work the way I expect.
I'm using phpMyAdmin to do all this now.
I deleted all the entries for user 'chris' and then added a new user 'chris' with host set to 'Any host', with password set to 'No password' and with all privileges set. This appears to work OK, phpMyadmin says I have a user as follows:-
chris % No ALL PRIVILEGES Yes Edit
(No is 'Password' and Yes is 'Grant')
However if I then say 'mysql -uchris' and try 'use fred' I still get:- ERROR 1044: Access denied for user: '@localhost' to database 'fred'
If, on the other hand, I do exactly as above except I set host to 'Local' then user chris works as expected.
Something is certainly awry somewhere, it may be my understanding but at the very least things are not quite how one would expect from what one is being told.
Is there some security thing that refuses to allow an 'Any host' user to have lots of privileges? No, I've tried creating a user with few privileges and 'Any Host' and they get the 'Access denied...' error too. It seems to me that the 'Any Host' doesn't work.
Chris Green writes:
However if I then say 'mysql -uchris' and try 'use fred' I still get:- ERROR 1044: Access denied for user: '@localhost' to database 'fred'
If, on the other hand, I do exactly as above except I set host to 'Local' then user chris works as expected.
Now that I think more about this, I recall that "any host" specifically excludes "localhost". I'm sure there's a good reason too, but it escapes me now. However, I did find, from http://dev.mysql.com/doc/mysql/en/Access_denied.html
.. the following which I think is relevant:
If you can't figure out why you get Access denied, remove from the user table all entries that have Host values containing wildcards (entries that contain `%' or `_'). A very common error is to insert a new entry with Host='%' and User='some_user', thinking that this will allow you to specify localhost to connect from the same machine. The reason that this doesn't work is that the default privileges include an entry with Host='localhost' and User=''. Because that entry has a Host value 'localhost' that is more specific than '%', it is used in preference to the new entry when connecting from localhost! The correct procedure is to insert a second entry with Host='localhost' and User='some_user', or to delete the entry with Host='localhost' and User=''. After deleting the entry, remember to issue a FLUSH PRIVILEGES statement to reload the grant tables.
As a general rule, just think of localhost and other hosts separately.
On Sun, Nov 07, 2004 at 12:17:26AM -0000, Mark Rogers wrote:
Chris Green writes:
However if I then say 'mysql -uchris' and try 'use fred' I still get:- ERROR 1044: Access denied for user: '@localhost' to database 'fred'
If, on the other hand, I do exactly as above except I set host to 'Local' then user chris works as expected.
Now that I think more about this, I recall that "any host" specifically excludes "localhost". I'm sure there's a good reason too, but it escapes me now. However, I did find, from http://dev.mysql.com/doc/mysql/en/Access_denied.html
.. the following which I think is relevant:
If you can't figure out why you get Access denied, remove from the user table all entries that have Host values containing wildcards (entries that contain `%' or `_'). A very common error is to insert a new entry with Host='%' and User='some_user', thinking that this will allow you to specify localhost to connect from the same machine. The reason that this doesn't work is that the default privileges include an entry with Host='localhost' and User=''. Because that entry has a Host value 'localhost' that is more specific than '%', it is used in preference to the new entry when connecting from localhost! The correct procedure is to insert a second entry with Host='localhost' and User='some_user', or to delete the entry with Host='localhost' and User=''. After deleting the entry, remember to issue a FLUSH PRIVILEGES statement to reload the grant tables.As a general rule, just think of localhost and other hosts separately.
Ah, yes, I think that would explain what I'm seeing, thanks. I do want to have the 'Any Host' access as this is on a small home network and I want to allow other computers to access that database. It's behind a NAT firewall so the outside world won't be able to see it.
On Sun, 7 Nov 2004 15:01:06 +0000, "Chris Green" chris@areti.co.uk said:
On Sun, Nov 07, 2004 at 12:17:26AM -0000, Mark Rogers wrote:
Now that I think more about this, I recall that "any host" specifically excludes "localhost". As a general rule, just think of localhost and other hosts separately.
Ah, yes, I think that would explain what I'm seeing, thanks. I do want to have the 'Any Host' access as this is on a small home network and I want to allow other computers to access that database. It's behind a NAT firewall so the outside world won't be able to see it.
I'm sure you know this already, but:
PHP runs locally so only needs privileges for 'localhost', its only if your running a MySQL client on another machine on the network that it needs to have privileges for a host other than 'localhost' (whether this be the default MySQL client or any other client software you may write).
You will also need to make sure that the line 'skip-networking' in /etc/mysql/my.cnf is commented out otherwise your privileges settings for hosts other than localhost will be useless ;-)
Cheers, Richard
On 8/11/2004, "Richard Lewis" richardlewis@fastmail.co.uk wrote:
On Sun, 7 Nov 2004 15:01:06 +0000, "Chris Green" chris@areti.co.uk said:
On Sun, Nov 07, 2004 at 12:17:26AM -0000, Mark Rogers wrote:
Now that I think more about this, I recall that "any host" specifically excludes "localhost". As a general rule, just think of localhost and other hosts separately.
Ah, yes, I think that would explain what I'm seeing, thanks. I do want to have the 'Any Host' access as this is on a small home network and I want to allow other computers to access that database. It's behind a NAT firewall so the outside world won't be able to see it.
I'm sure you know this already, but:
PHP runs locally so only needs privileges for 'localhost', its only if your running a MySQL client on another machine on the network that it needs to have privileges for a host other than 'localhost' (whether this be the default MySQL client or any other client software you may write).
You will also need to make sure that the line 'skip-networking' in /etc/mysql/my.cnf is commented out otherwise your privileges settings for hosts other than localhost will be useless ;-)
Cheers, Richard
Just a heads up if you're running Mandrake. This is from their security list and it sounds like you may have hit this problem:-
"I am currently running my mail and web server on MDK 9.2 which is kept "up to the minute" with all of the latest updates and patches advised on this list. I am running 4 sites (virtual hosting) using mySQL. Up until now I have never had a problem administering the mySQL databases with webmin or phpMyAdmin but suddenly after the update, I can no longer "see" the databases - only the one called test. The databases are still there and they are still functioning but I cannot now administer them. My usual username and password returns #1045 - Access denied with phpMyAdmin and webmin tells me there are no databases other than test.
What has changed to cause this - I need to do some work urgently. The databases cannot be accessed either remotely or locally."
Matt
On Mon, Nov 08, 2004 at 09:58:30AM +0000, Richard Lewis wrote:
On Sun, 7 Nov 2004 15:01:06 +0000, "Chris Green" chris@areti.co.uk said:
On Sun, Nov 07, 2004 at 12:17:26AM -0000, Mark Rogers wrote:
Now that I think more about this, I recall that "any host" specifically excludes "localhost". As a general rule, just think of localhost and other hosts separately.
Ah, yes, I think that would explain what I'm seeing, thanks. I do want to have the 'Any Host' access as this is on a small home network and I want to allow other computers to access that database. It's behind a NAT firewall so the outside world won't be able to see it.
I'm sure you know this already, but:
PHP runs locally so only needs privileges for 'localhost', its only if your running a MySQL client on another machine on the network that it needs to have privileges for a host other than 'localhost' (whether this be the default MySQL client or any other client software you may write).
I 'knew' it in the sense that if I'd thought about it I would have know it! :-)
You will also need to make sure that the line 'skip-networking' in /etc/mysql/my.cnf is commented out otherwise your privileges settings for hosts other than localhost will be useless ;-)
OK, thanks.
-
Chris Green -
Mark Rogers -
Matt Parker -
Richard Lewis