MySQL Lists are EOL. Please join:

List:MySQL and Perl« Previous MessageNext Message »
From:Michael Stassen Date:June 24 2005 5:39am
Subject:Re: DBD::mysql anonymous user problem
View as plain text  
Dave Howorth wrote:

> 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?

It's not hard to verify.  I'll explain below.

> 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?

By whom?  (not that I'm disagreeing)

> You say 'mysql will use defaults'. Do you mean mysql or the Perl 
> interface? 

As DBD::mysql and the mysql client behave the same way, in my experience, I 
assume this is built into mysql.

> 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

How so?

As you know, mysql users are a combination of username and host.  If I attempt 
to connect to a mysql server on the same machine without explicitly specifying 
a mysql user, my unix username is used, so I effectively announce myself to 
mysql as 'stassenm@localhost'.  Mysql then searches the user table for a 
match.  There may be more than one match, so mysql has to decide which one to 
use.  The following entries would match, in decreasing order of preference:

   1)  stassenm@localhost
   2)  ''@localhost         <-- the anonymous user
   3)  stassenm@%

Hence, I'll be stassenm@localhost if entry #1 exists.  If entry #1 does not 
exist, then I'll be the anonymous user, entry #2.

(Number 2 catches a lot of people by surprise.  Matching the host takes 
precedence over matching the username, so right host with wildcard user beats 
right user with wildcard host.)

This is why I am certain the manual paragraph you quoted is wrong.  It is 
written based on the assumption that you have just installed mysql for the 
first time and have not yet modified the default mysql users in any way.  It 
also assumes you are installing from source by hand, rather than using CPAN 
(or CPANPLUS).

For example, building from source by hand, I download the source, 
DBD-mysql-2.9008.tar.gz, unpack it, and run `perl Makefile.pl` with no flags. 
  I see these lines in the output

| I will use the following settings for compiling and testing:
|
...
|   testdb        (default     ) = test
|   testhost      (default     ) =
|   testpassword  (default     ) =
|   testsocket    (default     ) =
|   testuser      (default     ) =
|
| To change these settings, see 'perl Makefile.PL --help' and
| 'perldoc INSTALL'.

As you can see, testuser and testpassword are unset.  When I run `make test`, 
I get

   DBI connect('test','',...) failed: Access denied for user:
   'stassenm@localhost' (Using password: NO) at t/50chopblanks.t line 57
   Cannot connect: Access denied for user: 'stassenm@localhost' (Using
   password: NO)

which is the same as you got, only with my username instead of root.  If I 
switch to root and run the test (or do it from CPAN), the error message says 
'root@localhost', just as you got.

You see?  This would have worked on a brand new install of mysql.  Done 
manually, the attempt to connect as stassenm@localhost would have gotten me in 
as ''@localhost, the anonymous user, who doesn't have a password and has 
rights to the test db.  Even installing via CPAN would work, because while 
root@localhost exists, it doesn't yet have a password.

Of course, we take security seriously, so we don't leave mysql like this.  We 
assign a password to root@localhost, which breaks installation via CPAN.  We 
probably get rid of the anonymous user, also for security, and we create 
ourselves an account using our unix usernames, taking advantage of the 
default.  Now the manual source install is broken.

Well, not broken really.  You just have to set an actual testuser and 
testpassword that will give access to the test db, so `make test` can do its 
thing.  One option is to do precisely what you started to do -- create a 
test@localhost account in mysql with a password and access to the test db and 
nothing else.  Then, when building by hand, you use the appropriate command 
line switches

   perl Makefile.PL --testuser=test@localhost --testpassword=<pwd>

and everything should work.

I've not found a simple way to set those switches when building via CPAN.

> 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 had nothing to do with setting it up, so I can't say.  I would guess that 
the DBD::mysql build process was designed with manual install rather than CPAN 
install in mind.  If you are building manually, the assumptions don't really 
matter, as setting a correct test user and password using the provided command 
line options is much, much easier than the developers trying to guess what 
user and password combo will work.

I don't think making the tests explicitly default to the anonymous user would 
help much.  The anonymous user is a security hole.  I think most of us delete 
it right away.

> 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?

The anonymous user is described in several places in the manual.  I'd start 
with <http://dev.mysql.com/doc/mysql/en/default-privileges.html>.  Note that 
page recommends "If you want to prevent clients from connecting as anonymous 
users  without a password, you should either assign passwords to the 
anonymous accounts or else remove them."  You can search the manual on 
'anonymous' to see all the references.

<snip>
> 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.

The tests must be able to write to the test db.  A relatively trivial denial 
of service attack would be to connect to your mysql server as the default test 
user and write to the test db till your disk was full.  Having a standardized 
test user and password known to all, with the assumption that it would remain 
in place for testing, would be a big security problem.  What we really need is 
a simpler way to specify the test user and password to use for testing, one 
that works in CPAN as well.  Perhaps a prompt...

> 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.

Looking at that, I don't think I was clear.  I don't mean to override CPAN.  I 
mean you need to set the test user and password, rather than accepting the 
default of undef.

> 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.

Well, as I said, a standard test user is a bad idea.  What we need is a 
standard way of supplying those credentials that works in CPAN.

> (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.

Yes, a prompt would be ideal, perhaps at the beginning of the test run.

<snip>

>> 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.

Agreed.  I have to look up how I did this last time every time I install a new 
version.  It's a pain.

>> 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.

Same here.

> Cheers, Dave

Michael

Thread
DBD::mysql anonymous user problemDave Howorth21 Jun
  • Re: DBD::mysql anonymous user problemRudy Lippan21 Jun
    • Re: DBD::mysql anonymous user problemDave Howorth21 Jun
Re: DBD::mysql anonymous user problemDave Howorth21 Jun
  • Re: DBD::mysql anonymous user problemMichael Stassen21 Jun
    • Re: DBD::mysql anonymous user problemDave Howorth23 Jun