Skip to main content

Creating Users and Managing Privileges in MySQL

Hands-On Lab

 

Photo of Matthew Pearson

Matthew Pearson

Linux Training Architect II in Content

Length

01:00:00

Difficulty

Intermediate

The ability to create users and manage privileges provides a granular level of security and access in MySQL. In this lab, you will be tasked with creating new users in the MySQL server and deleting users that no longer need access. Once the users are created, you will need to grant the appropriate privileges to each user so that they have access to the correct databases and tables within the 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 Users and Managing Privileges in MySQL

The Scenario

Our company has hired four new employees, and they need access to the MySQL server. As our company's database administrators, it's our job to create these user accounts and grant them specific privileges to databases and tables within the server. In addition to this, we will need to remove the user account of an employee who has recently left the company, and modify the privileges of a current employee who has been moved to a different position.

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

We're ready to proceed.

Create the Users

This will create the four users, and only allow logins from localhost:

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

Check if the new users got created with this:

mysql> SELECT User FROM mysql.user

Delete a User

Delete the user stosh using the DROP statement:

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

Grant Privileges on the Newly Created Users

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

View any user's privileges with this (substituting usernames and hosts with whatever we want to check):

mysql> SHOW GRANTS FOR 'user@host`;

Revoke a Privilege

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

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

Validate the Changes

If we want to manually test whether our changes have worked out and been implemented how we expected, we can log in as those users and attempt to run queries.

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

Conclusion

By altering privileges, and adding and dropping users, we were able to address each of the tasks laid out in the scenario. We're finished. Congratulations!