More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
MySQL - Setting Up Passwords
In most cases you should use GRANT to set up your users/passwords, so the following only applies for advanced users.
The examples in the preceding sections illustrate an important principle: when you store a non-empty password using INSERT
or UPDATE statements, you must use the PASSWORD() function to encrypt it. This is because the user table stores passwords in
encrypted form, not as plaintext. If you forget that fact, you are likely to attempt to set passwords like this:
shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
VALUES('%','jeffrey','biscuit');
mysql> FLUSH PRIVILEGES;
The result is that the plaintext value 'biscuit' is stored as the password in the user table. When the user jeffrey attempts
to connect to the server using this password, the mysql client encrypts it with PASSWORD() and sends the result to the
server. The server compares the value in the user table (the encrypted value of 'biscuit') to the encrypted password (which
is not 'biscuit'). The comparison fails and the server rejects the connection:
shell> mysql -u jeffrey -pbiscuit test
Access denied
Passwords must be encrypted when they are inserted in the user table, so the INSERT statement should have been specified
like this instead:
mysql> INSERT INTO user (Host,User,Password)
VALUES('%','jeffrey',PASSWORD('biscuit'));
You must also use the PASSWORD() function when you use SET PASSWORD statements:
mysql> SET PASSWORD FOR jeffrey@"%" = PASSWORD('biscuit');
If you set passwords using the GRANT ... IDENTIFIED BY statement or the mysqladmin password command, the PASSWORD() function
is unnecessary. They both take care of encrypting the password for you, so you would specify a password of 'biscuit' like
this:
mysql> GRANT USAGE ON *.* TO jeffrey@"%" IDENTIFIED BY 'biscuit';
or
shell> mysqladmin -u jeffrey password biscuit
NOTE: PASSWORD() does not perform password encryption in the same way that Unix passwords are encrypted. You should not
assume that if your Unix password and your MySQL password are the same, that PASSWORD() will result in the same encrypted
value as is stored in the Unix password file.
(Continued on next question...)