Managing Users and Privileges - Learning MySQL - page 319

Again, the first statement creates a user with no privileges, and later statements add the
privileges. The first statement also serves another purpose: it sets the password for the
user. Since the password has been hashed with a one-way encryption function, it can't
be decrypted, and so we repeat the encrypted password string with the statement
IDENTIFIED BY PASSWORD . We discuss passwords in detail later in this chapter.
The SHOW GRANTS statement works only for exploring exactly one user that matches the
string you provide. For example, if you've previously created a user 'fred'@'%' , you
can list the privileges of that user with:
mysql> SHOW GRANTS FOR 'fred'@'%';
This statement doesn't check for all users with the name fred , however. Each username
and host pair is treated separately; for example, we could have the user 'ali'@'sa
dri.invyhome.com' with all privileges on the test database, and the user 'ali'@'sa
leh.invyhome.com' with all privileges on the music database:
mysql> GRANT ALL ON test.* TO 'ali'@'sadri.invyhome.com'
-> IDENTIFIED BY 'a_password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL ON music.* TO 'ali'@'saleh.invyhome.com'
IDENTIFIED BY 'another_password';
Query OK, 0 rows affected (0.00 sec)
If you check the privileges with the SHOW GRANTS statement, you'll see that the access
privileges and the stored password are different for each username and host pair:
mysql> SHOW GRANTS FOR 'ali'@'sadri.invyhome.com';
+--------------------------------------------------------------------+
| Grants for [e-mail] |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ali'@'sadri.invyhome.com' |
| IDENTIFIED BY PASSWORD '*5DC1D11F45824A9DD613961F05C1EC1E7A1601AA' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'ali'@'sadri.invyhome.com' |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SHOW GRANTS FOR 'ali'@'saleh.invyhome.com';
+--------------------------------------------------------------------+
| Grants for [e-mail] |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ali'@'saleh.invyhome.com' |
| IDENTIFIED BY PASSWORD '*A5CF560EBFDD483CD4162DD31FBA6AF8F5586069' |
| GRANT ALL PRIVILEGES ON `music`.* TO 'ali'@'saleh.invyhome.com' |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
As you can see, a connection by ali is allowed different privileges and uses a different
password according to the host the connection is coming from.
To explore all of the users available on your MySQL server, you can use the command-
line mysqlaccess utility that we describe in the next section.