Project 8-1 Set Privileges

This project will allow you to gain experience granting user accounts and setting the privileges for them to access your database.

Step by Step

1. Open a command-line window and log in to the MySQL client as root.

2. Create a user named second with the password pa552 and grant all privileges when logging in from any host.

    GRANT ALL ON *.* TO second@'%' IDENTIFIED BY "pa552"; 

   Note  

   Using a wildcard symbol ( * or % ) for the user or host requires that it be surrounded by single quotes.

3. Verify the grant.

    SHOW GRANTS FOR second@'%'; 

4. Create a user named joss with the password f1refly , logging in from the localhost only, with all privileges plus GRANT OPTION on the duck_sales table only.

    GRANT ALL ON duckwear.duck_sales TO joss@localhost IDENTIFIED BY "f1refly" WITH GRANT OPTION; 

5. Open a new command-line window and log in as joss . Execute the following commands to demonstrate that the user joss has access to the duck_sales table only.

    SELECT * FROM duck_cust LIMIT 3; SELECT * FROM duck_sales LIMIT 3; 

   You will get an error when the user joss tries to access the duck_cust table. (The LIMIT option is used only to make the return a manageable size for the illustration.)

6. Create a user named marti on localhost with a password of tick3t , who has ALL privileges on the duck_sales table, and then revoke the DELETE , DROP , and ALTER privileges.

    GRANT ALL ON duckwear.duck_sales TO marti@localhost IDENTIFIED BY "tick3t"; REVOKE DELETE, DROP, ALTER ON duckwear.duck_sales FROM  marti@localhost  ; SHOW GRANTS FOR marti@localhost; 

7. Have the user joss grant the DELETE privilege to marti@localhost .

    GRANT DELETE ON duckwear.duck_sales TO  marti@localhost  ; SHOW GRANTS FOR marti@localhost; 

8. Have the root user show the change.

    SHOW GRANTS FOR marti@localhost; 

   Compare the results with those received in step 6.

9. Revoke ALL privileges from the user second , delete the user, and then verify the removal.

    REVOKE ALL ON *.* FROM  second@`%`  ; DELETE FROM mysql.user WHERE USER="second" AND HOST= "%"; FLUSH PRIVILEGES; SHOW GRANTS FOR second@'%'; 

Project Summary

This project gave you hands-on experience with creating new users and setting and revoking their privileges. You created a user using WITH GRANT OPTION , and then had that user grant another user a specified privilege. You created users that had only table-specific access to a database, as well as a user with all privileges to all databases. You also revoked that user s privileges, which left the user with only the USAGE privilege, and then took that away by deleting the user from the mysql.user table and flushing the privileges tables to remove the previous reference to the now deleted user.