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
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. Oops, didn't use reply-to-list instead of reply-to-author, my apologies... 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. Hm, is the mysql-client library the same as the mysql-server? 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) ? -- Rik Wasmus -- 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, 1. FLUSH PRIVILEGES is not needed, the SQL commands to manage user and grants reload automatically the grant tables, that was used in the very past when people use to tinker directly the grant tables. 2. you did not specify the @ part of the 'someone' : GRANT ALL ON somedb.* TO 'someone'@'.' IDENTIFIED BY 'somepass'; cheers Claudio 2012/3/16 Clemens Eisserer linuxhi...@gmail.com 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 -- Claudio
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
you probably have the anonymous user account taking over: ''@'localhost' 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) login as root and: mysql drop user ''@'localhost'; and try again Cheers Claudio 2012/3/16 Clemens Eisserer linuxhi...@gmail.com 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 -- Claudio
Re: Can't connect as non-root user to database
If you want to verify it is very easy: $ mysql --user=someone somedb (without -p) mysql select user(); select current_user(); cheers Claudio 2012/3/16 Claudio Nanni claudio.na...@gmail.com you probably have the anonymous user account taking over: ''@'localhost' 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) login as root and: mysql drop user ''@'localhost'; and try again Cheers Claudio 2012/3/16 Clemens Eisserer linuxhi...@gmail.com 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 -- Claudio -- Claudio
Re: Can't connect as non-root user to database
Hm, is the mysql-client library the same as the mysql-server? Yes. Aight... 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? Hm, is a configuration file loaded? A ~/.my.cnf or something? And is there a socket defined there? See also http://dev.mysql.com/doc/refman/5.1/en/option-files.html, and check any of the mentioned files for 'socket' definitions: File Name Purpose /etc/my.cnf Global options /etc/mysql/my.cnf Global options (as of MySQL 5.1.15) SYSCONFDIR/my.cnf Global options $MYSQL_HOME/my.cnf Server-specific options defaults-extra-file The file specified with --defaults-extra-file=path, if any ~/.my.cnf User-specific options -- Rik Wasmus -- 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
On 3/16/2012 7:00 AM, Clemens Eisserer wrote: 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 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 @host matching patterns, 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
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 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 @host matching patterns, 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 Clemens, my pleasure! I forgot, you had to use also -P3306, so using both -h and -P which deny the lookup for users at localhost, forcing TCP-IP. and so IPs. this is also good when the socket file is not in the standard location, you will have the same problem logging in locally, using -h and -P forces TCP-IP Cheers! Claudio 2012/3/16 Clemens Eisserer linuxhi...@gmail.com 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 -- Claudio
Re: Can't connect as non-root user to database
On 3/16/2012 2:41 PM, Clemens Eisserer wrote: 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 Because there is also an entry in your hosts file that equates the address 127.0.0.1 to the alias 'localhost'. The MySQL permissions checks looks for the the most specific match, first. Host matches are more specific than user matches as demonstrated in the examples here: http://dev.mysql.com/doc/refman/5.5/en/connection-access.html Once you eliminated the anonymous @localhost user, the system was able to check other possible matches, in order of specificity, to see if your login attempt qualified for access. Because the socket-based direct match of someone@localhost failed, the system progressed to the network-based match of someone@% where the alias was able to resolve your address back from 127.0.0.1 into 'localhost' which matched your pattern and granted you access. Any previous improper behavior in older versions was most likely corrected when we fixed several bugs related to the IPv6 loopback address. Details are covered in the changelog to version 5.5.3 and the specific bug reports linked from within it. http://dev.mysql.com/doc/refman/5.5/en/news-5-5-3.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
