MySQL User Management Challenge - Configure Database Access Rights

Introduction

A small company needs to set up database access for their new marketing team. As the database administrator, you need to create a new user account that will allow the marketing team to view and analyze customer data, but not modify it.

This is a Challenge, which differs from a Guided Lab in that you need to try to complete the challenge task independently, rather than following the steps of a lab to learn. Challenges are usually a bit difficult. If you find it difficult, you can discuss with Labby or check the solution. Historical data shows that this is a beginner level challenge with a 98% pass rate. It has received a 100% positive review rate from learners.

Create Marketing Analyst Access

Tasks

Connect to MySQL as the root user
Create a new user named marketing_analyst that can only connect from localhost
Grant this user permission to view (SELECT) data from all tables in the marketing_db database
Ensure the user has a secure password

Requirements

All operations must be performed in the ~/project directory
The username must be exactly marketing_analyst
The user must only be able to connect from localhost
The user must only have SELECT privileges on marketing_db
The password must be at least 8 characters long

Example

After setting up the user correctly, when you check their privileges, you should see output similar to this:

SHOW GRANTS FOR 'marketing_analyst'@'localhost';

+--------------------------------------------------------------------------------------------------------------------------+
| Grants for marketing_analyst@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `marketing_analyst`@`localhost` IDENTIFIED BY PASSWORD '*63CC12793CD9D5CB64C4FED01CC3D4DE25848489' |
| GRANT SELECT ON `marketing_db`.* TO `marketing_analyst`@`localhost`                                                      |
+--------------------------------------------------------------------------------------------------------------------------+

✨ Check Solution and Practice

Summary

In this challenge, you practiced creating a MySQL user with specific access restrictions. The skills demonstrated include creating a user account, setting up connection restrictions, and granting appropriate privileges at the database level. These fundamental security practices are essential for maintaining proper access control in a database system.

MySQL Database Access Challenge