FAQ
When I log in as root using:
mysql -u root -p mysql
I get the mysql prompt:
mysql>

I then issue this command or statement:

mysql>GRANT ALL PRIVILEGES ON *.* TO 'testUser'@'' IDENTIFIED BY
'some_password' WITH GRANT OPTION;

I get the following returned:

Query OK, 0 rows affected (0.00 sec)


Why is this not working? To test it further I try to log in as testUser
but it tells me access denied for user testUser@localhost

Even if I grant the testUser @ localhost.

I can look in the user table and sure enough user testUser@localhost is
there.


Bruce Martin
The Martin Solution
PO Box 644
Delaware Water Gap, PA
(570) 421-0670
bruce@martinsolution.com

Search Discussions

  • Mark at Nov 5, 2005 at 2:42 pm

    -----Original Message-----
    From: Bruce Martin
    Sent: zaterdag 5 november 2005 3:58
    To: mysql@lists.mysql.com
    Subject: Add New User

    When I log in as root using: mysql -u root -p mysql I get the
    mysql prompt:
    mysql>

    I then issue this command or statement:

    mysql>GRANT ALL PRIVILEGES ON *.* TO 'testUser'@'' IDENTIFIED
    BY 'some_password' WITH GRANT OPTION;

    I get the following returned:

    Query OK, 0 rows affected (0.00 sec)

    Why is this not working?
    Because, afterwards, you forgot to say:

    FLUSH PRIVILEGES;

    Perhaps?

    - Mark
  • Michael Stassen at Nov 6, 2005 at 5:24 am

    Mark wrote:
    -----Original Message-----
    From: Bruce Martin
    Sent: zaterdag 5 november 2005 3:58
    To: mysql@lists.mysql.com
    Subject: Add New User

    When I log in as root using: mysql -u root -p mysql I get the
    mysql prompt:
    mysql>

    I then issue this command or statement:

    mysql>GRANT ALL PRIVILEGES ON *.* TO 'testUser'@'' IDENTIFIED
    BY 'some_password' WITH GRANT OPTION;

    I get the following returned:

    Query OK, 0 rows affected (0.00 sec)

    Why is this not working?

    Because, afterwards, you forgot to say:

    FLUSH PRIVILEGES;

    Perhaps?

    - Mark
    No. FLUSH PRIVILEGES is not needed with GRANT
    <http://dev.mysql.com/doc/refman/5.0/en/grant.html>.

    Michael
  • Mark at Nov 6, 2005 at 3:30 pm

    -----Original Message-----
    From: Michael Stassen
    Sent: zondag 6 november 2005 6:26
    To: Mark
    Cc: 'Bruce Martin'; mysql@lists.mysql.com
    Subject: Re: Add New User
    Because, afterwards, you forgot to say:

    FLUSH PRIVILEGES;

    Perhaps?

    - Mark
    No. FLUSH PRIVILEGES is not needed with GRANT <http://dev.mysql.com/-
    doc/refman/5.0/en/grant.html>.
    Funny, I always thought FLUSH PRIVILEGES was required when the GRANT s-
    tatement creates a new user in the process. True enough, though, I can (no
    longer?) find that in the manual.

    - Mark
  • Michael Stassen at Nov 6, 2005 at 5:22 am

    Bruce Martin wrote:
    When I log in as root using:
    mysql -u root -p mysql
    I get the mysql prompt:
    mysql>

    I then issue this command or statement:

    mysql>GRANT ALL PRIVILEGES ON *.* TO 'testUser'@'' IDENTIFIED BY
    'some_password' WITH GRANT OPTION;
    First, your hostname is blank. That won't work. You need to either specify a
    hostname, or use the wildcard character, '%'. See the manual for details
    <http://dev.mysql.com/doc/refman/5.0/en/connection-access.html>.

    Second, why are you making testuser equivalent to root? Don't give a user any
    more privileges than necessary. For a test user, I'd suggest starting with just

    GRANT ALL PRIVILEGES ON test.* TO 'testUser'@'localhost'
    IDENTIFIED BY 'some_password';
    I get the following returned:

    Query OK, 0 rows affected (0.00 sec)

    Why is this not working?
    What makes you think it didn't work? You didn't get an error message. Instead,
    you got "Query OK". Looks like it worked, to me.
    To test it further I try to log in as testUser
    but it tells me access denied for user testUser@localhost
    That's not the whole error message. Please show us your exact comand, and
    copy/paste the exact error message. We could try to guess what's wrong
    (probably there is no testUser@localhost), but it's better not to guess.
    Even if I grant the testUser @ localhost.
    No spaces. Again, show us the exact GRANT command, the exact connection
    command, and the exact error message.
    I can look in the user table and sure enough user testUser@localhost is
    there.
    Better yet, you can verify a user's privileges with the SHOW GRANTS command:

    SHOW GRANTS FOR testUser@localhost;
    Bruce Martin
    The Martin Solution
    PO Box 644
    Delaware Water Gap, PA
    (570) 421-0670
    bruce@martinsolution.com
    Michael
  • Bruce Martin at Nov 6, 2005 at 5:38 pm
    Ok thank you for your replies. I finally got it working, but as you
    stated I granted too many permissions. I found that in the user table
    my testUser had N for every column with the exception of the last few
    which where 0.

    I first logged into the database using:

    shell>mysql -u root -p mysql

    So I used:

    mysql>GRANT ALL PRIVILEGES ON test.* TO 'testUser'@'localhost'
    IDENTIFIED BY 'some_password';

    and:

    mysql>GRANT ALL PRIVILEGES ON test.* TO 'testUser'@'%' IDENTIFIED BY
    'some_password';

    Then I did:

    mysql> select * from user where user.User='testUser';

    Which returned:
    Host | User | Password |
    Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
    Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
    Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
    Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
    Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv
    Create_routine_priv | Alter_routine_priv | Create_user_priv |
    ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions |
    max_updates | max_connections | max_user_connections |
    +-----------+----------+-------------------------------------------
    +-------------+-------------+-------------+-------------+-------------
    +-----------+-------------+---------------+--------------+-----------
    +------------+-----------------+------------+------------
    +--------------+------------+-----------------------+------------------
    +--------------+-----------------+------------------+------------------
    +----------------+---------------------+--------------------
    +------------------+----------+------------+-------------
    +--------------+---------------+-------------+-----------------
    +----------------------+
    % | testUser | *12F46AB3EF1939F7B217B125466177AFA18495CF | N
    N | N | N | N | N
    N | N | N | N | N
    N | N | N | N | N
    N | N | N | N
    N | N | N | N
    N | N | |
    0 | 0 |
    0 | 0 |
    localhost | testUser | *12F46AB3EF1939F7B217B125466177AFA18495CF | N
    N | N | N | N | N
    N | N | N | N | N
    N | N | N | N | N
    N | N | N | N
    N | N | N | N
    N | N | |
    0 | 0 |
    0 | 0 |

    I then did:

    UPDATE user SET
    Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_p
    riv='Y' WHERE user.User='testUser';


    I can now access the server using my test user: testUser.

    However, even though I specified 'test.*' my test user can access the
    mysql database. doesn't test.* limit the user to the test database? or
    should it have been 'test' no quotes of course.

    Is there a way to update the database access without deleting the user
    and redoing it all?

    Thanks for the help.
    On Nov 6, 2005, at 12:23 AM, Michael Stassen wrote:

    Bruce Martin wrote:
    When I log in as root using:
    mysql -u root -p mysql
    I get the mysql prompt:
    mysql>
    I then issue this command or statement:
    mysql>GRANT ALL PRIVILEGES ON *.* TO 'testUser'@'' IDENTIFIED BY
    'some_password' WITH GRANT OPTION;
    First, your hostname is blank. That won't work. You need to either
    specify a hostname, or use the wildcard character, '%'. See the
    manual for details
    <http://dev.mysql.com/doc/refman/5.0/en/connection-access.html>.

    Second, why are you making testuser equivalent to root? Don't give a
    user any more privileges than necessary. For a test user, I'd suggest
    starting with just

    GRANT ALL PRIVILEGES ON test.* TO 'testUser'@'localhost'
    IDENTIFIED BY 'some_password';
    I get the following returned:
    Query OK, 0 rows affected (0.00 sec)
    Why is this not working?
    What makes you think it didn't work? You didn't get an error message.
    Instead, you got "Query OK". Looks like it worked, to me.
    To test it further I try to log in as testUser but it tells me access
    denied for user testUser@localhost
    That's not the whole error message. Please show us your exact comand,
    and copy/paste the exact error message. We could try to guess what's
    wrong (probably there is no testUser@localhost), but it's better not
    to guess.
    Even if I grant the testUser @ localhost.
    No spaces. Again, show us the exact GRANT command, the exact
    connection command, and the exact error message.
    I can look in the user table and sure enough user testUser@localhost
    is there.
    Better yet, you can verify a user's privileges with the SHOW GRANTS
    command:

    SHOW GRANTS FOR testUser@localhost;
    Bruce Martin
    The Martin Solution
    PO Box 644
    Delaware Water Gap, PA
    (570) 421-0670
    bruce@martinsolution.com
    Michael
    Bruce Martin
    The Martin Solution
    PO Box 644
    Delaware Water Gap, PA
    (570) 421-0670
    bruce@martinsolution.com
  • Björn Persson at Nov 6, 2005 at 6:15 pm

    söndagen den 6 november 2005 18:39 skrev Bruce Martin:
    However, even though I specified 'test.*' my test user can access the
    mysql database.
    That's because this statement:
    UPDATE user SET
    Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_p
    riv='Y' WHERE user.User='testUser';
    granted those privileges on _all_ databases.

    Björn Persson
  • Bruce Martin at Nov 6, 2005 at 7:43 pm
    Ok thank you, I think I have it now. Now to get my Client application
    working.
    On Nov 6, 2005, at 1:16 PM, Björn Persson wrote:

    söndagen den 6 november 2005 18:39 skrev Bruce Martin:
    However, even though I specified 'test.*' my test user can access the
    mysql database.
    That's because this statement:
    UPDATE user SET
    Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create
    _p
    riv='Y' WHERE user.User='testUser';
    granted those privileges on _all_ databases.

    Björn Persson

    --
    MySQL General Mailing List
    For list archives: http://lists.mysql.com/mysql
    To unsubscribe: http://lists.mysql.com/mysql?unsub=bmartin@mac.com
    Bruce Martin
    The Martin Solution
    PO Box 644
    Delaware Water Gap, PA
    (570) 421-0670
    bruce@martinsolution.com
  • Puiu Hrenciuc at Nov 6, 2005 at 11:14 am
    "Bruce Martin" <bmartin@mac.com> wrote in message
    news:5eee33292ec482025f2b249554c08ca2@mac.com...
    When I log in as root using:
    mysql -u root -p mysql
    I get the mysql prompt:
    mysql>

    I then issue this command or statement:

    mysql>GRANT ALL PRIVILEGES ON *.* TO 'testUser'@'' IDENTIFIED BY
    'some_password' WITH GRANT OPTION;
    You haven't specified the host, try this :

    To connect only from the localhost :

    GRANT ALL PRIVILEGES ON *.* TO 'testUser'@'localhost' IDENTIFIED BY
    'some_password' WITH GRANT OPTION;

    Or if you want to be able to connect from any host :

    GRANT ALL PRIVILEGES ON *.* TO 'testUser'@'%' IDENTIFIED BY
    'some_password' WITH GRANT OPTION;

    Or if you would like to be able to connect from some host :

    GRANT ALL PRIVILEGES ON *.* TO 'testUser'@'host_name_or_ip' IDENTIFIED BY
    'some_password' WITH GRANT OPTION;
    I get the following returned:

    Query OK, 0 rows affected (0.00 sec)


    Why is this not working? To test it further I try to log in as testUser
    but it tells me access denied for user testUser@localhost

    Even if I grant the testUser @ localhost.

    I can look in the user table and sure enough user testUser@localhost is
    there.


    Bruce Martin
    The Martin Solution
    PO Box 644
    Delaware Water Gap, PA
    (570) 421-0670
    bruce@martinsolution.com

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupmysql @
categoriesmysql
postedNov 5, '05 at 2:55a
activeNov 6, '05 at 7:43p
posts9
users5
websitemysql.com
irc#mysql

People

Translate

site design / logo © 2022 Grokbase