Bug #70081 Creating mysql user with hash password is not working.
Submitted: 19 Aug 2013 5:29 Modified: 17 Jun 2014 11:41
Reporter: sandeep varupula Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:mysql 5.1.69 OS:Linux (CentOS 5.7)
Assigned to: CPU Architecture:Any

[19 Aug 2013 5:29] sandeep varupula
Description:
I have created an user with selective privileges. 

I am able to login with that user but DML commands are not working.

How to repeat:
mysql> select password('test');
+------------------+
| password('test') |
+------------------+
| 378b243e220ca493 |
+------------------+

GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '378b243e220ca493';

GRANT SELECT, INSERT, UPDATE, DELETE,  INDEX, CREATE TEMPORARY TABLES, EXECUTE,  SHOW VIEW, TRIGGER ON `test`.* TO 'test'@'%';
[19 Aug 2013 5:41] MySQL Verification Team
After login with the test account can you show us output of:

SELECT CURRENT_USER(),USER();
SHOW GRANTS;
[23 Aug 2013 3:53] sandeep varupula
Here is the output.

mysql> SELECT CURRENT_USER(),USER();
+----------------+----------------------------------------+
| CURRENT_USER() | USER()                                 |
+----------------+----------------------------------------+
| test@%         | test@some.server.com 		  |
+----------------+----------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS\G
*************************** 1. row ***************************
Grants for test@%: GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '378b243e220ca493'
*************************** 2. row ***************************
Grants for test@%: GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, CREATE TEMPORARY TABLES, EXECUTE, SHOW VIEW, TRIGGER ON `test`.* TO 'test'@'%'
2 rows in set (0.00 sec)
[17 Jun 2014 11:41] MySQL Verification Team
Thank you for the report.
I could repeat this behavior on anyone of the GA releases(5.1, 5.5, 5.6).
Please could you upgrade to current GA version, try with it and
inform us if problem still exists.

mysql> select password('test');
+-------------------------------------------+
| password('test')                          |
+-------------------------------------------+
| *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> set old_passwords=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select password('test');
+------------------+
| password('test') |
+------------------+
| 378b243e220ca493 |
+------------------+
1 row in set (0.00 sec)

mysql> GRANT ALL ON *.* TO 'someuser'@'localhost' IDENTIFIED BY '378b243e220ca493';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'someuser'@'localhost';
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for someuser@localhost                                                                                            |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'someuser'@'localhost' IDENTIFIED BY PASSWORD '*5F196F3EECED7E7FBCE3147A817520B7CEE08055' |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> \q
Bye
[root@cluster-repo mysql-5.1.73]# bin/mysql -u someuser -p378b243e220ca493
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.1.73-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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>
mysql> SHOW GRANTS;
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for someuser@localhost                                                                                            |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'someuser'@'localhost' IDENTIFIED BY PASSWORD '*5F196F3EECED7E7FBCE3147A817520B7CEE08055' |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Database changed
mysql> create table t1(id int not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> update t1 set id=1000;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+------+
| id   |
+------+
| 1000 |
+------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT CURRENT_USER(),USER();
+--------------------+--------------------+
| CURRENT_USER()     | USER()             |
+--------------------+--------------------+
| someuser@localhost | someuser@localhost |
+--------------------+--------------------+
1 row in set (0.00 sec)