Databases Reference
In-Depth Information
you would with any other password-protected system: choose passwords that have a
mix of uppercase, lowercase, numeric, and special characters; avoid using dictionary
words; and avoid recording your password anywhere it can be easily found. We use no
passwords and simple passwords—such as
the_password
—in this chapter to demon-
strate concepts, but we recommend that in practice you use a more complex password
that incorporates a mix of letters, numbers, and punctuation symbols (for example,
1n1T?s313Y0
). Of course, choose a password that you can remember without having to
write it down somewhere; pieces of paper often turn up in the wrong hands!
The simplest method to set a password is to use the
IDENTIFIED BY
clause when you
create or modify the privileges of a user. You've seen several examples of this so far in
this chapter. Here's one reproduced from a previous section:
mysql>
GRANT ALL ON music.* TO 'allmusic'@'localhost' IDENTIFIED BY '
the_password
';
Query OK, 0 rows affected (0.06 sec)
This process takes the plain-text string
the_password
, hashes it using the MySQL
PASSWORD( )
function, and stores the hashed string in the
user
table in the
mysql
data-
base. Later, when a client wants a connection as this user, the plain-text password
supplied by the client is hashed with the
PASSWORD( )
function and compared to the
string in the database. If it matches, the client is authenticated; otherwise, not. Prior to
MySQL 4.1.0, the hashed string was 16 characters in length, and since 4.1.1 it has been
41 characters; don't use MySQL 4.1.0, which has an incompatible 45-character pass-
word and a different
PASSWORD( )
function.
You can experiment with the
PASSWORD( )
function to examine the strings produced
from a plain-text password. With a server older than 4.1.1, or with a new server con-
figured with the
old_passwords
option, you would see:
mysql>
SELECT PASSWORD('
the_password
');
+--------------------------+
| PASSWORD('
the_password
') |
+--------------------------+
| 268f5b591007a24f |
+--------------------------+
1 row in set (0.07 sec)
Using exactly the same command on a MySQL server that is newer than version 4.1.1
(and that has not been configured with the
old_passwords
option), we get:
mysql>
SELECT PASSWORD('
the_password
');
+-------------------------------------------+
| PASSWORD('
the_password
') |
+-------------------------------------------+
| *201716EF6717C367868F777B9C6E17796F19F379 |
+-------------------------------------------+
1 row in set (0.02 sec)
You can still list the old-format password using the
OLD_PASSWORD( )
function:
mysql>
SELECT OLD_PASSWORD('
the_password
');
+--------------------------+
