Role-Based Access Control in MySQL: Users and Permissions

Almost every application needs to answer: “may this user do this?” The most common design is role-based access control (RBAC): users have roles, roles have permissions, and the code checks permissions.

This is about your application’s users. It is not the same as MySQL’s own accounts and roles, which control who can connect to the database server (Roles).

The tables

query.sql

SQL

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    UNIQUE KEY uq_users_email (email)
);

CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    UNIQUE KEY uq_roles_name (name)
);

CREATE TABLE permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL,
    UNIQUE KEY uq_permissions_code (code)
);

CREATE TABLE user_roles (
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    PRIMARY KEY (user_id, role_id),
    KEY idx_role (role_id),
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE
);

CREATE TABLE role_permissions (
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    KEY idx_permission (permission_id),
    FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions (id) ON DELETE CASCADE
);

Output

OUTPUT

Query OK, 0 rows affected, 1 warning

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Two junction tables (Many-to-Many): a user can have several roles, and a role has several permissions. A permission has a code that your application checks, such as article.edit.

Sample data

query.sql

SQL

INSERT INTO users (email) VALUES ('asha@example.com'), ('ben@example.com'), ('cara@example.com');
INSERT INTO roles (name) VALUES ('admin'), ('editor'), ('viewer');
INSERT INTO permissions (code) VALUES ('article.read'), ('article.edit'), ('article.publish'), ('user.manage');

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p
WHERE (r.name = 'viewer' AND p.code IN ('article.read'))
   OR (r.name = 'editor' AND p.code IN ('article.read', 'article.edit', 'article.publish'))
   OR (r.name = 'admin'  AND p.code IN ('article.read', 'article.edit', 'article.publish', 'user.manage'));

INSERT INTO user_roles (user_id, role_id)
SELECT u.id, r.id FROM users u JOIN roles r
WHERE (u.email = 'asha@example.com' AND r.name = 'admin')
   OR (u.email = 'ben@example.com' AND r.name IN ('editor', 'viewer'))
   OR (u.email = 'cara@example.com' AND r.name = 'viewer');

Output

OUTPUT

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 8 rows affected
Records: 8  Duplicates: 0  Warnings: 0

Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

“What can this user do?”

query.sql

SQL

SELECT u.email, GROUP_CONCAT(DISTINCT p.code ORDER BY p.code SEPARATOR ', ') AS permissions
FROM users u
JOIN user_roles ur ON ur.user_id = u.id
JOIN role_permissions rp ON rp.role_id = ur.role_id
JOIN permissions p ON p.id = rp.permission_id
WHERE u.is_active = 1
GROUP BY u.id, u.email
ORDER BY u.email;

Output

OUTPUT

+------------------+----------------------------------------------------------+
| email            | permissions                                              |
+------------------+----------------------------------------------------------+
| asha@example.com | article.edit, article.publish, article.read, user.manage |
| ben@example.com  | article.edit, article.publish, article.read              |
| cara@example.com | article.read                                             |
+------------------+----------------------------------------------------------+
3 rows in set

Ben has two roles, and the DISTINCT makes sure a permission granted by both is listed once.

“May this user do this?”

The check the application runs on every protected action:

query.sql

SQL

SELECT EXISTS (
    SELECT 1
    FROM users u
    JOIN user_roles ur ON ur.user_id = u.id
    JOIN role_permissions rp ON rp.role_id = ur.role_id
    JOIN permissions p ON p.id = rp.permission_id
    WHERE u.id = 3 AND u.is_active = 1 AND p.code = 'article.edit'
) AS may_edit_cara, EXISTS (
    SELECT 1
    FROM user_roles ur
    JOIN role_permissions rp ON rp.role_id = ur.role_id
    JOIN permissions p ON p.id = rp.permission_id
    WHERE ur.user_id = 2 AND p.code = 'article.publish'
) AS may_publish_ben;

Output

OUTPUT

+---------------+-----------------+
| may_edit_cara | may_publish_ben |
+---------------+-----------------+
|             0 |               1 |
+---------------+-----------------+
1 row in set

EXISTS stops at the first match, and the primary keys of the junction tables make each lookup fast. The permissions of a user rarely change, so applications often load them once per request (or cache them) instead of querying on every check.

Changing access

query.sql

SQL

-- promote Cara to editor, take the viewer role away
INSERT INTO user_roles (user_id, role_id) SELECT 3, id FROM roles WHERE name = 'editor';
DELETE FROM user_roles WHERE user_id = 3 AND role_id = (SELECT id FROM roles WHERE name = 'viewer');

SELECT r.name FROM user_roles ur JOIN roles r ON r.id = ur.role_id WHERE ur.user_id = 3;

Output

OUTPUT

+--------+
| name   |
+--------+
| editor |
+--------+
1 row in set

Because access is data, changing it needs no code change and no deployment.

Rules that make RBAC work

Check permissions, not role names, in the code: can('article.edit'), not hasRole('editor'). Then the meaning of “editor” can change without code changes.
Deny by default: no matching row means no access.
Assign roles to people, and permissions to roles. Avoid one-off permissions on users, which become impossible to review.
Check on the server, on every request. Hiding a button is not security (Never gate by client-side code).
Keep an audit trail of who changed roles (Auditing).
Disable accounts with is_active = 0 instead of deleting them, so that history stays intact.

When RBAC is not enough

“Editors can edit their own articles” depends on the row, not only on the role. Combine a permission (article.edit_own) with a row check in the query (WHERE author_id = :user). For rules that depend on teams or departments, add a team_id to the assignment, and filter on it (Multi-Tenant Data).

Do not mix up the two kinds of roles

	MySQL roles (`CREATE ROLE`)	Application roles (these tables)
Control	Who may use database objects	Who may do things in your app
Subject	Database accounts (`shop_app`, a developer)	Your users (thousands, or millions)
Stored in	The `mysql` system schema	Your own tables

Your web application connects to MySQL with one limited account. The application decides what each of its users may do.

lightbulb

Seed the roles in a migration

Roles and permissions are reference data that every environment needs. Create them in a migration, so that staging and production have the same ones (Schema Migrations).