Re: Can't connect as non-root user to database
Hi Shawn, I understand the logic behind seperating local and remote users, postgresql does the same thing in its pg_hba.conf file. However, what I don't understand is the way this turned out to be such a huge problem (for me), as it worked already with MySQL-5.1 a few years ago. I've worked with other DBMs a bit as well, so I am not an absolute noob. To be honest I am a bit troubled with your explanation. If the % does not include "localhost", why is it now possible to me to login as "someone"@"%" after I have deleted that anonymous user claudio mentioned (thanks again!). Furthermore, why does mysql print "access denied for someone@localhost" even when I specify -h127.0.0.1? Thanks a lot for your response, Clemens > On Unix-based systems the alias 'localhost' implies the local Unix socket, > not a networking port. The important part to notice is that you created a > user from "@%" but the error message said "@localhost". As that is a local > socket, the pattern matching algorithm applied to the % to compare the > incoming address (the source of the networked connection) to the account > fails. Therefore it does not match to @localhost as the network was not > involved. > > There is a logic behind this method of operation. Users with physical access > to the machine (or remote access through tunneling protocols like ssh) are > local to the files and processes themselves. The security exposure for this > type of user means that this is most likely a very privileged person and > they probably need to be allowed privileges for full administrative actions. > Therefore a local MySQL user (coming in through the local Unix socket) may > be assigned very different permissions than a user who happens to know the > administrative account's password but is only allowed to login remotely (via > the network). By keeping @localhost separate from @, > we allow you (the DBA) to deny privileged access to any other user that > cannot login directly from the host machine. > > Hopefully, this clarifies why your localhost account was unable to login. > > Additional reading: > http://dev.mysql.com/doc/refman/5.5/en/connection-access.html > > -- > Shawn Green > MySQL Principal Technical Support Engineer > Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. > Office: Blountville, TN > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Can't connect as non-root user to database
Hi Claudio, > you probably have the anonymous user account taking over: ''@'localhost' > login as root and: > mysql> drop user ''@'localhost'; Thanks a lot, that solved the problem (and saved my day :) !). > when you specify the host with -h you are actually forcing MySQL to use > TCP/IP so it will authenticate you using your ip address (127.0.0.1) Actually even with -h127.0.0.1 specified, I got "access denied for ...@localhost". Thanks again, Clemens -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Can't connect as non-root user to database
Hi Rik, > Hm, is the mysql-client library the same as the mysql-server? Yes. > And does mysql --host=127.0.0.1 --user=someone -p somedb work (or it's actual > IP-address, forcing the TCP/IP connect instead of possible sockets) ? This is really strange - with -h127.0.0.1 I get the same error: ERROR 1045 (28000): Access denied for user 'someone'@'localhost' (using password: YES) However with -h192.168.1.102 everything works as expected, although I used 'someone'@'%' everywhere. Does '%' not include local connections / unix domain sockets? Thanks, Clemens -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Can't connect as non-root user to database
Hi Rik, > Have you tried the _full_ user-identification (which is with host)? > > GRANT ALL ON somedb.* TO 'someone'@'%'; Yes I had (and just tried to verify) but it didn't help. Could it be Fedora's mysql packages are special somehow? Re-installing mysql after deletig its data-directory didn't help unfourtunatly. I also don't use fancy configuration, all I did was to copy my-medium.cnf to /etc/my.cnf and enabled innodb. Thanks, Clemens -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Can't connect as non-root user to database
Hi, All I would like to do is the create a small database with a non-root user which is allowed to access the db, however after hours of trying I gave up. I am using MySQL-5.5.20 on Fedora16 . CREATE USER 'someone'@'%' IDENTIFIED BY 'somepass'; CREATE DATABASE somedb; GRANT ALL ON somedb.* TO 'someone' IDENTIFIED BY 'somepass'; FLUSH PRIVILEGES; However, when I try to log in to somedb using someone, I always get: mysql --user=someone -p somedb Enter password: ERROR 1045 (28000): Access denied for user 'someone'@'localhost' (using password: YES) Any idea whats going wrong here? Connecting with mysql-workbench seems to work, although I don't see somedb in the list of databases. Thanks in advance, Clemens -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql