Creating Users and Managing Privileges in MySQL

Once logged in to the MySQL server, run the following command to create the users:

mysql> CREATE USER 'corey'@'localhost' IDENTIFIED BY 'Linux4you!','will'@'localhost' IDENTIFIED BY 'Linux4you!','mike'@'localhost' IDENTIFIED BY 'Linux4you!','myles'@'localhost' IDENTIFIED BY 'Linux4you!';

Delete the user stosh using the DROP statement:

mysql>DROP USER 'stosh'@'localhost';

Grant all privileges on the dev database to the corey user:

mysql> GRANT ALL ON dev.* TO 'corey'@'localhost';

Grant INSERT and SELECT privileges on the products table in the dev database to will:

mysql> GRANT SELECT, INSERT ON dev.products TO 'will'@'localhost';

Grant ALL privileges on the prod database to the mike user:

mysql> GRANT ALL ON prod.* TO 'mike'@'localhost';

Grant SELECT privileges on the products table in the prod database to myles user:

mysql> GRANT SELECT ON prod.products TO 'myles'@'localhost';

Revoke the INSERT privilege from the kenny user running the following command:

mysql> REVOKE INSERT ON prod.orders FROM 'kenny'@'localhost';

Ensure that the user corey does not have access to the prod database. Run the following as the corey user:

mysql> SELECT * FROM prod.orders;

Ensure that the user will does not have access to the orders table on the dev database. Run the following as the will user:

mysql> SELECT * FROM dev.orders;

Ensure that the user mike does not have access to the dev database. Run the following as the mike user:

mysql> SELECT * FROM dev.orders;

Ensure that the user myles does not have access to the orders table in the prod database. Run the following as the myles user:

mysql> SELECT * FROM prod.orders;

Ensure that the user kenny does not have INSERT privileges on the orders table in the prod database. Run the following as the kenny user:

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