Search code examples
mysqlsql-grant

Granting privilege to existing user gives "you are not allowed to create a user with grant" even though the user already exists

I am logged in as root user and i just reinstalled the mysql server. login_manager isn't created as is shown when i execute SELECT * FROM mysql.user;. I now execute

CREATE USER 'login_manager'@'localhost' IDENTIFIED BY 'login_manager'; and when i check if the user is created, i can find it. Executing

GRANT INSERT
    ON clients
    TO login_manager;

where client is a table gives me the error i put in the title. Executing FLUSH PRIVILEGES; after creating the user didn't help either. I flushed privileges before creating the user. I looked that up, and normally this error comes when you try to create the user in the grant statement, which i am not doing.

I know this is long, but here are the Grants that root has:

'GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION'

and

'GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION'

and

'GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION'
Solution

  • Your login_manager user has a Host entry, likely localhost or %.

    Because of this, your GRANT technically isn't for that user, it's for a login_manager with no Host entry - so mysql knows it would have to create the user with no Host first.

    Asuming you used localhost when you created the user, use this to do the grants:

    GRANT INSERT
ON clients
TO 'login_manager'@'localhost';