How to Create a User Role Management System With PHP MySQL

Welcome to a tutorial on how to create a PHP User Role Management System. 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 how to create a simple permissions structure in this guide, step-by-step. Read on to find out!

ⓘ 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.

 

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.

 

QUICK NOTES

  • Create a database and import all the files in the sql folder.
  • 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, please feel free to comment below. I try to answer 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.

 

ASSUMPTIONS – AN EXISTING PROJECT

A permissions system will not make much sense as a “standalone”, so I shall assume here that most of you guys have an existing project, and looking for ways to build a permissions structure on top of it. We shall not go into how to create a user system. If you do not already have a user/login system, I will leave a link in the extras section below to my other guide.

Also, this guide will be in pure HTML, CSS, Javascript, and PHP – No third-party frameworks will be used, and that should make it much easier for everyone to integrate.

 

 

A) THE DATABASE

Let us now start with the foundations of the system, the database – Don’t worry if you have not created a users database yet, I shall provide a complete working example here.

 

A1) TABLES OVERVIEW

There are 4 tables involved in this project:

  • Permissions – To keep track of actions that require permission. For example, accessing the list of users, and creating new users.
  • Roles – Names of the roles. For example, administrators, editors, etc…
  • Roles-Permissions – To specify which role has which permissions.
  • Users – Your list of users and their roles.

 

A2) THE PERMISSIONS TABLES

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

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

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;

CREATE TABLE `roles_permissions` (
  `role_id` int(11) NOT NULL,
  `perm_mod` varchar(5) NOT NULL,
  `perm_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `roles_permissions`
  ADD PRIMARY KEY (`role_id`,`perm_mod`,`perm_id`);
Permissions
Field Description
perm_mod The module, an abbreviation code up to 5 characters. For example “USR” for users, “INV” for inventory. Partial primary key.
perm_id Permissions ID, just a running number. Partial primary key.
perm_desc Permission description. For example, access inventory list, create a new user, etc…
Roles
Field Description
role_id Role ID, primary key, and auto-increment.
role_name Name of the role. For example, an administrator.
Role Permissions
Field Description
role_id Role ID, partial primary key.
perm_mod Module code, partial primary key.
perm_id Permission ID, partial primary key.

 

A3) USERS TABLES

If you do not have a users table, here is a simple one that you can use.

sql/1b-users.sql
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `user_email` varchar(255) NOT NULL,
  `user_name` 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_id`),
  ADD UNIQUE KEY `user_email` (`user_email`),
  ADD KEY `user_name` (`user_name`);

ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT;
Field Description
user_id The user ID, auto-increment and primary key.
user_email User’s email. Set to unique to prevent duplicate registrations.
user_name The user’s name. Indexed for better search performance.
user_password User password.
role_id Role of the user.

P.S. If you already have an existing users table, just give each of your users a role_id.

 

 

A4) SAMPLE DATA

Finally, here is the dummy data that we will use as an example.

sql/1c-sample.sql
INSERT INTO `users` (`user_id`, `user_email`, `user_name`, `user_password`, `role_id`) VALUES
(1, 'john@doe.com', 'John Doe', '123456', 1),
(2, 'jane@doe.com', 'Jane Doe', '123456', 2);

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

INSERT INTO `roles` (`role_id`, `role_name`) VALUES
(1, 'Administrator'),
(2, 'Power User');

INSERT INTO `roles_permissions` (`role_id`, `perm_mod`, `perm_id`) VALUES
(1, 'USR', 1),
(1, 'USR', 2),
(1, 'USR', 3),
(1, 'USR', 4),
(2, 'USR', 1);

 

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) MUTE NOTICES
error_reporting(E_ALL & ~E_NOTICE);

// (B) DATABASE SETTINGS - CHANGE THESE TO YOUR OWN
define('DB_HOST', 'localhost');
define('DB_NAME', 'test');
define('DB_CHARSET', 'utf8');
define('DB_USER', 'root');
define('DB_PASSWORD', '');

// (C) CONNECT TO DATABASE
try {
  $pdo = new PDO(
    "mysql:host=" . DB_HOST . ";charset=" . DB_CHARSET . ";dbname=" . DB_NAME,
    DB_USER, DB_PASSWORD, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false]
  );
} catch (Exception $ex) {
  print_r($ex);
  die();
}

// (D) START SESSION
session_start();

Well, nothing much to this first script – You should already have these somewhere in your own project. Just make sure that you have a database connection and start the PHP session.

 

 

B2) SAVE PERMISSIONS INTO PHP SESSION DURING LOGIN

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

// (B) WE FETCH THE USER FROM DATABASE & VERIFY THE PASSWORD
require "2a-core.php";
$stmt = $pdo->prepare("SELECT * FROM `users` LEFT JOIN `roles` USING (`role_id`) WHERE `user_email`=?");
$stmt->execute([$_POST['email']]);
$user = $stmt->fetchAll();
$pass = count($user)>0;
if ($pass) {
  $pass = $user[0]['user_password'] == $_POST['password'];
}

// (C) IF VERIFIED - WE PUT THE USER & PERMISSIONS INTO THE SESSION
if ($pass) {
  $_SESSION['user'] = $user[0];
  $_SESSION['user']['permissions'] = [];
  unset($_SESSION['user']['user_password']); // Security...
  $stmt = $pdo->prepare("SELECT * FROM `roles_permissions` WHERE `role_id`=?");
  $stmt->execute([$user[0]['role_id']]);
  while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
    if (!isset($_SESSION['user']['permissions'][$row['perm_mod']])) {
      $_SESSION['user']['permissions'][$row['perm_mod']] = [];
    }
    $_SESSION['user']['permissions'][$row['perm_mod']][] = $row['perm_id'];
  }
}

// (D) DONE!
echo $pass ? "OK" : "Invalid email/password" ;
echo "<br><br>SESSION DUMP<br>";
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 SCRIPTS

2c-protection.php
<?php
// (A) LET'S SAY THAT THIS SCRIPT IS USED TO UPDATE A USER
$_POST = [
  "id" => 2,
  "email" => "joy@doe.com",
  "name" => "Joy Doe",
  "password" => "123456",
  "role" => 1
];

// (B) PERMISSIONS CHECK FUNCTION
// Keep this somewhere in your "core library".
function check ($module, $id) {
  return in_array($id, $_SESSION['user']['permissions'][$module]);
}

// (C) WE WILL CHECK IF THE USER HAS PERMISSIONS TO DO SO FIRST
require "2a-core.php";
if (!check ("USR", 3)) {
  die("NO PERMISSION TO ACCESS!");
}

// (D) PROCEED IF OK
try {
  $stmt = $pdo->prepare("UPDATE `users` SET `user_email`=?, `user_name`=?, `user_password`=?, `role_id`=? WHERE `user_id`=?");
  $stmt->execute([$_POST['email'], $_POST['name'], $_POST['password'], $_POST['role'], $_POST['id']]);
} catch (Exception $ex) {
  print_r($ex);
  die();
}
echo "UPDATE OK!";

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… But even though this might be easy, it can be extremely tedious if you have hundreds of different functions. So sometimes, it is better to go easier and not micro-manage too much.

 

 

USEFUL BITS & LINKS

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

 

SUMMARY

  • Assign a module code to whatever you want to build next. For example, “INV” for inventory or “PDT” for products.
  • List out all the functions that require permissions. For example, 1 for accessing the inventory list, 2 for adding new items, 3 for editing items, etc…
  • Add the permissions to the database, and assign which user roles have the permissions.
  • Build your library, function, and/or scripts. But do a check with the $_SESSION['user']['permissions'] before actually processing it.

 

YOUTUBE TUTORIAL

 

LINKS & REFERENCES

 

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!

11 thoughts on “How to Create a User Role Management System With PHP MySQL”

  1. 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’]);
    }

  2. 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.

  3. 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. Required fields are marked *