1

I'm creating this new user specifically to use with my django project, i made this same user with mariadb and phpmyadmin, done, tryin to do the same with mysql, and suddenly i discovered my root user can't grant privileges to other users.

And even more, i tried with my second super user which is admin, both are unable to grant it, i've tried several solutions to this issue posted here in stackoverflow, and nothing has worked.

This is not a remote connection, everything from localhost.

Saw many people saying that i should flush privileges after granting option, problem is i'm unable to grant anything in the first place, already tried granting all with grant option with admin to root, and viceversa

The current line shows exactly what you'll expect, both admin and root has grant and super priv.

The second line shows all the privileges owned and granted for root user, as you can see, it shows in the end, with grant option.

SELECT Host,User,Grant_priv,Super_priv FROM mysql.user;

SHOW GRANTS FOR 'root'@'localhost';

╔═══════════╦══════════════════╦════════════╦════════════╗
║ Host      ║ User             ║ Grant_priv ║ Super_priv ║
╠═══════════╬══════════════════╬════════════╬════════════╣
║ %         ║ admin            ║ Y          ║ Y          ║
║ %         ║ gameled_admin    ║ N          ║ N          ║
║ localhost ║ mysql.infoschema ║ N          ║ N          ║
║ localhost ║ mysql.session    ║ N          ║ Y          ║
║ localhost ║ mysql.sys        ║ N          ║ N          ║
║ localhost ║ root             ║ Y          ║ Y          ║
╚═══════════╩══════════════════╩════════════╩════════════╝

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

GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION

GRANT ALL PRIVILEGES ON `mysql`.* TO `root`@`localhost` WITH GRANT OPTION

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

anything will be helpful, thanks in advance

EDIT 1:

Another code tested:

SELECT User()

Results in:

╔════════════════╗
║ User()         ║
╠════════════════╣
║ root@localhost ║
╚════════════════╝

Some of the errors i get are:

You are not allowed to create a user with GRANT

I use this line

SHOW GRANTS FOR 'multilang_user';

And get this:

There is no such grant defined for user 'multilang_user' on host '%'

EDIT 2:

A new 'root'@'%' user has been created

SHOW GRANTS FOR 'root'@'%';

this line shows once again:

Grants for root@%

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`@`%` WITH GRANT OPTION

GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`%` WITH GRANT OPTION

once again i tested:

GRANT ALL ON *.* TO 'multilang_user'@'localhost' WITH GRANT OPTION;

and results in this:

You are not allowed to create a user with GRANT

EDIT 3 PROBLEM SOLVED:

Solution: So i decided to delete my multilang user and create it once again, this time specifying the @'localhost' at the end of the username, so that way i could grant all the privileges from the root@localhost

Nicolas Silva
  • 566
  • 1
  • 10
  • 28
  • Do you get any error messages (or other messages), and if so, please add them. Try `select user()` to check which user/host you are logged in with. – Solarflare Apr 21 '19 at 08:42
  • thanks for the answer, i tried the select user(), and i get this User() root@localhost – Nicolas Silva Apr 21 '19 at 19:05
  • Thanks for adding the error message. You may be running into [this](https://stackoverflow.com/q/50177216) issue (assuming you are using MySQL 8). – Solarflare Apr 21 '19 at 19:51
  • ok so i tried the solution mentioned there, but the only thing that was added was a new root user for remote use, like this 'root'@'%' nothing else has changed, i'm still unable to grant any privilege to any user, neither for root nor % – Nicolas Silva Apr 21 '19 at 23:55
  • Then again: provide the corresponsing error messages, e.g. a specific command then the specific error message it creates (or the specific additional command + result with which you checked that the command had no effect). The 2 errors you showed are consistent with not having created the user first (which was allowed prior to version 8). – Solarflare Apr 22 '19 at 08:16
  • don't know exactly what you mean with not having created the user first?, already showed root exist, privileges exist, with grant option, can you help me on that?, i'm not getting your question – Nicolas Silva Apr 22 '19 at 16:42

0 Answers0