inappropriate authentication behavior after creating new user

[Tom Roche]

I expect that a new user, on which I have SET PASSWORD, would

+ be able to login to `mysql` and `mysqladmin` with its password
+ be able to use a MyODBC connection with its password
+ not be able to login to mysql/mysqladmin without its password

Instead I find (below) that the new user

+ is able to use a MyODBC connection with its password
- is not able to login to mysql/mysqladmin with its password
- is able to login to mysql/mysqladmin without any password

Please help me to obtain the desired functionality. Details:

I am new to administering MySQL: please forgive me if this is a FAQ,
but I haven't seen anything about this in the user-setup documents. If
I need to RTFM, please pass appropriate URIs.

I recently did an install of Ubuntu Server Edition, which created a
fresh install of 5.0.22-Debian_0ubuntu6.06.3-log. After that I
installed a FOSS point-of-sale system: it's not well documented, but I
do have access to its code, config files, etc. I need to setup remote
connections for a group to start doing data entry, and I don't want
everyone using user=root, certainly with null password :-( So I tried
to create a new user in `mysql` via an SSH shell from my win32 laptop:

> $ date ; ssh USER@HOST
> Mon Jan 14 16:31:50 EST 2008
> ...

> USER@HOST:~$ date ; mysql -u root -p
> Mon Jan 14 16:31:42 EST 2008
> Enter password: <null/>
> Welcome to the MySQL monitor. Commands end with ; or \g.
> ...

> mysql> SELECT User,Host,Password FROM mysql.user;
> +------------------+-----------+------------------+
> | User | Host | Password |
> +------------------+-----------+------------------+
> | root | localhost | |
> | root | USER | |
> | debian-sys-maint | localhost | 231995284c2c7308 |
> | | localhost | |
> | is4clane | % | |
> | is4cserver | localhost | |
> | root | % | |
> +------------------+-----------+------------------+

Accounts=is4c* were created by the POS.

> mysql> CREATE USER 'administrator';
> Query OK, 0 rows affected (0.00 sec)

> mysql> SET PASSWORD FOR 'administrator'@'%' = PASSWORD('NewPassword');
> Query OK, 0 rows affected (0.00 sec)

> mysql> GRANT ALL PRIVILEGES ON *.* TO 'administrator'@'%' IDENTIFIED BY 'NewPassword' WITH GRANT OPTION;
> Query OK, 0 rows affected (0.00 sec)

I want this account to have root-like account privileges, but not to
be root itself.

> mysql> FLUSH PRIVILEGES;
> Query OK, 0 rows affected (0.00 sec)

> mysql> SELECT User,Host,Password FROM mysql.user;
> +------------------+-----------+------------------+
> | User | Host | Password |
> +------------------+-----------+------------------+
> | root | localhost | |
> | root | USER | |
> | debian-sys-maint | localhost | 231995284c2c7308 |
> | | localhost | |
> | is4clane | % | |
> | is4cserver | localhost | |
> | root | % | |
> | administrator | % | 1afc4f476a97e6ca |
> +------------------+-----------+------------------+

> mysql> exit
> Bye

Then I tried to use the account:

> USER@HOST:~$ date ; mysql -u administrator -p
> Mon Jan 14 16:37:33 EST 2008
> Enter password: NewPassword
> ERROR 1045 (28000): Access denied for user 'administrator'@'localhost' (using password: YES)

> USER@HOST:~$ date ; mysql -u root -p
> Mon Jan 14 16:37:48 EST 2008
> Enter password: <null/>
> Welcome to the MySQL monitor. Commands end with ; or \g.
> ...

> mysql> exit
> Bye

> USER@HOST:~$ date ; mysql -u administrator # NO PASSWORD!
> Mon Jan 14 16:37:56 EST 2008
> Welcome to the MySQL monitor. Commands end with ; or \g.
> ...

> mysql> exit
> Bye

> USER@HOST:~$ date ; mysqladmin -u administrator -p version ; date
> Mon Jan 14 16:45:20 EST 2008
> Enter password: NewPassword
> mysqladmin: connect to server at 'localhost' failed
> error: 'Access denied for user 'administrator'@'localhost' (using password: YES)'
> Mon Jan 14 16:45:26 EST 2008

> USER@HOST:~$ date ; mysqladmin -u root -p version ; date
> Mon Jan 14 16:45:45 EST 2008
> Enter password: <null/>
> mysqladmin Ver 8.41 Distrib 5.0.22, for pc-linux-gnu on i486
> Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
> This software comes with ABSOLUTELY NO WARRANTY. This is free software,
> and you are welcome to modify and redistribute it under the GPL license

> Server version 5.0.22-Debian_0ubuntu6.06.3-log
> Protocol version 10
> Connection Localhost via UNIX socket
> UNIX socket /var/run/mysqld/mysqld.sock
> Uptime: 2 days 17 hours 50 min 59 sec

> Threads: 2 Questions: 301 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 40 Queries per second avg: 0.001
> Mon Jan 14 16:45:46 EST 2008

> USER@HOST:~$ date ; mysqladmin -u administrator version ; date
> Mon Jan 14 16:48:03 EST 2008
> mysqladmin Ver 8.41 Distrib 5.0.22, for pc-linux-gnu on i486
> ...
> Mon Jan 14 16:48:04 EST 2008

Subsequently I setup an MyODBC connection from my laptop over an SSH
tunnel, and was able to connect with {id=administrator,
pw=NewPassword} using that connection and OpenOffice Base.

I am now confused ... not to mention more than a little troubled.
Having created a new user (as `root`, i.e. the default user) *with a
password*,

* I cannot login to `mysql` and `mysqladmin` as that user using the
set password

* I can login to `mysql` and `mysqladmin` as that user WITHOUT using a
password

* I can connect to the server via ODBC as that user using the set
password

This behavior seems wrong to me: having created the user, I should be
able to login to any associated application (e.g. connector, `mysql`,
`mysqladmin`) with a password, but not without a password. Am I
missing something?

If this is non-default behavior, and you feel the POS must have
changed MySQL's configuration, please point me toward, e.g., files to
examine, or tools to use to check MySQL's configuration. I would also
appreciate pointers to tools and documentation for setting up new
users "the right way."

TIA, Tom Roche <[e-mail]>