Why is grant identified as “mandatory” in some teams?

Question

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

This does not 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 it 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 is optional if it is already a user?

Answer 1

Most likely a problem with MySQL replication.

Answer 2

Perhaps you can restrict the GRANT statement to a user only if he is logged in using this particular authentication method (here: password). This, at least, is the case with the CREATE USER statement, and I think it should be similar here ...

See auth_option on this page for more details .