Why is GRANT not working in MySQL?

Question

I'm scratching my head on this one as I see a ton of helper websites showing how to create MySQL users and grant privileges but for some reason it just does not work for me. I tried on both WinXP and a MacBook Pro laptop with the latest MySQL version installed.

The following is just an example from when I worked with WordPress. The actual database is something different but same issues.

Here are the steps:

mysql> mysql -uroot -p<password>
mysql> CREATE DATABASE wwordpress;
mysql> CREATE USER 'www'@'localhost' IDENTIFIED BY 'basic';

Query OK, 0 rows affected (0.14 sec)

mysql> GRANT INSERT ON wordpress.* TO 'www'@'localhost' IDENTIFIED BY 'basic';

Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM mysql.user WHERE User='www' \G

*************************** 1. row ***************************
                 Host: localhost
                 User: www
             Password: *E85C94AF0F09C892339D31CF7570A970BCDC5805
          Select_priv: N
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
          Reload_priv: N
        Shutdown_priv: N
         Process_priv: N
            File_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
         Show_db_priv: N
           Super_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
         Execute_priv: N
      Repl_slave_priv: N
     Repl_client_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
     Create_user_priv: N
           Event_priv: N
         Trigger_priv: N
             ssl_type:
           ssl_cipher:
          x509_issuer:
         x509_subject:
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
1 row in set (0.00 sec)

mysql>

As you can see "Insert_priv: N" should be "Y".

Any suggestions?

Thanks in advance.

score 53 · Accepted Answer · edited Apr 13 '21 at 05:46

53

What you are selecting are the global privileges. You are however giving database (and host, but that doesn't matter) specific privileges.

GRANT INSERT ON wordpress.* TO 'www'@'localhost' IDENTIFIED BY 'basic';

These permissions are stored in the db table.

Just to point you in the right direction:

SHOW GRANTS FOR 'www'@'localhost'

http://dev.mysql.com/doc/refman/5.0/en/show-grants.html

edited Apr 13 '21 at 05:46

Null

1,950
9
30
33

answered Oct 08 '10 at 19:29

The Surrican

29,118
24
122
168

martin clayton · Answer 2 · 2010-10-07T23:55:48.003

16

That's not where the most user GRANTed rights are stored - try

SHOW GRANTS FOR 'www'@'localhost'

to see database-specific permissions instead. (A grant would only show up in the user table if it was for all databases.)

Here's a (rather old) step-by-step detail of how permissions are stored in MySQL - I don't think things have changed much.

edited Oct 07 '10 at 23:55

answered Oct 07 '10 at 23:49

martin clayton

76,436
32
213
198

Thanks Martin. However, I wonder what this table is for then? I would think they should be consistent? Here's one of many links showing that the fields should be 'Y': http://kb.mediatemple.net/questions/788/HOWTO%3A+GRANT+privileges+in+MySQL – Christopher Oct 07 '10 at 23:55
It appears RTFM is the answer here: http://dev.mysql.com/doc/refman/5.0/en/show-grants.html – Christopher Oct 07 '10 at 23:59
@Christopher - I added a link that gives a discursive intro, hth. – martin clayton Oct 07 '10 at 23:59

score 3 · Answer 3 · edited Jul 05 '17 at 05:43

3

This should work:

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

edited Jul 05 '17 at 05:43

g00glen00b

41,995
13
95
133

answered Jul 05 '17 at 05:04

Ethicalguy Gopal Bogati

87
1
2

I know it's an example, but it should be `database\_name`. You need to escape underscores because database names are pattern matches. – Duncan Jun 08 '21 at 04:07

score 1 · Answer 4 · answered Mar 26 '12 at 12:06

You need to look at mysql.db or mysql.tables_priv tables if you need to select the Y or N if you are trying to do some restrictions of from what page a user can edit or insert or delete... This tables are automatically updated with the Ys and Ns as they are solely designed to show what privileges a user has on tables or columns as opposed to mysql.user whose purpose is to show that there is a certain user who can login(create connection) to a database.

score 0 · Answer 5 · answered Jan 22 '22 at 13:09

Yes I agree I created 2 users and granted them all privileges on the database but somehow only one of the users will receive the upgraded privileges, no matter what I do the second user will not receive the privileges despite the fact that I tried to grant the privileges to the user in exactly the same way that worked with the first user and I flushed the privileges afterwards

Why is GRANT not working in MySQL?

5 Answers5

Linked