5

I don't understand this. I have a database, my_database, and a table, tablename. I have a user, ken who has read only access to the database. This user is already set up with a password, and people use this username remotely. I want to give them some permissions on tablename.

This doesn't work:

mysql> grant SELECT, INSERT, UPDATE, DELETE on my_database.tablename to 'ken'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

But this does:

mysql> grant SELECT, INSERT, UPDATE, DELETE on my_database.tablename to 'ken'@'%' identified by password '<existing password hash>';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Why? I thought the identified by part was optional if they're already a user?

coding_hero
  • 1,759
  • 3
  • 19
  • 34
  • Just out of curiosity: Why would you do something so confusing as grant the ability to `INSERT`, `UPDATE`, and `DELETE` to a user you've named `read_only` (which seems to indicate they're only allowed to read (`SELECT`) from the data? That seems like a really poorly thought out idea to me. – Ken White May 15 '15 at 17:19
  • Because that's not the real username. – coding_hero May 15 '15 at 17:37
  • Hmm.. OK. I personally would have gone with "I have a user, Joe, who...". :-) – Ken White May 15 '15 at 17:43
  • There you go. Changed it for you. – coding_hero May 15 '15 at 18:00
  • Thanks. Let me know when my rights change goes through, will you? :-) – Ken White May 15 '15 at 18:01
  • 1
    Are you able to reproduce this behaviour on a clean database on the same server? What does `SHOW GRANTS FOR ken@'%'` give you before and after the GRANT statement without password? – miken32 May 15 '15 at 20:38
  • I'm beginning to think this was some sort of replication issue. I think I'll close this question. – coding_hero May 16 '15 at 16:30
  • You might as well delete it. Just wasted 5 minutes, not reproducible. – fancyPants Nov 19 '15 at 08:32

2 Answers2

0

Maybe you can restrict the GRANT statement to the user only if it has logged on by this specific authentication method (here: password). This is at least the case with the CREATE USER statement, and I think it should be similar here...

For details, please search for auth_option on this page.

TomS
  • 467
  • 9
  • 25
0

Most likely a MySQL replication issue.

coding_hero
  • 1,759
  • 3
  • 19
  • 34