Dynamic Role-Permission Matrix in Laravel (MySQL + Spatie Permission)

#laravel

When you're managing role-based access control (RBAC) in a Laravel app using the excellent Spatie Laravel Permission, you might wonder:

“How can I generate a matrix view of which roles have which permissions?”

In this post, we’ll show you how to pivot your permissions table dynamically in MySQL — perfect for reporting, admin dashboards, audits, or debugging.

🧱 The Structure (Spatie Defaults)

By default, the Spatie package uses the following tables:

permissions
roles
role_has_permissions

This is a classic many-to-many relationship.

🎯 The Goal

We want to generate a matrix that looks like this:

permission_name	Admin	Editor	Student
manage_users	Y
view_dashboard	Y	Y	Y
submit_feedback			Y

Each row is a permission, and each column is a role.

💻 Solution: Dynamic SQL in MySQL

MySQL doesn’t support a native PIVOT, but we can emulate it using GROUP_CONCAT + dynamic CASE statements.

Here’s the SQL to get it done:

SET @sql = NULL;

-- Step 1: Dynamically build columns from roles
SELECT GROUP_CONCAT(
    DISTINCT
    CONCAT(
        'MAX(CASE WHEN r.name = ''', name, ''' THEN ''Y'' ELSE '''' END) AS `', name, '`'
    )
) INTO @columns
FROM roles;

-- Step 2: Build the full SQL statement
SET @sql = CONCAT('
    SELECT 
        p.name AS permission_name, ', @columns, '
    FROM permissions p
    LEFT JOIN role_has_permissions rp ON p.id = rp.permission_id
    LEFT JOIN roles r ON rp.role_id = r.id
    GROUP BY p.name
    ORDER BY p.name;
');

-- Step 3: Execute
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

📋 Explanation

GROUP_CONCAT dynamically creates column expressions like:

  MAX(CASE WHEN r.name = 'Admin' THEN 'Y' ELSE '' END) AS `Admin`

The final result is a pivoted matrix of permissions vs roles.
'Y' marks a role that has the corresponding permission.

🧪 Want to Flip It? Roles as Rows?

Just reverse the logic:

Pivot on p.name
GROUP BY r.name
Show permissions as columns

🧩 Bonus: Laravel Integration

You can easily integrate this into Laravel by running the SQL through DB:

DB::unprepared($yourSql);

Or if you want to show it on a report page, execute and render as a table.

✅ Final Thoughts

The Spatie Permission package is powerful, and with a little SQL wizardry, you can create a permission matrix that's both informative and dynamic. This is useful for: