hashingMySQLpassword
Is there a query that a MySQL user that does not have admin rights can run to determine if they are using the old (pre-4.1) hash or the new (4.1-and-later) hash? This user was explicitly granted access only to their own database and not to any of the system tables (such as mysql.user).
This is on a MySQL 5.5 system.
I think using a MySQL gui tool like SQLyog would solve your problem. It has User manager tool where you can set privilege to different users.
Based on your old_passwords setting, I would ask you to look at three things
mysql.userPlease run the following:
show variables like 'old_passwords';
select password('rolando'),old_password('rolando');
Depending on where old_passwords is enabled or not, you should get either the following:
MySQL 5.0 with old_passwords off
mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> select password('rolando'),old_password('rolando');
+-------------------------------------------+-------------------------+
| password('rolando') | old_password('rolando') |
+-------------------------------------------+-------------------------+
| *C1868CE781FCB521A03168DE044BF3F64E9E485A | 05e97e95241a4409 |
+-------------------------------------------+-------------------------+
1 row in set (0.00 sec)
mysql>
MySQL 5.1 with old_passwords on
mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> select password('rolando'),old_password('rolando');
+---------------------+-------------------------+
| password('rolando') | old_password('rolando') |
+---------------------+-------------------------+
| 05e97e95241a4409 | 05e97e95241a4409 |
+---------------------+-------------------------+
1 row in set (0.00 sec)
mysql>
MySQL 5.5 with old_passwords off
mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> select password('rolando'),old_password('rolando');
+-------------------------------------------+-------------------------+
| password('rolando') | old_password('rolando') |
+-------------------------------------------+-------------------------+
| *C1868CE781FCB521A03168DE044BF3F64E9E485A | 05e97e95241a4409 |
+-------------------------------------------+-------------------------+
1 row in set (0.00 sec)
mysql>
MySQL 5.5 with old_passwords on
mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> select password('rolando'),old_password('rolando');
+---------------------+-------------------------+
| password('rolando') | old_password('rolando') |
+---------------------+-------------------------+
| 05e97e95241a4409 | 05e97e95241a4409 |
+---------------------+-------------------------+
1 row in set (0.00 sec)
mysql>
Please run this query:
SELECT COUNT(1) password_length_count,password_length FROM
(SELECT LENGTH(password) password_length FROM mysql.user) A
GROUP BY password_length;
If you get any password_length of 16, you have to enable old_passwords for the old style authentication to work.
In some cases, using mysql libraries that came from old yum installs of MySQL, PHP, Perl or Apache may have pre-MySQL4.1 user authentication code. Please upgrade such libraries.
Best Answer
No, a user would need SELECT on mysql.user to be able to read the password column, or the SUPER privilege to be able to see the password in the output of SHOW GRANTS. I think both of those would be classed as "admin rights".