Chapter 10: The Roles & Permissions System

Introduction: Role-Based Access Control (RBAC)

A critical part of any non-trivial application is controlling what users are allowed to see and do. A far more powerful and scalable solution than simple checks is Role-Based Access Control (RBAC), where we assign permissions to roles, and roles to users.

---

The Database Schema (In Detail)

Our RBAC system is built upon a foundation of interconnected tables. The structure is designed to be powerful and flexible, supporting two key advanced features: multi-tenancy and hierarchical roles.

Visualizing the Schema

The complete relationship between the tables is shown in the following ER Diagram.

Multi-Tenancy with Organizations

The Organizations table is the key to our multi-tenancy model. It allows us to scope users, roles, and permissions to a specific organization. This means a user who is an "Administrator" in one organization will have no special privileges in another.

Hierarchical Roles

The parent_role_id column in the Roles table enables role inheritance. This allows us to create a hierarchy where a "child" role automatically inherits all the permissions of its "parent." For example, an "Editor" role could inherit all the permissions of a "Contributor" role, plus its own additional permissions.

Complete Table Definitions

CREATE TABLE `Organizations` (
    `organization_id` INT AUTO_INCREMENT PRIMARY KEY,
    `organization_name` VARCHAR(255) NOT NULL
);

CREATE TABLE `Users` (
    `user_id` INT AUTO_INCREMENT PRIMARY KEY,
    `organization_id` INT NOT NULL,
    FOREIGN KEY (`organization_id`) REFERENCES `Organizations`(`organization_id`)
);

CREATE TABLE `Roles` (
    `role_id` INT AUTO_INCREMENT PRIMARY KEY,
    `organization_id` INT NOT NULL,
    `parent_role_id` INT NULL,
    `name` VARCHAR(255) NOT NULL,
    FOREIGN KEY (`organization_id`) REFERENCES `Organizations`(`organization_id`),
    FOREIGN KEY (`parent_role_id`) REFERENCES `Roles`(`role_id`)
);

-- ... and so on for Permissions, U2R, R2P tables ...

The PHP Implementation

With the database schema established, we turn to the PHP code that will interact with it. To keep our data access logic clean, we use the Repository Pattern.

The PermissionRepository

The PermissionRepository contains methods for managing permissions. Its most important method gets all permissions for a user, accounting for inherited permissions from parent roles.

PHP

<?php
class PermissionRepository
{
private DataBase $db;
public function __construct(DataBase $db) { $this->db = $db; }

public function getPermissionsByUserId(int $userId): array
{
    $results = $this->db->spma('rbac_getPermissionsForUser', [$userId]);
    return array_column($results, 'permissionName');
}

}

#The RoleRepository

> The RoleRepository handles all logic related to roles, such as fetching the roles assigned to a specific user.

> PHP
> ```php
<?php
class RoleRepository
{
    private DataBase $db;
    public function __construct(DataBase $db) { $this->db = $db; }

    public function getRolesByUserId(int $userId): array
    {
        $results = $this->db->spma('rbac_getRolesForUser', [$userId]);
        return array_column($results, 'roleName');
    }
}

Conclusion: Secure, Scalable Access Control

By combining a flexible database schema with the clean separation of the Repository Pattern, we have built a Role-Based Access Control system that is powerful, secure, and easy to maintain. The database provides a single source of truth for all permissions, and the controller provides a single, consistent point of enforcement.