Sospeter Mong'are

Posted on Apr 29

Implementing Role-Based Access Control (RBAC) in MySQL

#mysql #programming #beginners #database

Introduction

Role-Based Access Control (RBAC) is a security model that restricts system access based on user roles. It simplifies permission management by grouping privileges into roles, which are then assigned to users. This article provides a complete MySQL implementation for an RBAC system, including core tables, relationships, and optional enhancements.

Core RBAC Tables

1. Users Table

Stores user account information.

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

2. Roles Table

Defines different roles (e.g., Admin, Manager, User).

CREATE TABLE roles (
    role_id INT AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3. Permissions Table

Lists all possible actions (e.g., users.create, products.delete).

CREATE TABLE permissions (
    permission_id INT AUTO_INCREMENT PRIMARY KEY,
    permission_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Relationship Tables

4. User-Roles Assignment (Many-to-Many)

Links users to their assigned roles.

CREATE TABLE user_roles (
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE CASCADE
);

5. Role-Permissions Assignment (Many-to-Many)

Specifies which permissions each role has.

CREATE TABLE role_permissions (
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(permission_id) ON DELETE CASCADE
);

Optional Enhancements

6. Permission Categories (For Better Organization)

Groups permissions into logical categories (e.g., "User Management," "Inventory").

CREATE TABLE permission_categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT
);

ALTER TABLE permissions ADD COLUMN category_id INT;
ALTER TABLE permissions ADD FOREIGN KEY (category_id) REFERENCES permission_categories(category_id);

7. Audit Log (For Tracking Access)

Records user actions for security and compliance.

CREATE TABLE access_audit_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50),
    entity_id INT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL
);

Sample Data Setup

Inserting Roles and Permissions

-- Add roles
INSERT INTO roles (role_name, description) VALUES 
('admin', 'Full system access'),
('manager', 'Can manage products and users'),
('user', 'Basic access');

-- Add permissions
INSERT INTO permissions (permission_name, description) VALUES
('users.create', 'Create new users'),
('users.read', 'View users'),
('products.edit', 'Modify products');

-- Assign permissions to roles
INSERT INTO role_permissions (role_id, permission_id) VALUES
(1, 1), (1, 2), (1, 3), -- Admin has all permissions
(2, 2), (2, 3); -- Manager can view users and edit products

Assign Roles to Users

INSERT INTO user_roles (user_id, role_id) VALUES
(1, 1), -- User 1 is an Admin
(2, 2); -- User 2 is a Manager

Querying Permissions

Check if a User Has a Specific Permission

SELECT COUNT(*) > 0 AS has_permission
FROM users u
JOIN user_roles ur ON u.user_id = ur.user_id
JOIN role_permissions rp ON ur.role_id = rp.role_id
JOIN permissions p ON rp.permission_id = p.permission_id
WHERE u.user_id = 1 AND p.permission_name = 'users.create';

List All Permissions for a User

SELECT p.permission_name
FROM users u
JOIN user_roles ur ON u.user_id = ur.user_id
JOIN role_permissions rp ON ur.role_id = rp.role_id
JOIN permissions p ON rp.permission_id = p.permission_id
WHERE u.user_id = 1;

Best Practices

Use CASCADE Deletes
- Ensures that when a user or role is deleted, their assignments are automatically removed.
Index Frequently Queried Columns
- Add indexes on user_id, role_id, and permission_name for faster lookups.
Regularly Audit Permissions
- Use the access_audit_log to monitor suspicious activity.
Avoid Over-Permissioning
- Follow the Principle of Least Privilege—only grant necessary permissions.

Conclusion

This MySQL RBAC structure provides a scalable, secure, and flexible way to manage user permissions. By separating roles and permissions, you can easily adjust access levels without modifying individual user settings.

For smaller applications, you can omit optional tables like permission_categories and audit_log. For enterprise systems, consider adding hierarchical roles (where roles inherit permissions from parent roles).

DEV Community