User Accounts and Privileges - Learning MySQL and MariaDB - page 303

GRANT USAGE ON *.* TO 'lena_stankoska' @ 'lena_stankoska_home'
IDENTIFIED BY 'her_password_123' ;
These examples usedthe CREATE USER and the GRANT statements to create the user ac-
counts. If you enter GRANT and specify a username that doesn't exist, it automatically cre-
ates the user — and remember that each combination of user and hostname is a unique
user account. However, it's recommended that you start with CREATE USER to create
the user account and then grant privileges. We addedthe IDENTIFIED BY clauses in
each of these SQL statements to set the passwords for each user account.
Let's see how one of Lena'suser accounts looks at this point. Enter the following on your
server:
SHOW GRANTS FOR 'lena_stankoska'@'localhost' \G
*************************** 1. row ***************************
Grants for admin_backup@localhost:
GRANT USAGE ON *.* TO 'lena_stankoska'@'localhost'
IDENTIFIED BY PASSWORD '
*B1A8D5415ACE5AB4BBAC120EC1D17766B8EFF1A1'
Notice that the password is encrypted in the results. There isn't a way within MySQL to
retrieve the password in plain text, to decrypt it. Also notice that the encrypted password
ispreceded by the PASSWORD keyword. If you don't want to enter someone's password
with clear text as we did in the earlier commands, you could encrypt the password on a
different computer withthe PASSWORD() function and then copy the results to the server
using the GRANT statement. You would do that like this:
SELECT PASSWORD('her_password_123');
+-------------------------------------------+
| PASSWORD('its_password_123') |
+-------------------------------------------+
| *B1A8D5415ACE5AB4BBAC120EC1D17766B8EFF1A1 |
+-------------------------------------------+
The encrypted text is identical to the one in the results of the earlier SHOW GRANTS
statement. If your server is logging all transactions, you may want to encrypt passwords
on your personal computer by this method and use the results for entering the passwords
on your server so no one else will know the password for a user account. Starting with
MySQL version 5.6, any SQL statement that contains the reserved word PASSWORD will
not be logged.