0

I created a user named 'root', by setting a password for it by using:

 create user 'root'@'localhost' identified by 'some_password';

It successfully created a user and I assigned all the privileges to it against a particular database that I created, and was able to connect successfully to that database.

But when I logged in using:

 mysql -u root -p

with the password I set in first step and tried performing operations like Create New Database, Select user from mysql.user, and all other operations not related to that particular database against which the permissions are assigned , it is throwing

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'root'@'localhost' for table 'user'
mysql> 
mysql> grant usage on *.* to 'root'@'localhost';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
mysql> GRANT ALL PRIVILEGES ON * . * TO 'root'@'localhost';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
mysql> SHOW GRANTS FOR 'root'@'localhost';
+-------------------------------------------------------------+
| Grants for root@localhost                                   |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'localhost'                    |
| GRANT ALL PRIVILEGES ON `navi`.`navi` TO 'root'@'localhost' |
+-------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> create user 'django'@'localhost' identified by 'django-user-password';
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
mysql> flush privileges;
ERROR 1227 (42000): Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
mysql> FLUSH PRIVILEGES;
ERROR 1227 (42000): Access denied; you need (at least one of) the RELOAD privilege(s) for this operation

In my understanding I might have overwritten the root user by creating a new root user. Is there anyway I can login as main root user and not the one that I created? So that I can perform basic MySQL operations.

Shadow
  • 33,525
  • 10
  • 51
  • 64
Pramod
  • 1
  • 1
  • I think that the question was closed errorneously. OP does not need to reset the password. The default `root` privileges must be reset - reference thread cannot help. – Akina Dec 28 '20 at 11:18
  • Perform the operation described at https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html "B.3.3.2.3 Resetting the Root Password: Generic Instructions", but instead of ALTER USER execute the next statements: – Akina Dec 28 '20 at 11:22
  • ```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;``` – Akina Dec 28 '20 at 11:22
  • ```GRANT APPLICATION_PASSWORD_ADMIN,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,SERVICE_CONNECTION_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION;``` – Akina Dec 28 '20 at 11:23
  • ```GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;``` – Akina Dec 28 '20 at 11:23

0 Answers0