Professional Web Applications Themes

user authentication weirdness - MySQL

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

  1. #1

    Default user authentication weirdness

    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

    Ted Guest

  2. #2

    Default Re: user authentication weirdness

    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]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: user authentication weirdness


    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]jstucklexattglobal.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_dblocalhost
    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 Guest

  4. #4

    Default Re: user authentication weirdness

    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.
    >>jstucklexattglobal.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_dblocalhost
    > 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 'userhost' 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]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  5. #5

    Default Re: user authentication weirdness


    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.
    > >>jstucklexattglobal.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_dblocalhost
    > > 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 'userhost' 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]jstucklexattglobal.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

    Ted Guest

  6. #6

    Default Re: user authentication weirdness

    "Ted" <r.ted.byersrogers.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 userlocalhost. 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]
    Axel Schwenke Guest

  7. #7

    Default Re: user authentication weirdness


    Axel Schwenke wrote:
    > "Ted" <r.ted.byersrogers.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 userlocalhost. 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 userlocalhost.
    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

    Ted Guest

  8. #8

    Default Re: user authentication weirdness

    >> 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 userlocalhost. 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 userlocalhost.
    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 rootlocalhost 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
    Gordon Burditt Guest

Similar Threads

  1. User Authentication
    By Terry Murray in forum ASP Database
    Replies: 10
    Last Post: May 25th, 11:09 AM
  2. User Control in IE (authentication)?
    By Mantas Miliukas in forum ASP.NET Building Controls
    Replies: 0
    Last Post: February 25th, 03:57 PM
  3. XP user authentication
    By Avi in forum ASP.NET Security
    Replies: 2
    Last Post: December 15th, 08:09 PM
  4. SQL User authentication
    By Nikolay Petrov in forum ASP.NET Web Services
    Replies: 3
    Last Post: October 19th, 04:15 PM
  5. DB User authentication.
    By Allan Hugo in forum PHP Development
    Replies: 4
    Last Post: November 4th, 11:18 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139