Role-based access in MySQL - Mini Project: Build & Apply

Role-based Access Control Setup in MySQL

📖 Scenario: You are setting up a simple role-based access control system for a small company's database. Different users will have different roles such as 'manager' and 'employee', and each role will have specific permissions on a table called projects.

🎯 Goal: Build a MySQL setup that creates roles, assigns privileges to those roles, and then grants those roles to users. This will help control who can read or modify the projects table.

📋 What You'll Learn

Create a role called manager_role

Create a role called employee_role

Grant SELECT and UPDATE privileges on the projects table to manager_role

Grant only SELECT privilege on the projects table to employee_role

Create two users: alice and bob

Grant manager_role to alice

Grant employee_role to bob

💡 Why This Matters

🌍 Real World

Role-based access control is used in companies to manage who can see or change data in databases securely.

💼 Career

Database administrators and developers use roles to simplify permission management and improve security.

1

Create roles manager_role and employee_role

Write SQL statements to create two roles named manager_role and employee_role.

MySQL

-- Your code here to create roles manager_role and employee_role

Need a hint?

Use CREATE ROLE role_name; to create each role.

2

Grant privileges to the roles

Grant SELECT and UPDATE privileges on the projects table to manager_role, and grant only SELECT privilege on the projects table to employee_role.

MySQL

-- Your code here to grant privileges to manager_role and employee_role

Need a hint?

Use GRANT privileges ON table TO role_name; to assign privileges.

3

Create users alice and bob

Create two users named alice and bob with passwords 'alicepass' and 'bobpass' respectively.

MySQL

-- Your code here to create users alice and bob with passwords

Need a hint?

Use CREATE USER 'username'@'host' IDENTIFIED BY 'password'; to create users.

4

Grant roles to users

Grant the role manager_role to user alice and the role employee_role to user bob.

MySQL

-- Your code here to grant roles to alice and bob

Need a hint?

Use GRANT role_name TO 'username'@'host'; to assign roles to users.