user authentication weirdness

[Ted]

On our development machine, running MySQL 5.0.19, I created a database
alert_db, and for development purposes created a user called alert_db
with a password alert_db (obviously this uid/pwd will not exist on the
deployment machine).

I can connect to this db using this uid/pwd combination when using
MySQL's Query Browser, as well as some of my Perl scripts. However, my
colleague is presently sitting at the development machine running
scripts, and he received the following error message:

Loading data into the temporary tables failed now, at Tue Jul 4
10:28:50 2006,
with the following error:
ERROR 1045 (28000) at line 6: Access denied for user 'alert_db'@'%'
(using passw
ord: YES)

The relevant portion of my script is:

my $cmdline = "mysql --user=alert_db --password=alert_db
--database=alert_db < temporary_load_script_file.sql 3>&1 1>&2 2>&3";
my $rv = qx/$cmdline/;
$now_string = localtime;
if (length($rv) == 0) {
print "The data has been loaded into the temporary tables now,
$now_string.\n";
print log_file "The data has been loaded into the temporary tables
now, $now_string.\n";
} else {
print "Loading data into the temporary tables failed now, at
$now_string, with the following error:\n$rv";
print log_file "Loading data into the temporary tables failed now, at
$now_string, with the following error:\n$rv";
exit;
}

I do not understand why a given uid/pwd combination would work when
using MySQL's Query Browser but not with this script. It is especially
odd since there is another perl script that, after a bit of
preprocessing, runs a couple SQL scripts that create the tables in this
DB, using the same procedure to run the scripts and using the same
uid/pwd, and it runs to completion fine. All this code works fine if
the script is executed using the root uid/pwd.

Any thought or ideas on why this is happening and how I can fix it?

Thanks

Ted

[Jerry Stuckle]

Ted wrote:

> On our development machine, running MySQL 5.0.19, I created a database
> alert_db, and for development purposes created a user called alert_db
> with a password alert_db (obviously this uid/pwd will not exist on the
> deployment machine).
>
> I can connect to this db using this uid/pwd combination when using
> MySQL's Query Browser, as well as some of my Perl scripts. However, my
> colleague is presently sitting at the development machine running
> scripts, and he received the following error message:
>
> Loading data into the temporary tables failed now, at Tue Jul 4
> 10:28:50 2006,
> with the following error:
> ERROR 1045 (28000) at line 6: Access denied for user 'alert_db'@'%'
> (using passw
> ord: YES)
>
> The relevant portion of my script is:
>
> my $cmdline = "mysql --user=alert_db --password=alert_db
> --database=alert_db < temporary_load_script_file.sql 3>&1 1>&2 2>&3";
> my $rv = qx/$cmdline/;
> $now_string = localtime;
> if (length($rv) == 0) {
> print "The data has been loaded into the temporary tables now,
> $now_string.\n";
> print log_file "The data has been loaded into the temporary tables
> now, $now_string.\n";
> } else {
> print "Loading data into the temporary tables failed now, at
> $now_string, with the following error:\n$rv";
> print log_file "Loading data into the temporary tables failed now, at
> $now_string, with the following error:\n$rv";
> exit;
> }
>
> I do not understand why a given uid/pwd combination would work when
> using MySQL's Query Browser but not with this script. It is especially
> odd since there is another perl script that, after a bit of
> preprocessing, runs a couple SQL scripts that create the tables in this
> DB, using the same procedure to run the scripts and using the same
> uid/pwd, and it runs to completion fine. All this code works fine if
> the script is executed using the root uid/pwd.
>
> Any thought or ideas on why this is happening and how I can fix it?
>
> Thanks
>
> Ted
>

What host is the userid alert_db authorized to connect from? localhost only, or any host?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[email]jstucklex@attglobal.net[/email]
==================

[Ted]

Jerry Stuckle wrote:

> Ted wrote:

> > [snip]

>
> What host is the userid alert_db authorized to connect from? localhost only, or any host?
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> [email]jstucklex@attglobal.net[/email]
> ==================

Hi Jerry,

Now that you mention it, I did notice, when I dropped by the office
today, that MySQL Query Browser displayed the uid as alert_db@localhost
which I expect is different from alert_db@% (if I am not mistaken, I
expect % is a wildcard in the latter so it includes any host including
localhost).

At this stage, it has not been determined whether the end user will be
connecting using a client on their own machine, or through a web
application that connects to the DB only through localhost.

I'd bet dollars to donuts that this is the issue. However, I have not
found anything in MySQL Administrator anything that looks like it
allows me to specify where a given user is permitted to connect from.
Maybe I'm looking in the wrong places. Can I use MySQL Administrator
to specify that a given user is allowed to connect from any host? If
so, where will I find the control(s) to do so?

Thanks,

Ted

[Jerry Stuckle]

Ted wrote:

> Jerry Stuckle wrote:
>

>>Ted wrote:
>>

>>>[snip]

>>
>>What host is the userid alert_db authorized to connect from? localhost only, or any host?
>>
>>--
>>==================
>>Remove the "x" from my email address
>>Jerry Stuckle
>>JDS Computer Training Corp.
>>jstucklex@attglobal.net
>>==================

>
> Hi Jerry,
>
> Now that you mention it, I did notice, when I dropped by the office
> today, that MySQL Query Browser displayed the uid as alert_db@localhost
> which I expect is different from alert_db@% (if I am not mistaken, I
> expect % is a wildcard in the latter so it includes any host including
> localhost).
>
> At this stage, it has not been determined whether the end user will be
> connecting using a client on their own machine, or through a web
> application that connects to the DB only through localhost.
>
> I'd bet dollars to donuts that this is the issue. However, I have not
> found anything in MySQL Administrator anything that looks like it
> allows me to specify where a given user is permitted to connect from.
> Maybe I'm looking in the wrong places. Can I use MySQL Administrator
> to specify that a given user is allowed to connect from any host? If
> so, where will I find the control(s) to do so?
>
> Thanks,
>
> Ted
>

Ted,

I don't know how to do it with MySQL Administrator. I don't use it.

But the host is specified as part of the user in the GRANT and REVOKE
commands. The default (if I remember correctly) is localhost, but you
can specify others as 'user@host' And '%' is the wildcard character
for any host.

See [url]http://dev.mysql.com/doc/refman/5.1/en/grant.html[/url] for more info.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[email]jstucklex@attglobal.net[/email]
==================

[Ted]

Jerry Stuckle wrote:

> Ted wrote:

> > Jerry Stuckle wrote:
> >

> >>Ted wrote:
> >>
> >>>[snip]
> >>
> >>What host is the userid alert_db authorized to connect from? localhost only, or any host?
> >>
> >>--
> >>==================
> >>Remove the "x" from my email address
> >>Jerry Stuckle
> >>JDS Computer Training Corp.
> >>jstucklex@attglobal.net
> >>==================

> >
> > Hi Jerry,
> >
> > Now that you mention it, I did notice, when I dropped by the office
> > today, that MySQL Query Browser displayed the uid as alert_db@localhost
> > which I expect is different from alert_db@% (if I am not mistaken, I
> > expect % is a wildcard in the latter so it includes any host including
> > localhost).
> >
> > At this stage, it has not been determined whether the end user will be
> > connecting using a client on their own machine, or through a web
> > application that connects to the DB only through localhost.
> >
> > I'd bet dollars to donuts that this is the issue. However, I have not
> > found anything in MySQL Administrator anything that looks like it
> > allows me to specify where a given user is permitted to connect from.
> > Maybe I'm looking in the wrong places. Can I use MySQL Administrator
> > to specify that a given user is allowed to connect from any host? If
> > so, where will I find the control(s) to do so?
> >
> > Thanks,
> >
> > Ted
> >

>
> Ted,
>
> I don't know how to do it with MySQL Administrator. I don't use it.
>
> But the host is specified as part of the user in the GRANT and REVOKE
> commands. The default (if I remember correctly) is localhost, but you
> can specify others as 'user@host' And '%' is the wildcard character
> for any host.
>
> See [url]http://dev.mysql.com/doc/refman/5.1/en/grant.html[/url] for more info.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> [email]jstucklex@attglobal.net[/email]
> ==================

Thanks Jerry,

That's what I need, but I still don't see anything in MySQL
Administrator that supports this. I guess I'll have to handle this
with a SQL statement in the script that creates the database.

Ted

[Axel Schwenke]

"Ted" <r.ted.byers@rogers.com> wrote:

> On our development machine, running MySQL 5.0.19, I created a database
> alert_db, and for development purposes created a user called alert_db
> with a password alert_db (obviously this uid/pwd will not exist on the
> deployment machine).
>
> I can connect to this db using this uid/pwd combination when using
> MySQL's Query Browser, as well as some of my Perl scripts. However, my
> colleague is presently sitting at the development machine running
> scripts, and he received the following error message:

> ERROR 1045 (28000) at line 6: Access denied for user 'alert_db'@'%'
> (using password: YES)

Ted,

MySQL handles authentication differently for clients on the same host
as the server (localhost) and clients connecting through the network.
Also user@'%' does *not* include user@localhost. This is a common
pitfall.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
MySQL User Forums: [url]http://forums.mysql.com/[/url]

[Ted]

Axel Schwenke wrote:

> "Ted" <r.ted.byers@rogers.com> wrote:
>

> > On our development machine, running MySQL 5.0.19, I created a database
> > alert_db, and for development purposes created a user called alert_db
> > with a password alert_db (obviously this uid/pwd will not exist on the
> > deployment machine).
> >
> > I can connect to this db using this uid/pwd combination when using
> > MySQL's Query Browser, as well as some of my Perl scripts. However, my
> > colleague is presently sitting at the development machine running
> > scripts, and he received the following error message:

>

> > ERROR 1045 (28000) at line 6: Access denied for user 'alert_db'@'%'
> > (using password: YES)

>
> Ted,
>
> MySQL handles authentication differently for clients on the same host
> as the server (localhost) and clients connecting through the network.
> Also user@'%' does *not* include user@localhost. This is a common
> pitfall.
>
>
> XL
> --

Thanks Axel

I appreciate this. I suppose then, for a given user that I want to
allow to connect from anywhere, including the localhost, I need to
create two uids, one being user@% and the other being user@localhost.
I guess, since the user will always be accessing the DB using one of my
applications, I should have the DB know only about my applications,
giving each application its own uid/pwd combination for accessing the
db, and then handle human user authentication using application code.

Thanks

Ted

[Gordon Burditt]

>> MySQL handles authentication differently for clients on the same host

>> as the server (localhost) and clients connecting through the network.
>> Also user@'%' does *not* include user@localhost. This is a common
>> pitfall.

Actually, I believe user@'%' matches any IP address, including the
IP address of the local system and 127.0.0.1 . But 'localhost' is
special and won't match: it indicates that the connection should
be done by unix-domain socket, as distinguished from an inet socket.

You don't have to ever use the local socket (although there is an
efficiency gain using it) even if your app is on the same server
as the db.

>I appreciate this. I suppose then, for a given user that I want to
>allow to connect from anywhere, including the localhost, I need to
>create two uids, one being user@% and the other being user@localhost.

What you create with a MySQL GRANT command is *not* a UNIX uid or
Windows login, and it's unrelated to a UNIX uid or Windows login.
This is important since if you leave root@localhost without a
password, *ANY* user on the box can get in with:
mysql -u root
whether they have root privileges or not.

>I guess, since the user will always be accessing the DB using one of my
>applications, I should have the DB know only about my applications,
>giving each application its own uid/pwd combination for accessing the
>db, and then handle human user authentication using application code.

It's very common to let an application have its own db username/password,
and let it do its own authentication for the app. Some apps don't
usually take that approach, such as phpMyAdmin or other general
database editors. Those, you give it YOUR credentials and let the
database worry about what you're allowed to edit.

Gordon L. Burditt