SQL statement for privileges management in MySQL

SQL syntax

####This is a cheat sheet for mysql syntax, which is mostly about privilege management in mysql.
---------------------------------------------------------------------------------------------------------------------
### List all the user in mysql(with access to the mysql database)
mysql> select user, host, password from mysql.user;
 
### Create a user with password
mysql> create user 'shady'@'localhost' identified by 'password';
### Create a user without password
mysql> create user 'eric'@'localhost';
-----------------------------------------------------------------------------------------------------------------------
 
### Change the password for currently logged in account
  #### Find out which account you are currently logged in
  mysql> select current_user();
mysql> set password = password('new_password');
 
/*Notice that the password() function in mysql 
  The PASSWORD() function that computes password hash values and in 
  the structure of the user table where passwords are stored.
*/
 
### Change the password for specific user(with corresponding privileges)
mysql> set password for 'eric'@'localhost' = password('new_password');
--------------------------------------------------------------------------------------------------------------------------
#### Grant privilege for the new created user.
 
### Show the privileges of specific user  
mysql> show grants for 'daniel'@'localhost';
### You may see something like:  
+---------------------------------------------------------------------------------------------------------------+
| Grants for daniel@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'daniel'@'localhost' IDENTIFIED BY PASSWORD '*3C06A471CB6048FCCCF5DB904D8F5BE49F1C7585' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
### Show privileges for the current account that you logged in
mysql> show grants;
 
### Grant all privileges on one database to specific user
mysql> grant all on databaseName.* to 'daniel'@'localhost'; 
### Grant specific privileges(such as select, update, insert, delete) on database to specific user
mysql> grant select on databaseName.* to 'daniel'@'localhost';
### Grant specific privileges on table for specific user
mysql> grant select(track_id, time) on music.track to 'daniel'@'localhost';
 
### Use the with grant option, to make the user have the privilege to pass the privileges that he/she had granted
mysql> grant select on databaseName.* to 'daniel'@'localhost' with grant option;
/* This gives the the user 'daniel'@'localhost' the privilege to grant the select privilege to other user */
---------------------------------------------------------------------------------------------------------------------
### revoke privileges from specific user
   #### First, to see which privileges the user has
   mysql> show grants for 'daniel'@'localhost'
#### Rebvoke the select privileges from the user
mysql> revoke select on databaseName.* from 'daniel'@'localhost';