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.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/TransactionManagementandSecurityGroup(["Transaction Management and Security"]) sql(("SQL")) -.-> sql/DatabaseManagementandOptimizationGroup(["Database Management and Optimization"]) mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) mysql(("MySQL")) -.-> mysql/DatabaseFunctionsandDataTypesGroup(["Database Functions and Data Types"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("Database Selection") mysql/DatabaseFunctionsandDataTypesGroup -.-> mysql/user("User Info Function") mysql/TransactionManagementandSecurityGroup -.-> mysql/identified_by("User Authentication") mysql/TransactionManagementandSecurityGroup -.-> mysql/grant_permission("Permission Granting") mysql/TransactionManagementandSecurityGroup -.-> mysql/revoke_permission("Permission Revocation") sql/DatabaseManagementandOptimizationGroup -.-> sql/security_permissions("Security and Permissions") subgraph Lab Skills mysql/use_database -.-> lab-418300{{"MySQL Database Access Challenge"}} mysql/user -.-> lab-418300{{"MySQL Database Access Challenge"}} mysql/identified_by -.-> lab-418300{{"MySQL Database Access Challenge"}} mysql/grant_permission -.-> lab-418300{{"MySQL Database Access Challenge"}} mysql/revoke_permission -.-> lab-418300{{"MySQL Database Access Challenge"}} sql/security_permissions -.-> lab-418300{{"MySQL Database Access Challenge"}} end

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.