Skip to main content

Creating and Assigning Roles in MySQL

Hands-On Lab

 

Photo of Matthew Pearson

Matthew Pearson

Linux Training Architect II in Content

Length

01:00:00

Difficulty

Intermediate

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.

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Creating and Assigning Roles in MySQL

The Scenario

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

Getting Logged In

Use the credentials and server IP in the hands-on lab overview page to log into our lab server. Refer back to that page for other usernames and passwords as we need them. Once we're logged into the server itself, log into MySQL as root:

[cloud_user@host]$ mysql -u root -p

Create Three Roles for Access to the dev Database

Run following statement to create roles for the dev database:

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

Create Three Roles for Access to the prod Database

Run following statement to create roles for the prod database:

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

Check That They Were Created

To make sure our roles exist now, run:

mysql> SELECT User FROM mysql.user;

Grant Privileges to the dev Roles

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

Check Current Grant Privileges

To check that we granted permissions properly, run:

mysql> SHOW GRANTS FOR '<ROLE_NAME>';

Just stick whichever role name we're checking on in the query.

Grant Privileges to the prod Roles

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 dev Database Roles

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 prod Database Roles

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

We can verify which privileges a user has, as it pertains to a role they're in, by running something like:

mysql> SHOW GRANTS FOR 'aaron'@'localhost' USING dev_write;

That will show what privileges the aaron user has in the dev_write role.

Optionally, Validate the Newly Assigned Roles by Testing User Access

In order to run these tests, we'd have to log out of MySQL (because we're currently root) and then log back in as the user we're testing.

To ensure that the user corey does not have access to the prod database, we'd first become the corey MySQL user, and try running queries:

[clous_user@host]$ mysql -u corey -p

mysql> select * from prod.products;

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

mysql> select * from dev.products;

For these others, just log in first as the user in question, then try the queries.

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

Conclusion

We've explored some different things we can do with MySQL roles, and our company's server is in much better shape because of it. Congratulations!