Michael Stassen wrote:
> Dave Howorth wrote:
> > I'm trying to upgrade DBD::mysql and having trouble with the anonymous
> > user for the test database. When I run the install the tests fail with
> > messages like:
> >
> > DBI connect('test','',...) failed: Access denied for user:
> > 'root@localhost' (Using password: NO) at t/50chopblanks.t line 57
> > Cannot connect: Access denied for user: 'root@localhost' (Using
> > password: NO)
>
> Look at the error message. The tests are attempting to log into mysql
> as user root@localhost with no password.
Hi Michael,
OK, I'm with you so far ...
> <snip>
> > According to the mysql manual:
> >
> > "The DBD::mysql installation runs a number of tests. These tests require
> > being able to connect to the local MySQL server as the anonymous user
> > with no password. If you have removed anonymous accounts or assigned
> > them passwords, the tests fail."
>
> This is from <http://dev.mysql.com/doc/mysql/en/perl-installation.html>.
>
> The manual is wrong.
Hmm. This is a pretty bold statement and I have no way to judge whether
you're right or not. Can anybody else confirm this?
In the meantime, let's assume you're right and carry on ...
> By default, the tests do not specify the user or
> password. With neither specified, mysql will use defaults. If the
> current unix user (when using CPAN, this is usually root, as in your
> case) exists as user@localhost in mysql, that is the default. If
> user@localhost does not exist, then the anonymous user, ''@localhost, is
> the next option, if it exists.
This sounds like it could be handled better, then?
You say 'mysql will use defaults'. Do you mean mysql or the Perl
interface? Because your description of the mechanism doesn't seem to
match that given in 5.6.5. Access Control, Stage 1: Connection
Verification at http://dev.mysql.com/doc/mysql/en/connection-access.html
But again, assuming your description is right ...
As I understand it, mysql creates its 'root' user when it's installed
and as you say the DBD::mysql install will almost certainly be run as
root. So trying to connect as root@localhost will be the normal use case
for the tests? And 'good doobies' will have secured that account as the
mysql manual suggests. Particularly if this is an upgrade of DBD::mysql
rather than an initial install. Why isn't it set up to use the anonymous
user for the tests, which would match mysql's default configuration?
I find the anonymous user baffling, because I haven't been able to find
any documentation apart from the mysql manual, which doesn't describe
the Perl syntax. There's no mention in DBI (but then again it's
mysql-specific, yes?). There's also no mention in DBD::mysql, which
surprises me. There's no index entry in the couple of books I have. I
didn't find anything useful with Google. Is there a good description
anywhere? What is the syntax to establish a connection as an anonymous user?
> > I haven't changed the anonymous user accounts AFAIK. I did add some
> > extra accounts. My mysql.user table looks like this:
> >
> > mysql> select host,user,password,select_priv from user;
> > +--------------------+----------------+------------------+-------------+
> > | host | user | password | select_priv |
> > +--------------------+----------------+------------------+-------------+
> > | localhost | root | 5b3e0551192fc0ec | Y |
> > | suse3.lmb.internal | root | 5b3e0551192fc0ec | Y |
> > | localhost | | | N |
> > | suse3.lmb.internal | | | N |
> > | % | test | 378b243e220ca493 | N |
> > | % | test@localhost | 378b243e220ca493 | N |
> > | localhost | test | 378b243e220ca493 | N |
> > +--------------------+----------------+------------------+-------------+
> > 7 rows in set (0.00 sec)
>
> The next to last row, user = test@localhost, host = %, makes no sense
> and should probably be dropped. I'd also recommend reconsidering
> whether you really need test@%, from a security standpoint. If you do
> need an external test user, you may still be able to restrict the hosts
> from which test can connect to something less than every machine in the
> world. Also, as you've gone to the trouble of creating a test user, you
> should consider whether you really need the anonymous user anymore. I
> think most of us delete the anonymous user accounts at the same time as
> we give root a password, again for security reasons. Passwordless
> access to mysql just doesn't seem safe.
Thanks for this advice. The test@localhost line is an error; I created
it by accident but wanted to show the complete environment that was in
place at the time of the problem I was reporting. I also agree about
deleting test@% - I was just setting up all possible combinations to get
the @*%$ thing to run :)
The only reason I created the test user was because the tests didn't
seem to run with the anonymous user as the docs say they should.
Personally, I think it would all be much clearer if mysql automatically
set up a 'test' database with a 'test' user with a 'test' password and
if DBD::mysql (and every other Perl module!) used that by default.
But I just want to end up with a standard installation where whoever
maintains this system after I move on will be able to leverage their
knowledge and not have to deal with special cases (or bother with
reading my documentation, if I'm an extra good doobie :)
> > There's also a user comment on the mysql manual:
> >
> > "Note that if you've been a good doobie and already changed the root
> > password then the DBD::mysql tests will fail."
>
> This is accurate, but fixable. You run CPAN as root (needed for the
> install part), so the tests default to connecting as root@localhost.
> You need to override that default to make the tests work.
I'd prefer not to do that for two reasons:
(1) It makes it non-standard and I don't like that as above. The tests
ought to work out of the box, IMHO.
(2) There doesn't seem to be any way to reconfigure the tests without
breaking the File Hierarchy Standard on a SuSE system anyway. Template
Toolkit, for example stops and asks me what user to use and writes its
own private test configuration file. That's OK.
> > This fits my circumstances; I have indeed given root a password and a
> > .my.cnf file. I don't want to take it away again - it seems pretty
> > tedious if I have to change root's password every time I want to upgrade
> > modules that use mysql.
> >
> > Is the user comment correct and if so, is there anything I can do to
> > make test installation work as normal? I don't understand why changing
> > root's password affects the anonymous user.
>
> It doesn't, but you aren't using the anonymous user.
>
> <snip>
>
> Dave Howorth also wrote:
>
>> Jochen Wiedmann wrote:
>>
>>> On 6/21/05, Dave Howorth <dhoworth@stripped> wrote:
>>>
>>>> No. I should have mentioned that I'm using CPANPLUS for the install so
>>>> there's no opportunity to interact. If I run the tests manually, I can
>>>> edit mysql.mtest to the user 'test' that I added, but I don't
>>>> understand
>>>> why the default CPAN installation isn't working. I thought it was all
>>>> supposed to default to the anonymous user?
>>>
>>>
>>> There is a possibility to interact, which was designed spefically with
>>> CPAN installations in mind: Write your own "mysqlconfig". Should be
>>> documented in the INSTALL section.
>>
>>
>> Well after having cpan run and then going back and investigating what
>> went wrong, you can change something. But mysql_config is already
>> installed in /usr/bin by SuSE so that's not really something that
>> should be edited by a user. There should be something elsewhere in a
>> user-alterable part of the file system, which is why I bodged it by
>> editing mysql.mtest.
>
>
> I agree. Editing mysql_config to fix this problem isn't a very good
> idea. It's overkill, and it's a maintenance and security headache.
! :)
>> But my main issue is that none of this should be necessary. It should
>> just install automatically using the default mysql anonymous accounts.
>> Why is that not working?
>
>
> DBD::mysql has always been a pain to install via CPAN. If you install
> it manually, you are prompted for a valid mysql user and password to use
> to access the test db, but CPAN doesn't prompt you, leaving those
> options set to their defaults (undef). Fortunately, the defaults can be
> altered via command line switches to `perl Makefile.PL`, and there is a
> way to set those in CPAN. First, you need to enter
>
> cpan> o conf makepl_arg "--testuser=test --testpass=passwd"
>
> (substituting the real password for test@localhost in place of
> "paswd"). Then enter
>
> cpan> install DBD::mysql
>
> Now the test scripts should connect as test@localhost using the correct
> password, so you should get valid test results.
OK, I guess this is as good a workaround as I've got. But it's magic.
Whoever does the install/upgrade has to know in advance about the
problem and remember the incantation (or look it up in the book of
shadows). At least the way TT does it there's a prompt to alert you that
there might be a problem.
> I haven't used CPANPLUS much yet, so I'm not sure how to accomplish this
> there.
I try to use it every now and again, because it's the future and because
it automatically sends install reports which make me feel like I'm
helping. But every time I try it, it eventually bites me and I go back
to cpan. It does seem to be getting better slowly. Purely my personal
top of the head opinion, YMMV.
Cheers, Dave
