Setting Up Users on MySQL

Create a New User:

mysql> CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';
mysql> CREATE USER 'user1'@'%' IDENTIFIED BY 'password';

If you know the password hash, you can add a PASSWORD prefix to your call like this:

mysql> CREATE USER 'user1'@'%' IDENTIFIED BY PASSWORD '*93840205958...';

Make sure you know what is the default password encoding in use:

mysql> SHOW VARIABLES LIKE 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

Ref: http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_password

Grant Privileges to a User:

-- Grant everything to user1 from any host
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' WITH GRANT OPTION;
-- You can shorten ALL PRIVILEGES with ALL
mysql> GRANT ALL ON *.* TO 'user1'@'localhost';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'user1'@'localhost';
mysql> FLUSH PRIVILEGES;

Create a New User and Grant Privileges in One Line:

mysql> GRANT ALL ON *.* TO 'user1'@'localhost' IDENTIFIED BY 'password';

To check privileges for an account:

mysql> SHOW GRANTS;
mysql> SHOW GRANTS FOR 'user1'@'localhost';
+--------------------------------------------------------------------+
| Grants for user1@localhost                                         |
+--------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'user1'@'localhost' |
+--------------------------------------------------------------------+

To reset password:

mysql> SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('mypassword');

mysql> SET PASSWORD FOR 'user1'@'localhost' = OLD_PASSWORD('mypassword');

mysql> SET PASSWORD FOR 'user1'@'localhost' = 'password-hash-string';

Remove User's Privilege

mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'user1'@'localhost';