Table of Contents

1. Challenge

   Create the Following Three Roles for Access to the `dev` Database: `dev_all`, `dev_read`, and `dev_write`

   Login to the MySQL server as the root user and run following statement to create roles for the dev database:

   mysql> CREATE ROLE 'dev_all', 'dev_read', 'dev_write';

2. Challenge

   Create the Following Three Roles for Access to the `prod` Database: `prod_all`, `prod_read`, and `prod_write`

   Run following statement to create roles for the prod database:

   mysql> CREATE ROLE 'prod_all', 'prod_read', 'prod_write';

3. Challenge

   Grant Privileges to the `dev_all`, `dev_read`, and `dev_write` Roles According to the Information Provided in the Instructions

   This command will grant all privileges on the dev database to the dev_all role:

   mysql> GRANT ALL ON dev.* TO 'dev_all';

   To grant the SELECT privilege on the dev database to the dev_read role, run this:

   mysql> GRANT SELECT ON dev.* TO 'dev_read';

   Use the following command to grant the INSERT, UPDATE, AND DELETE privileges on the dev database to the dev_write role:

   mysql> GRANT INSERT, UPDATE, DELETE ON dev.* TO 'dev_write';

4. Challenge

   Grant Privileges to the `prod_all`, `prod_read`, and `prod_write` Roles According to the Information Provided in the Instructions

   Use the following command to grant all privileges on the prod database to the prod_all role:

   mysql> GRANT ALL ON prod.* TO 'prod_all';

   Use the following command to grant the SELECT privilege on the dev database to the prod_read role:

   mysql> GRANT SELECT ON prod.* TO 'prod_read';

   Use the following command to grant the INSERT, UPDATE, AND DELETE privileges on the dev database to the prod_write role:

   mysql> GRANT INSERT, UPDATE, DELETE ON prod.* TO 'prod_write';

5. Challenge

   Assign the Roles for the `dev` Database to the Appropriate Users According to the Information Provided in the Instructions

   Assign the dev_all role to the user corey:

   mysql> GRANT dev_all to 'corey'@'localhost';

   Assign the dev_read role to the user will:

   mysql> GRANT dev_read to 'will'@'localhost';

   Assign the dev_write and dev_read roles to the user aaron:

   mysql> GRANT dev_write, dev_read to 'aaron'@'localhost';

   Set the default roles for the users so that the granted roles are active on login:

   mysql> SET DEFAULT ROLE ALL TO 'corey'@'localhost', 'will'@'localhost', 'aaron'@'localhost';

6. Challenge

   Assign the Roles for the `prod` Database to the Appropriate Users According to the Information Provided in the Instructions

   Assign the prod_all role to the user kenny:

   mysql> GRANT prod_all to 'kenny'@'localhost';

   Assign the prod_read role to the user myles:

   mysql> GRANT prod_read to 'myles'@'localhost';

   Assign the prod_write and prod_read roles to the user mike:

   mysql> GRANT prod_write, prod_read to 'mike'@'localhost';

   Set the default roles for the users so that the granted roles are active on login:

   mysql> SET DEFAULT ROLE ALL TO 'kenny'@'localhost', 'myles'@'localhost', 'mike'@'localhost';

7. Challenge

   Optionally, Validate the Newly Assigned Roles by Testing User Access

   Ensure that the user corey does not have access to the prod database:

   mysql> select * from prod.products;

   Ensure that the user corey has access to the dev database:

   mysql> select * from dev.products;

   Ensure that the user will has read access to the dev database:

   mysql> select * from dev.products;

   Ensure that the user aaron has write access to the dev database:

   mysql> INSERT INTO dev.orders (orderID,userName,orderType,purchaseDate) VALUES (4,'mike','laptop','2018-04-08');

   Ensure that the user kenny does not have access to the dev database:

   mysql> select * from dev.products;

   Ensure that the user kenny has access to the prod database:

   mysql> select * from prod.products;

   Ensure that the user myles has read access to the prod database:

   mysql> select * from prod.products;

   Ensure that the user aaron has write access to the prod database:

   mysql> INSERT INTO prod.orders (orderID,userName,orderType,purchaseDate) VALUES (4,'mike','laptop','2018-04-08');