User-name case sensitivity in MySQL grant statement

October 2, 2023

My impression is that MySQL is generally not case-sensitive, especially in MS Windows. In MySQL 8.0.34, I ran the following statements and got an error for the last statement. The error message is "Error Code: 1141. There is no such grant defined for user ‘USER1’ on host ‘%’". Once I change "USER1" to "user1", it works. "GRANT SELECT ON DB1.TABLE1 TO USER1;" also won’t work. Can you please explain why? Thank you very much.

DROP SCHEMA IF EXISTS db1;
CREATE SCHEMA db1;
USE db1;
CREATE TABLE Table1(
  productID INT primary key
);
DROP USER IF EXISTS user1;
CREATE USER user1 IDENTIFIED BY 'password111';
SHOW GRANTS FOR USER1;

My system’s character set and collation are ‘utf8mb4’, ‘utf8mb4_0900_ai_ci’ respectively.

>Solution :

https://dev.mysql.com/doc/refman/8.0/en/account-names.html

For access-checking purposes, comparisons of User values are case-sensitive. Comparisons of Host values are not case-sensitive.

The mysql.user table has a column user, which stores the username in a column defined to use a binary collation. Thus it is case-sensitive.

mysql> show create table mysql.user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  ...
  `User` char(32) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
  ...