MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE users (
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(255),
	PRIMARY KEY (id)
);

CREATE TABLE roles (
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(255),
	parent_id INT,
	PRIMARY KEY (id)
);

CREATE TABLE user_roles (
	id INT NOT NULL AUTO_INCREMENT,
	user_id INT,
	role_id INT,
	PRIMARY KEY (id)
);

INSERT INTO users(name) VALUES ('Alice'),('Bob'),('Charlie');

INSERT INTO roles(name, parent_id) VALUES
('superuser',null),
('admin',1),
('ceo',1),
('employee', null);

INSERT INTO user_roles(user_id, role_id) VALUES
(1,2),
(2,3),
(3,4);

SELECT * FROM users
WHERE EXISTS(
	SELECT 1 FROM roles
	INNER JOIN user_roles ON roles.id = user_roles.role_id
	WHERE users.id = user_roles.user_id
	AND EXISTS(
    	WITH RECURSIVE cte AS (
        	SELECT * FROM roles as roles_recursive
			WHERE roles_recursive.id = roles.id
			UNION ALL
			SELECT roles_recursive.* FROM roles as roles_recursive
			INNER JOIN cte ON cte.parent_id = roles_recursive.id
        )
		SELECT 1 from cte WHERE name='superuser'
    )
);

id	name
1	Alice
2	Bob