Practitioner Level

Creating and Assigning Roles in MySQL

Pricing
1 hour
  • 7 Learning Objectives

About this Hands-on Lab

Managing access and granting privileges to individual users can be very time consuming and cumbersome. That is why MySQL provides the ability to create roles. Roles are granted privileges, then roles are assigned to users. The users inherit the privileges of the roles. In this lab, we’ll create a set of roles and grant those roles specific privileges. Once the roles have been created, we’ll assign them to the appropriate users, in order to grant access to databases and tables within the MySQL server.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

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';

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';

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';

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';

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';

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';

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');

Contact SalesSee Pricing

Additional Resources

As your company has continued to grow, maintaining user access and privileges has become difficult to manage. Because of this, your team has been tasked with creating roles that can be used to grant users access to objects on the MySQL server.

First you will need to create three roles for access to the dev database - dev_all, dev_read, and dev_write. The dev_all role should be granted all privileges for the dev database on all tables. The dev_read roll should be granted the SELECT privilege for the dev database on all tables. The dev_write roll should be granted the SELECT, UPDATE, and DELETE privileges for the dev database on all tables.

Next you will need to create three roles for the prod database - prod_all, prod_read, and prod_write. The prod_all role should be granted all privileges for the prod database on all tables. The prod_read roll should be granted the SELECT privilege for the prod database on all tables. The prod_write roll should be granted the SELECT, UPDATE, and DELETE privileges for the prod database on all tables.

Once the roles have been given the proper access, you will need to assign these roles to users as follows: the user corey should be granted the dev_all role, the user will should be granted the dev_read roll, the user aaron should be granted the dev_read and dev_write roles, the user kenny should be granted the prod_all role, the user myles should be granted the prod_read role, and the user mike should be granted the prod_read and prod_write roles. Once the roles have been assigned, ensure that they are set as the default roles for the users.

Additional Information:

  • Tasks in the MySQL database should be performed as the root user (unless otherwise specified).
  • Use the following password to login to the MySQL server as the root user: Linux4me!
  • Use the following password to login to the MySQL server as a user other than root: Linux4you!
  • Do not update the password for the MySQL root user as it is used for grading purposes.

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!

Don't have an account?

Get Started
Who’s going to be learning?
MyselfMy Organization