Role-based access in MySQL - Step-by-Step Execution

Concept Flow - Role-based access

Create Roles

↓

Assign Privileges to Roles

↓

Create Users

↓

Assign Roles to Users

↓

User Accesses Database

↓

Check User Roles

↓

Grant or Deny Access Based on Role

This flow shows how roles are created, given privileges, assigned to users, and then used to control access.

Execution Sample

MySQL

CREATE ROLE reader;
GRANT SELECT ON employees TO reader;
CREATE USER 'alice'@'localhost' IDENTIFIED BY 'pass';
GRANT 'reader' TO 'alice'@'localhost';
SET DEFAULT ROLE reader TO 'alice'@'localhost';
SELECT * FROM employees;

This code creates a role with select permission, creates a user, assigns the role, and then the user queries the table.

Execution Table

Step	Action	Evaluation	Result
1	CREATE ROLE reader;	Role 'reader' does not exist	Role 'reader' created
2	GRANT SELECT ON employees TO reader;	Role 'reader' exists	SELECT privilege granted on employees to reader
3	CREATE USER 'alice'@'localhost' IDENTIFIED BY 'pass';	User 'alice'@'localhost' does not exist	User 'alice'@'localhost' created
4	GRANT reader TO 'alice'@'localhost';	Role 'reader' and user exist	Role 'reader' granted to user 'alice'@'localhost'
5	SET DEFAULT ROLE reader TO 'alice'@'localhost';	User and role exist	Default role for 'alice' set to 'reader'
6	SELECT * FROM employees; -- executed by alice	User 'alice' has role 'reader' with SELECT privilege	Query succeeds, returns employee rows
7	SELECT * FROM employees; -- executed by user without role	User lacks SELECT privilege	Query denied

💡 Execution stops after query success or denial based on role privileges.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6
Roles	{}	{reader}	{reader}	{reader}	{reader}	{reader}	{reader}
Privileges	{}	{}	{reader: SELECT on employees}	{reader: SELECT on employees}	{reader: SELECT on employees}	{reader: SELECT on employees}	{reader: SELECT on employees}
Users	{}	{}	{}	{'alice'@'localhost': {}}	{'alice'@'localhost': {reader}}	{'alice'@'localhost': {reader}}	{'alice'@'localhost': {reader}}
Default Role	{}	{}	{}	{}	{}	{'alice'@'localhost': reader}	{'alice'@'localhost': reader}

Key Moments - 3 Insights

Why can't a user access a table even if the table exists?

Because the user does not have a role with the required privileges. See execution_table step 7 where the query is denied due to missing SELECT privilege.

What happens if a role is created but no privileges are granted to it?

The role exists but grants no access. Users with that role cannot perform any actions. See variable_tracker Privileges after Step 1 and 2 for difference.

Why do we set a default role for a user?

The default role determines which privileges apply automatically when the user connects. Without it, the user might have no active privileges. See execution_table step 5.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the user 'alice' created?

A Step 1

B Step 4

C Step 3

D Step 5

💡 Hint

Check the 'Action' column for user creation commands.

According to variable_tracker, what is the default role of 'alice' after Step 5?

A reader

B No default role

C admin

D guest

💡 Hint

Look at the 'Default Role' row after Step 5.

In execution_table, what happens at Step 7 when a user without the role tries to SELECT?

A Query succeeds

B Query denied

C Role is automatically assigned

D User is created

💡 Hint

See the 'Result' column for Step 7.

Concept Snapshot

Role-based access in MySQL:
- CREATE ROLE to define roles
- GRANT privileges to roles
- CREATE USER to add users
- GRANT role TO user to assign roles
- SET DEFAULT ROLE to activate roles on login
- User actions checked against active roles' privileges

Full Transcript

Role-based access control in MySQL involves creating roles, assigning privileges to those roles, creating users, and then granting roles to users. When a user connects, their default role determines what actions they can perform. For example, creating a role 'reader' with SELECT privilege on a table, then creating a user 'alice' and granting her the 'reader' role, allows 'alice' to query the table. If a user lacks the role or privileges, their queries are denied. This method simplifies managing permissions by grouping privileges into roles and assigning roles to users.