List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 13 1999 2:40am
Subject:Re: Question about privileges
View as plain text  
At 9:34 PM -0500 9/12/99, Robert C. Paulsen, Jr. wrote:
>I am just getting started with MySQL and am trying to understand the
>basics of user privileges.
>I have run into a problem where a user who has been GRANTed
>privileges doesn't seem to get those privileges. I think I understand
>the problem and have even been able to "solve" it, but I don't have
>a warm feeling I solved it the right way.
>Here is the mysql.user table...
>| host      | user   | password         | etc...
>+-----------+--------+------------------+ etc...
>| localhost | root   | 5d2e19393cc5ef67 | etc...
>| home      | root   |                  | etc...
>| localhost |        |                  | etc...
>| home      |        |                  | etc...
>| %         | robert | 5d2e19393cc5ef67 | etc...
>The first four lines were created when MySQL was installed and
>the 'mysql_install_db' script was run. The last line was created
>when root issued:
>mysql>grant all on sample.* to robert identified by 'password'
>When user robert logs in to MySQL with -p, he gets "access denied"...
>> mysql -p
>Enter password: <"password" is typed here>
>ERROR 1045: Access denied for user: 'robert@localhost' (Using password: YES)
>But if he logs in like this, it works, but only to a point:
>> mysql
>Welcome to the MySQL monitor.  Commands end with ; or \g.
>Your MySQL connection id is 9 to server version: 3.22.25
>Type 'help' for help.
>mysql> use sample;
>Database changed
>mysql> show tables;
>ERROR 1044: Access denied for user: '@localhost' to database 'bookbiz'
>This all makes some sense since both of the above login attempts
>match the third line in the 'user' database. I can fix things by
>UPDATing the last line of the above 'user' database to change the
>host to 'localhost'. (I think I could also have deleted the 3rd
>entry -- "localhost".)
>What I don't understand is why I needed to go in and "fix" what the
>GRANT did. I assume I am misunderstanding something and have done
>something wrong.

GRANTing to "robert" is the same as granting to "robert@%".  If you
had granted to "robert@localhost", then you'd have a more specific
entry than the third line in your user table, and it would take
precedence over that line.

I would go ahead and delete the localhost/blank line.  I find they
cause more problems than they solve.  Make sure to FLUSH PRIVILEGES

Paul DuBois, paul@stripped
Question about privilegesJr. Robert C. Paulsen13 Sep
  • Re: Question about privilegesPaul DuBois13 Sep