Database Reference
In-Depth Information
Changing Passwords and Names
For better security, it's a good idea to change the passwords for user accounts regularly, es-
pecially for accounts with administrative privileges. How to change passwords is covered
in the next subsection. A user may ask, or you may want to rename a user account. This
isn't done as often, although it could be another security precaution. However, when you
change a name or a password, you should be mindful of whether the user account name and
password are incorporated into any scripts, in particular ones that run automatically to
make backups of the databases. You'll have to change them in those scripts, as well.
Setting a User Account Password
In the examplesthroughout this chapter, we have created user accounts without passwords
or given them passwords when creating the user accounts. You will occasionally need to
change the password for a user account, and actually should do so regularly for good secur-
ity. To do this, use the
SET PASSWORD
statementwith the
PASSWORD()
function to en-
crypt the password given.
TIP
As of version 5.6, you can force a user to change their password by expiring it. For this, you would usethe
ALTER USER
statement withthe
PASSWORD EXPIRE
clause like this:
ALTER
USER
'admin_granter'
@
'localhost'
PASSWORD EXPIRE
;
The next time the user tries to log in or execute an SQL statement, he will receive an error message in-
structing him to change his password. He'll have to use the
SET PASSWORD
statement to do that, before
any other SQL statements can be executed.
Let's change the password for the
admin_granter@localhost
user account:
SET
PASSWORD
FOR
'admin_granter'
@
'localhost'
=
PASSWORD
(
'some_pwd_123'
);
That's not a very good password. Let's change the password to something more complic-
ated, such as
P1ed_Avoce7-79873
. For an extra security measure, we'll use our personal
computer to encrypt that password before logging onto the server to set it in MySQL. From
a local computer, we'll execute thefollowing from the command line, assuming MySQL is
running on it:
mysql -p --skip-column-names --silent \
--execute="SELECT PASSWORD('P1ed_Avoce7-79873')"