Bug #84903 Getting access denied error while assigning Grant all privileges to a user
Submitted: 9 Feb 2017 8:25 Modified: 9 Feb 2017 13:27
Reporter: Azhar Siddiqui Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S1 (Critical)
Version:5.7.17 OS:CentOS (6.8)
Assigned to: CPU Architecture:Any
Tags: Root access denied error while assigning Grants

[9 Feb 2017 8:25] Azhar Siddiqui
Description:
Hi,

I am able to login using root access credentials using the command.
shell>mysql -u root -p

On successful login, while I can create database, create user and assigning some grants but as soon as I try to give Grant all Privilege to a user I created, it gives error as follows

mysql> create user dba identified by  'Dbasecret@1';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' IDENTIFIED BY 'dbasecret';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I tried many options but unable to overcome this issue. Kindly help me resolve this issue.

Thanks,

How to repeat:
shell> mysql --version

mysql> create user dba identified by  'Dbasecret@1';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' IDENTIFIED BY 'dbasecret';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I tried many options but unable to overcome this issue. Kindly help me resolve this issue.
[9 Feb 2017 11:05] MySQL Verification Team
Thank you for the bug report. I coudln't repeat with most recent source code, are you using the temporary password for root created in the install process?

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-log Source distribution PULL: 2017-FEB-01

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > create user dba identified by  'Dbasecret@1';
Query OK, 0 rows affected (0,00 sec)

mysql 5.7 > GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' IDENTIFIED BY 'dbasecret';
Query OK, 0 rows affected, 2 warnings (0,00 sec)
[9 Feb 2017 11:19] Azhar Siddiqui
Hi,
No I didn't use the default password which comes with the log. I made the file empty and then created a root password.

I was able to login using the root password.

Was that an issue?

Thanks,
[9 Feb 2017 13:27] MySQL Verification Team
I couldn't repeat on CentOS 7, please check your root grants:

miguel@tikal:~ $ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW GRANTS FOR 'root'@'localhost'\G
*************************** 1. row ***************************
Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
2 rows in set (0.00 sec)

mysql> create user dba identified by  'Dbasecret@1';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' IDENTIFIED BY 'dbasecret';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' IDENTIFIED BY 'QWE567Met@#$';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement.
1 row in set (0.00 sec)