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.