Role-based access in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is role-based access control (RBAC) in databases?

RBAC is a method to restrict database access based on user roles. Each role has specific permissions, and users get access by being assigned to roles.

beginner

How do you create a role in MySQL?

Use the statement: CREATE ROLE 'role_name'; to create a new role.

intermediate

How do you assign privileges to a role in MySQL?

Use GRANT privilege ON database.table TO 'role_name'; to give permissions to a role.

intermediate

How do you assign a role to a user in MySQL?

Use GRANT 'role_name' TO 'user'@'host'; to assign a role to a user.

intermediate

What command activates a role for the current session in MySQL?

Use SET ROLE 'role_name'; to activate the role's privileges in your session.

Which command creates a new role in MySQL?

A SET ROLE 'manager';

B CREATE USER 'manager';

C GRANT ROLE 'manager';

D CREATE ROLE 'manager';

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

How do you give SELECT permission on a table to a role?

A GRANT SELECT ON database.table TO 'role_name';

B CREATE ROLE SELECT ON database.table;

C SET ROLE SELECT ON database.table;

D ASSIGN SELECT TO 'role_name';

GRANT SELECT ON database.table TO 'role_name'; assigns SELECT permission to the role.

What command assigns a role to a user?

A CREATE USER 'user' WITH ROLE 'role_name';

B SET ROLE 'role_name' FOR 'user';

C GRANT 'role_name' TO 'user'@'host';

D ASSIGN ROLE 'role_name' TO 'user';

GRANT 'role_name' TO 'user'@'host'; assigns the role to the user.

How does a user activate a role's privileges in their session?

A CREATE ROLE 'role_name';

B SET ROLE 'role_name';

C GRANT ROLE 'role_name';

D USE ROLE 'role_name';

SET ROLE 'role_name'; activates the role's privileges for the session.

Which of these is NOT true about roles in MySQL?

A Roles automatically activate for all users without SET ROLE.

B Users can be assigned multiple roles.

C Roles can have privileges assigned.

D Roles simplify privilege management.

Roles must be activated with SET ROLE; they do not activate automatically.

Explain how role-based access control works in MySQL and why it is useful.

Think about how grouping permissions into roles helps manage many users.

Describe the steps to create a role, assign privileges, and enable a user to use that role in MySQL.

Focus on the sequence of commands from role creation to user activation.