Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Not a Bug
-
Affects Version/s: 5.5.25-galera
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
-
Environment:Mageia Linux
Description
This is new instalation, no update
MariaDB [(none)]> create user dbuser identified by 'mysql';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant ALL privileges on . to 'dbuser'@'%';
Query OK, 0 rows affected (0.00 sec)
Then from the shell:
$ mysql -p -u dbuser
Enter password:
ERROR 1045 (28000): Access denied for user 'dbuser'@'localhost' (using password: YES)
I even created a valid Linux user with the name dbuser before adding it to database.
Log file is useless:
121122 09:49:11 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
121122 9:49:11 InnoDB: The InnoDB memory heap is disabled
121122 9:49:11 InnoDB: Mutexes and rw_locks use GCC atomic builtins
121122 9:49:11 InnoDB: Compressed tables use zlib 1.2.6
121122 9:49:11 InnoDB: Using Linux native AIO
121122 9:49:11 InnoDB: Initializing buffer pool, size = 128.0M
121122 9:49:11 InnoDB: Completed initialization of buffer pool
121122 9:49:11 InnoDB: highest supported file format is Barracuda.
121122 9:49:12 InnoDB: Waiting for the background threads to start
121122 9:49:13 Percona XtraDB (http://www.percona.com) 1.1.8-26.0 started; log sequence number 1776818
121122 9:49:13 [Note] Plugin 'PBXT' is disabled.
121122 9:49:13 [Note] Event Scheduler: Loaded 0 events
121122 9:49:13 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.25-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 Mageia - MariaDB Community Edition (GPL)
Nothing else 
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
It looks like you have an anonymous user ''@'localhost' in your system. Since your newly created account doesn't have a specified host, the anonymous user with the host takes precedence. Please check MySQL documentation for more details on user authentication.
Either remove the anonymous user (it's recommended for security reasons anyway), or specify your new one as 'dbuser'@'localhost'. Any of these should resolve the problem.
Hi Sergei & Elena.
Thank you very much for your answer. However I respectfully disagree with your assesment because this is an obvious behavioral bug.
Let assume you have an UNIX system, at one point I am adding the anonymous user for anonymous FTP and as a result anyone's
account starting with AO and beyond stops working. It just does not makes any sense at all.
If this behaviour is expected based on the design then even if is not a codding bug it is a design bug.
What would make sense is for the lookup to be based on user name and in the case that the same user name is specified with different hosts then the host shall take the second priority for security reasons. However, having a total different user name faulting somebody else it is still a bug, regardless if is specified in manual as a feature 
Thank you again,
MTM
Hi Sergei & Elena.
Thank you very much for your answer. However I respectfully disagree with your assesment because this is an obvious behavioral bug.
Let assume you have an UNIX system, at one point I am adding the anonymous user for anonymous FTP and as a result anyone's
account starting with AO and beyond stops working. It just does not makes any sense at all.
If this behaviour is expected based on the design then even if is not a codding bug it is a design bug.
What would make sense is for the lookup to be based on user name and in the case that the same user name is specified with different hosts then the host shall take the second priority for security reasons. However, having a total different user name faulting somebody else it is still a bug, regardless if is specified in manual as a feature
Thank you again,
MTM
I agree with that to an extent. In particular, the manual calls it "a very commont mistake" for a reason - people do it often, indeed. And I'd say that it's better to change the behavior to make this very common mistake impossible, instead of trying to document it.
But this is the behavior that MySQL had, like, forever. Since 1998, or earlier. Changing it would be a big incompatible change, we need to approach it carefully.
This is expected behavior. It is explained in the manual, as a "very common mistake".
You are connecting as dbuser@localhost. That is, you can see, that access is denied to dbuser@localhost. This happens, because you have the anonymous @localhost user, and MariaDB (and MySQL) sort accounts with a known host name before accounts with a wildcard (%) host name. And when you connect as dbuser@localhost it matches @localhost before it has a chance to match dbuser@%.
You need either to create a user dbuser@localhost, or remove the anonymous @localhost user.