Learning Mysql

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 ali@sadri.invyhome.com |
+--------------------------------------------------------------------+
| GRANT USAGE ONTO 'ali'@'sadri.invyhome.com' |
| IDENTIFIED BY PASSWORD '*5DC1D11F45824A9DD613961F05C1EC1E7A1601AA' |
| GRANT ALL PRIVILEGES ON test TO 'ali'@'sadri.invyhome.com' |
+--------------------------------------------------------------------+
2 rows inset sec)
mysql> SHOW GRANTS FOR 'ali'@'saleh.invyhome.com';
+--------------------------------------------------------------------+
| Grants for ali@saleh.invyhome.com |
+--------------------------------------------------------------------+
| GRANT USAGE ONTO 'ali'@'saleh.invyhome.com' |
| IDENTIFIED BY PASSWORD '*A5CF560EBFDD483CD4162DD31FBA6AF8F5586069' |
| GRANT ALL PRIVILEGES ON music TO 'ali'@'saleh.invyhome.com' |
+--------------------------------------------------------------------+
2 rows inset 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.

Download 4.24 Mb.

Share with your friends: