Granting and Revoking Privileges

Background

This is a two part exercise. The first part is a quiz about MySQL syntax with respect to privileges. The second part consists of granting and revoking privileges to MySQL users.

Objectives

• Understand MySQL syntax with respect to privileges;
• Grant privileges to MySQL users;
• Revoke privileges from MySQL users.

Steps

1. Take the Quiz
• If you get stuck, access the MySQL help feature by typing in help;.


2. Grant privileges
• Use the mysql database.

mysql>use mysql;

• Write a GRANT statement to create a user called 'vet', with password 'newpw', who has access to SELECT, UPDATE, INSERT, and DELETE on the pet table.

mysql>grant select, update, insert, delete
->on pet.pet to vet
->identified by 'newpw';

• Verify that the privileges were granted.

mysql>SHOW GRANTS FOR 'vet'@'localhost';

• Rename the user vet to doc.

  mysql>RENAME USER vet TO doc;

• Find out how many columns there are in the user table.

mysql>SHOW COLUMNS FROM mysql;

• Without using a GRANT statement, grant all privileges by creating an entry for nurse@localhost in the user table.

mysql>INSERT INTO user VALUES('localhost','nurse',PASSWORD('newpw'),
->'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
->'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
->'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

• Verify that the privileges were granted.

mysql>SHOW GRANTS FOR 'nurse'@'localhost';

Why weren't the privileges granted?

• Make the change take effect.

mysql>FLUSH PRIVILEGES;

• Again, verify that the privileges were granted.

mysql>SHOW GRANTS FOR 'nurse'@'localhost';

3. Revoke privileges
• Write a REVOKE statement to remove this user's privileges.

mysql>revoke select, update, insert, delete
->on pet.pet from doc;

• Write a DROP statement to remove nurse as a user.

mysql>DROP USER nurse@localhost


4. Exit
• Use QUIT to exit out of the server.

  mysql>QUIT

Congratulations! You have successfully granted and revoked MySQL privileges.