Search code examples
mysqlsqldatabase-administrationsql-grant

Why is 'identified by' mandatory in some grant commands?

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?

Solution

  • Most likely a MySQL replication issue.