Role-based access in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Check user privileges with roles

Given a MySQL database with roles assigned to users, what will be the output of the following query?

SELECT user, host, GROUP_CONCAT(DISTINCT role SEPARATOR ', ') AS roles FROM mysql.role_edges GROUP BY user, host;

MySQL

SELECT user, host, GROUP_CONCAT(DISTINCT role SEPARATOR ', ') AS roles FROM mysql.role_edges GROUP BY user, host;

A Lists all roles in the database without grouping by user

B Returns an error because GROUP_CONCAT cannot be used with DISTINCT

C Lists each user and host with a comma-separated list of roles assigned to them

D Returns only users without any roles assigned

💡 Hint

GROUP_CONCAT aggregates values per group; DISTINCT avoids duplicates.

The query groups rows by user and host, then concatenates distinct roles for each user-host pair, showing their assigned roles.

🧠 Conceptual

intermediate

Understanding role inheritance in MySQL

In MySQL role-based access control, if role A is granted to role B, and role B is granted to user U, which of the following is true?

A User U inherits privileges from both role A and role B

B User U only inherits privileges from role B, not role A

C User U must be granted role A directly to inherit its privileges

D Role inheritance is not supported in MySQL

💡 Hint

Think about how roles can be nested in MySQL.

MySQL supports role inheritance, so granting role A to role B and role B to user U means U inherits privileges from both roles.

📝 Syntax

advanced

Identify the correct syntax to create a role and grant privileges

Which of the following MySQL statements correctly creates a role named 'report_viewer' and grants SELECT privilege on the 'sales' database to it?

A CREATE ROLE report_viewer; GRANT SELECT ON sales TO report_viewer;

B CREATE ROLE report_viewer; GRANT SELECT ON sales.* TO report_viewer;

C CREATE ROLE 'report_viewer'; GRANT SELECT ON sales.* TO 'report_viewer';

D CREATE ROLE 'report_viewer'; GRANT SELECT ON sales TO 'report_viewer';

💡 Hint

Role names are identifiers, not strings; privileges must specify database and tables.

In MySQL, role names are identifiers without quotes; privileges must specify database and tables with .* suffix.

❓ optimization

advanced

Optimizing role privilege checks

You want to quickly check all privileges a user has through roles in MySQL. Which query is the most efficient to get all privileges for user 'alice' including inherited roles?

A SELECT * FROM information_schema.role_table_grants WHERE grantee IN (SELECT role FROM mysql.role_edges WHERE user = 'alice') OR grantee = 'alice';

B SELECT * FROM mysql.user WHERE user = 'alice';

C SELECT * FROM mysql.role_edges WHERE user = 'alice';

D SELECT * FROM information_schema.role_table_grants WHERE grantee = 'alice';

💡 Hint

Consider roles assigned to the user and privileges granted to those roles.

Option A checks privileges granted directly to the user and to roles assigned to the user, covering inheritance efficiently.

🔧 Debug

expert

Debugging role assignment failure

A DBA runs:

GRANT 'data_analyst' TO 'bob'@'localhost';

But receives an error. What is the cause?

A GRANT cannot be used to assign roles

B User 'bob'@'localhost' does not exist

C Role 'data_analyst' does not exist

D Role name should not be in quotes in GRANT statement

💡 Hint

Check syntax for role names in GRANT statements.

In MySQL, role names are identifiers and should not be quoted in GRANT statements; quoting causes syntax error.

Practice

1. What is the main purpose of role-based access control (RBAC) in MySQL?

easy

A. To store user passwords securely

B. To group permissions into roles and assign them to users

C. To optimize query performance

D. To backup the database automatically

Solution

1. Step 1: Understand role-based access control concept

   RBAC groups permissions into roles instead of assigning them individually to users.

2. Step 2: Identify the main purpose in MySQL context

   Assigning roles to users simplifies permission management and improves security.

3. Final Answer:

   To group permissions into roles and assign them to users -> Option B

4. Quick Check:

   RBAC = Group permissions + assign roles [OK]

Hint: RBAC groups permissions, then assigns roles to users [OK]

Common Mistakes:

• Confusing RBAC with password storage
• Thinking RBAC improves query speed
• Assuming RBAC is for backups

2. Which of the following is the correct syntax to create a role named manager in MySQL?

easy

A. CREATE ROLE manager;

B. MAKE ROLE manager;

C. ADD ROLE manager;

D. NEW ROLE manager;

Solution

1. Step 1: Recall MySQL syntax for creating roles

   The correct command to create a role is CREATE ROLE role_name;.

2. Step 2: Match syntax with options

   Only CREATE ROLE manager; uses the correct CREATE ROLE syntax.

3. Final Answer:

   CREATE ROLE manager; -> Option A

4. Quick Check:

   CREATE ROLE = create new role [OK]

Hint: Use CREATE ROLE to make a new role in MySQL [OK]

Common Mistakes:

• Using MAKE ROLE instead of CREATE ROLE
• Using ADD ROLE which is invalid
• Using NEW ROLE which is not a MySQL command

3. Given the following commands:

CREATE ROLE analyst;
GRANT SELECT ON sales TO analyst;
GRANT analyst TO alice;

What permission does user alice have on the sales table?

medium

A. SELECT permission on sales

B. INSERT permission on sales

C. No permissions on sales

D. ALL permissions on sales

Solution

1. Step 1: Analyze role creation and permission grant

   The role analyst is created and given SELECT permission on the sales table.

2. Step 2: Check user role assignment

   User alice is granted the analyst role, so she inherits its permissions.

3. Final Answer:

   SELECT permission on sales -> Option A

4. Quick Check:

   User with role gets role's permissions [OK]

Hint: User inherits permissions from assigned roles [OK]

Common Mistakes:

• Assuming user gets INSERT without grant
• Thinking user has no permissions without direct grant
• Assuming ALL permissions without explicit grant

4. Consider this MySQL command:

GRANT SELECT ON employees TO 'john';

Why might this command fail when using role-based access?

medium

A. Because 'john' is not a valid username format

B. Because SELECT is not a valid permission

C. Because GRANT cannot be used on tables

D. Because permissions should be granted to roles, not directly to users

Solution

1. Step 1: Understand role-based access best practice

   In RBAC, permissions are granted to roles, then roles are assigned to users.

2. Step 2: Identify why direct grant to user may fail

   Granting permissions directly to users bypasses roles and may be restricted or discouraged.

3. Final Answer:

   Because permissions should be granted to roles, not directly to users -> Option D

4. Quick Check:

   Grant permissions to roles, assign roles to users [OK]

Hint: Grant permissions to roles, not users directly [OK]

Common Mistakes:

• Thinking SELECT is invalid permission
• Assuming username format is wrong
• Believing GRANT can't be used on tables

5. You want to create a role developer with INSERT and UPDATE permissions on the projects table, then assign it to user emma. Which sequence of commands correctly achieves this?

hard

A. CREATE ROLE developer; GRANT SELECT ON projects TO developer; GRANT developer TO emma;

B. GRANT INSERT, UPDATE ON projects TO emma; CREATE ROLE developer; GRANT developer TO emma;

C. CREATE ROLE developer; GRANT INSERT, UPDATE ON projects TO developer; GRANT developer TO emma;

D. CREATE USER developer; GRANT INSERT, UPDATE ON projects TO developer; GRANT developer TO emma;

Solution

1. Step 1: Create the role named developer

   Use CREATE ROLE developer; to define the role.

2. Step 2: Grant INSERT and UPDATE permissions on projects to the role

   Use GRANT INSERT, UPDATE ON projects TO developer; to assign permissions.

3. Step 3: Assign the role to user emma

   Use GRANT developer TO emma; to give emma the role's permissions.

4. Final Answer:

   CREATE ROLE developer; GRANT INSERT, UPDATE ON projects TO developer; GRANT developer TO emma; -> Option C

5. Quick Check:

   Create role, grant permissions, assign role [OK]

Hint: Create role, grant permissions, then assign role to user [OK]

Common Mistakes:

• Granting permissions directly to user instead of role
• Using CREATE USER instead of CREATE ROLE
• Granting wrong permissions like SELECT instead of INSERT, UPDATE