MySQL I : Creating Users

1. In this exercise you will create users other than root for working with databases, and grant them specific privileges. This provides added security against unwanted intruders and (more importantly) against your own goof-ups.
2. Log into the MySQL Monitor client: mysql –u root –p
3. Create two new databases:
CREATE DATABASE alpacas;
4. Create a user that has administrative privileges on the ‘alpacas’ database:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, FILE ON alpacas.* TO llama@localhost IDENTIFIED BY “camel”;
5. What might be wrong with the following commands?
GRANT SELECT ON *.* TO llama IDENTIFIED BY “camel”;
or
GRANT ALL ON *.* TO llama IDENTIFIED BY “camel”;
6. Type: FLUSH PRIVILEGES;
IMPORTANT! This applies the changes that you made and is required. MySQL will not enable your changes without this step.
An alternate way to flush privileges is to exit the client and type: mysqladmin –u root –p reload
7. Quit MySQL: quit
8. Test the new llama account: mysql –u llama –p
Then enter the password: camel
You should enter the client.
9. Exit the MySQL client.
10. Note that the root administrator can revoke privileges as well. The syntax is:
REVOKE <privileges> ON <database.table> FROM <user@location>;