Simple User Role Management System With PHP MySQL (Free Download)

Welcome to a tutorial on how to create a user role management system with PHP and MySQL. So you have a project that needs to identify and restrict what each user is able to do? Creating a permissions structure is often quite a grueling task and pain to integrate… But we shall walk through a simple permissions structure in this guide, step-by-step. Read on!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want the code and skip the tutorial.

 

TABLE OF CONTENTS

Download & Notes The Database How To Integrate
Useful Bits & Links The End

 

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

QUICK NOTES

If you prefer to “quickstart” and trace the code on your own:

  • Create a database and import all the 1-XYZ.sql files.
  • Change the database settings in 2a-core.php to your own.
  • Follow along 2b-login.php and 2c-protection.php to see how it works.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

PART A) THE DATABASE

All right, let us now get started with the foundations of the system – The database.

 

A1) TABLES OVERVIEW

There are 4 tables involved in this project:

  • Users – Captain Obvious. The list of users and their roles.
  • Roles – Names of the roles. For example, administrators, editors, users, subscribers, etc…
  • Permissions – Actions that require permission. For example, show the list of users, add users, update users, etc…
  • Roles-Permissions – To specify which role has which permissions.

 

A2) USERS TABLES

1a-users.sql
CREATE TABLE `users` (
  `user_email` varchar(255) NOT NULL,
  `user_password` varchar(255) NOT NULL,
  `role_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_email`);
Field Description
user_email User’s email, primary key (to prevent duplicate registrations).
user_password User’s password.
role_id User’s role.

If you don’t already have a user table, here’s a super simple one. If you already have an existing table – Just add a role_id and assign a role to your users.

 

 

A2) ROLES TABLE

1b-database.sql
CREATE TABLE `roles` (
  `role_id` int(11) NOT NULL,
  `role_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
ALTER TABLE `roles`
  ADD PRIMARY KEY (`role_id`),
  ADD UNIQUE KEY `role_name` (`role_name`);
 
ALTER TABLE `roles`
  MODIFY `role_id` int(11) NOT NULL AUTO_INCREMENT; 
Field Description
role_id Role ID, primary key.
role_name Name of the role.

 

A3) PERMISSIONS TABLES

1b-database.sql
CREATE TABLE `permissions` (
  `perm_id` int(11) NOT NULL,
  `perm_mod` varchar(5) NOT NULL,
  `perm_desc` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `permissions`
  ADD PRIMARY KEY (`perm_id`),
  ADD KEY `perm_mod` (`perm_mod`);

ALTER TABLE `permissions`
  MODIFY `perm_id` int(11) NOT NULL AUTO_INCREMENT;
Field Description
perm_id Permission ID, primary key.
perm_mod Module code. For example “USR” for users, “INV” for inventory. We don’t actually need this field, but it helps in better management. For example, SELECT * FROM `permissions` WHERRE `perm_mod`="USR" will get all user-related permissions.
perm_desc Permission description.

 

A4) ROLE PERMISSIONS TABLES

1b-database.sql
CREATE TABLE `roles_permissions` (
  `role_id` int(11) NOT NULL,
  `perm_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
ALTER TABLE `roles_permissions`
  ADD PRIMARY KEY (`role_id`,`perm_id`);
Field Description
role_id Role ID, partial primary key.
perm_id Permission ID, partial primary key.

 

 

A5) AN EXAMPLE

If you are still confused, here’s an example of how it works:

1c-dummy.sql
INSERT INTO `roles` (`role_id`, `role_name`) VALUES
(1, 'Administrator'),
(2, 'Power User');

First, we have two roles – “Administrator” and “Power User”.

1c-dummy.sql
INSERT INTO `permissions` (`perm_id`, `perm_mod`, `perm_desc`) VALUES
(1, 'USR', 'Access users'),
(2, 'USR', 'Create new users'),
(3, 'USR', 'Update users'),
(4, 'USR', 'Delete users');

Then, we define all the actions that require permissions.

1c-dummy.sql
INSERT INTO `roles_permissions` (`role_id`, `perm_id`) VALUES
(1, 1),
(1, 2),
(1, 3),
(1, 4),
(2, 1);

Specify the permissions that each role has.

  • Administrators can access, create, update, and delete users.
  • Power users can only access the user list.
1c-dummy.sql
INSERT INTO `users` (`user_email`, `user_password`, `role_id`) VALUES
('john@doe.com', '123456', 1),
('jane@doe.com', '123456', 2);

Lastly, we create the users and assign their roles.

 

PART B) HOW TO INTEGRATE

With the database foundations established, we shall now walk through how to put the permissions into the scripts.

 

B1) THE CORE SCRIPT

2a-core.php
<?php
// (A) DATABASE CLASS
class DB {
  // (A1) CONSTRUCTOR - CONNECT TO THE DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error;
  function __construct () {
    try {
      $this->pdo = new PDO(
        "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
        DB_USER, DB_PASSWORD, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
      ]);
    } catch (Exception $ex) { exit($ex->getMessage()); }
  }
 
  // (A2) DESTRUCTOR - CLOSE DATABSE CONNECTION
  function __destruct () {
    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
  }
 
  // (A3) EXECUTE SQL QUERY
  function exec ($sql, $data=null) {
    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($data);
      return true;
    } catch (Exception $ex) {
      $this->error = $ex->getMessage();
      return false;
    }
  }
 
  // (A4) FETCH (SINGLE ROW)
  function fetch ($sql, $data=null) {
    if ($this->exec($sql, $data) === false) { return false; }
    return $this->stmt->fetch();
  }
 
  // (A5) FETCH ALL (MULTIPLE ROWS)
  function fetchAll ($sql, $data=null) {
    if ($this->exec($sql, $data) === false) { return false; }
    return $this->stmt->fetchAll();
  }
}
 
// (B) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8");
define("DB_USER", "root");
define("DB_PASSWORD", "");
 
// (C) START!
$DB = new DB();
session_start();

I know this looks massive, but keep calm and look carefully:

  1. A database class.
    • (A1 & A2) The constructor connects to the database when $DB = new DB() is created, the destructor closes the connection.
    • (A3) exec() Runs an SQL query.
    • (A4) fetch() Runs a SELECT SQL query, returns a single row.
    • (A5) fetchAll() Runs a SELECT SQL query, returns multiple rows.
  2. Database settings. Remember to change to your own.
  3. Self-explanatory…

Yep, in “normal projects” it is better to break the database class into a script on its own. I am just being lazy and trying to keep things simple here.

 

 

B2) REGISTER PERMISSIONS DURING LOGIN

2b-login.php
<?php
// (A) LOGIN FORM SENDS TO THIS SCRIPT
$_POST = [
  "email" => "john@doe.com",
  //"email" => "jane@doe.com",
  "password" => "123456"
];

// (B) FETCH USER FROM DATABASE & VERIFY THE PASSWORD
require "2a-core.php";
$user = $DB->fetch(
  "SELECT * FROM `users` JOIN `roles` USING (`role_id`) WHERE `user_email`=?",
  [$_POST["email"]]
);
$pass = is_array($user);
if ($pass) { $pass = $user["user_password"] == $_POST["password"]; }
if (!$pass) { exit("Invalid user/password"); }

// (C) IF VERIFIED - REGISTER USER INTO SESSION
$_SESSION["user"] = $user;
$_SESSION["user"]["permissions"] = [];
unset($_SESSION["user"]["user_password"]);
 
// (D) REGISTER PERMISSIONS
$perm = $DB->fetchAll(
  "SELECT * FROM `roles_permissions` WHERE `role_id`=?",
  [$user["role_id"]]
);
foreach ($perm as $p) { $_SESSION["user"]["permissions"][] = $p["perm_id"]; }
 
// (E) DONE!
print_r($_SESSION);

This should be pretty straightforward – In your login process, simply do the usual email/password check. But on top of it, fetch the permissions from the database and put them into the session.

 

 

B3) PROTECT THE PROCESSES

2c-protection.php
<?php
// (A) INIT + PERMISSION CHECK MECHANISM
require "2a-core.php";
function cando ($pid) {
  return in_array($pid, $_SESSION["user"]["permissions"]);
}

// (B) ADD USER
// (B1) DUMMY USER
$_POST = [
  "email" => "jack@doe.com",
  "password" => "123456",
  "role" => 2
];

// (B2) PERMISSION CHECK
if (!cando(2)) { exit("No permission to add user"); }

// (B3) SQL INSERT
echo $DB->exec(
  "INSERT INTO `users` (`user_email`, `user_password`, `role_id`) VALUES (?,?,?)",
  [$_POST["email"], $_POST["password"], $_POST["role"]]
) ? "OK" : $DB->error ;

Yep, it is as simple as that. In your own libraries/functions/scripts, just do a quick check on the session to see if the user has sufficient permissions… Even though this may seem easy, it can be very tedious if you have hundreds of processes. So sometimes, it is better to not micro-manage everything.

 

USEFUL BITS & LINKS

That’s it for all the code, and here are a few small extras that you may find to be useful.

 

MULTIPLE USER ROLES?

Of course, we can. Just create another user_roles table with 2 fields – user_id and role_id. But think twice – The complexity increases exponentially. You need to check for duplicate permissions and even possible clash of interest. For example, can a customer also be an administrator? Does this even make any sense?

 

LINKS & REFERENCES

 

YOUTUBE TUTORIAL

 

THE END

Thank you for reading, and we have come to the end of this guide. I hope that it has helped you to better manage access permissions in your project. If you have anything to share with this guide, please feel free to comment below. Good luck, and happy coding!

15 thoughts on “Simple User Role Management System With PHP MySQL (Free Download)”

  1. Hello! Thanks for this complete guide. I’d like to add if I may to those who came here in search of improving security in their projects (like myself) that you add on your tables two fields (like ‘granted by’ and ‘created_at’) so that you know who did an action(like granting a permission) and by what date. I’d especifically do this on this guide to 1c-dummy.sql -> roles_permission.

  2. This tutorial is good for beginner, but also beginners need to know how to secure a script.
    But to keep it simple there needs to be a password_hash() in this script.
    Instead of using this:

    if ($pass) {
    $pass = $user[0][‘user_password’] == $_POST[‘password’];
    }

    I recommend using this

    if($pass){
    $pass = password_verify($_POST[‘password’], $user[0][‘user_password’]);
    }

  3. I’m curious, was there a reason why you used a composite key over a surrogate key within the `permissions` (Strong Entity) table?
    I’m only a novice but I heard that composite keys can be a real headache within complex databases.

    1. Good question, I don’t remember the exact details too. This is a simplified version of one that I have previously built, I believe the composite primary key is there as a restriction, to boost SELECT-JOIN-SORT query performance, or sorts.

      But go ahead – perm_id (primary key) + perm_mod (indexed) works better in this simplified version.

  4. Hi,
    Great tutorial. It would be a good option to allow a user to have multiple roles. Instead of adding the role on the user table, you need to create a new table users_roles
    user_id role_id
    in that way the system is not limited to single roles. hope this helps. cheers

    1. Thanks for sharing, but let’s keep things simple here. For you guys who are looking at multiple roles, know that there are complications. The system can grow exponentially complicated, do be careful with the possibilities of clashing permissions and job scope… Not just in the system, but also in real life.

Leave a Comment

Your email address will not be published.