newb probs w/ user accts

[notbob]

Newb here! Using 4.0.20 on Slack. Slogging through the official
manual. At 2.4.3 Securing the Initial MySQL Accounts, I'm finally
stopped cold while trying to follow instructions. Here's what I did:

shell> mysql -u root
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpw d');

.....as per instructed (I just cut 'n paste). I then quit mysql and
log back on as root:

mysql -u root

.....expecting a passwd prompt. Nothing. I just go in as root, as
confirmed by:

mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+

So, about syntax, are all those single quotes suppose to be in the
actual command? Also, since I've done this (and nothing else), I can
no longer log in as anonymous. I get:

notbob ~>mysql
ERROR 1045: Access denied for user: 'notbob@localho st' (Using
password: NO)

Everything was going along so well. What happened?

nb

[Bill Karwin]

Re: newb probs w/ user accts

notbob wrote:[color=blue]
> mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpw d');
>
> ....as per instructed (I just cut 'n paste). I then quit mysql and
> log back on as root:
>
> mysql -u root
>
> ....expecting a passwd prompt. Nothing.[/color]

You won't see a password prompt unless you also use the -p option. The
same username can be configured to log in using a password or without
using a password.

This is done so that you could, for example, configure a different set
of privileges for the same user, depending on whether they provide the
password, or log in without giving a password. You could even have the
same user log in with one of several passwords, and grant different
privileges depending on which password they use.

MySQL has great flexibility with their privilege system. So flexible
that it's probably very confusing to keep track of all the
configurations one might set up.
[color=blue]
> So, about syntax, are all those single quotes suppose to be in the
> actual command?[/color]

Yes, they are.
[color=blue]
> notbob ~>mysql
> ERROR 1045: Access denied for user: 'notbob@localho st' (Using
> password: NO)[/color]

Again, you need to use the -p option to let mysql know that you are
attempting to log in using a password.

Without the -p option, you have told it to give you the privileges
granted to the anonymous login when no password is given. That's a
legitimate situation, since you might have deliberately granted a
special set of limited privileges in that case.
For example:
$ mysql -u root
mysql> grant all on test.* to ''@'localhost' identified by '';
mysql> quit;
$ mysql test
(no denial of connection, and no password prompt)

But since you changed the anonymous user's password to 'newpwd', and
didn't issue a specific grant to the anonymous user when not using a
password, this has effectively denied all access when you don't use a
password. No connection will work when not specifying a password.
That's probably what is desired, according to the MySQL setup
instructions -- to disable all non-passworded access, for all databases.

So try logging in thus:

mysql -p

Then type your newpwd at the prompt.

Regards,
Bill K.