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

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

 

QUICK NOTES

  • Create a database and import 1-database.sql.
  • Change the database settings in 2-lib-user.php to your own.
  • Run 3a-login.php to log in as a manager, supervisor, or log out.
  • Then access 3b-run.php to see how the permission restrictions work.
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.

 

 

PHP MYSQL USER ROLE MANAGEMENT

All right, let us now get into the details of building a user role management system with PHP and MYSQL.

 

PART 1) THE DATABASE

 

1A) ROLES TABLE

1-database.sql
CREATE TABLE `roles` (
  `role_id` bigint(20) NOT NULL,
  `role_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `roles`
  ADD PRIMARY KEY (`role_id`),
  ADD UNIQUE KEY `role_name` (`role_name`);
 
ALTER TABLE `roles`
  MODIFY `role_id` bigint(20) NOT NULL AUTO_INCREMENT;
 
INSERT INTO `roles` (`role_id`, `role_name`) VALUES
(1, 'Manager'),
(2, 'Supervisor'); 
  • role_id Primary key and auto-increment.
  • role_name Name of the role.

First, let us address the elephant in the room. A role management system needs to have… roles. For this example, we go with Manager and Supervisor.

 

1B) PERMISSIONS TABLES

1-database.sql
CREATE TABLE `permissions` (
  `perm_id` bigint(20) 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` bigint(20) NOT NULL AUTO_INCREMENT;
 
INSERT INTO `permissions` (`perm_id`, `perm_mod`, `perm_desc`) VALUES
(1, 'USR', 'Get users'),
(2, 'USR', 'Save users'),
(3, 'USR', 'Delete users');
  • perm_id Primary key and auto-increment.
  • perm_mod Module code. For example USR for users, INV for inventory.
  • perm_desc A description of the action.
Next, we have a table to hold the list of “actions that you want to control”. We will keep things simple in this example – Get, save, and delete users.

 

 

1C) ROLE PERMISSIONS TABLES

1-database.sql
CREATE TABLE `roles_permissions` (
  `role_id` bigint(20) NOT NULL,
  `perm_id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `roles_permissions`
  ADD PRIMARY KEY (`role_id`,`perm_id`);
 
INSERT INTO `roles_permissions` (`role_id`, `perm_id`) VALUES
(1, 1), (1, 2), (1, 3),
(2, 1);
  • role_id Primary and foreign key.
  • perm_id Primary and foreign key.

Which role has permission to do what? In this example:

  • Manager can get, save, and delete users.
  • Supervisor can only get users.

 

1D) USERS TABLES

1-database.sql
CREATE TABLE `users` (
  `user_id` bigint(20) NOT NULL,
  `user_email` varchar(255) NOT NULL,
  `user_password` varchar(255) NOT NULL,
  `role_id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `user_email` (`user_email`),
  ADD KEY `role_id` (`role_id`);
 
ALTER TABLE `users`
  MODIFY `user_id` bigint(20) NOT NULL AUTO_INCREMENT;
 
INSERT INTO `users` (`user_email`, `user_password`, `role_id`) VALUES
('joe@doe.com', '123456', 1),
('jon@doe.com', '123456', 2);
  • user_id Primary key, auto-increment.
  • user_email User’s email, unique to prevent duplicates.
  • user_password User’s password.
  • role_id User’s role, foreign key.

Lastly, this is just a good old user table, with an “additional” role_id field. In this example:

  • joe@doe.com Manager.
  • jon@doe.com Supervisor.

 

 

PART 2) PHP LIBRARY

2A) INITIALIZE

2-lib-user.php
<?php
class User {
  // (A) 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()); }}
 
  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
  }
 
  // (C) RUN SQL QUERY
  function query ($sql, $data=null) {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }
  // ...
}
 
// (I) 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", "");
 
// (J) START!
$_USR = new User();
session_start();

The library looks massive at first, but keep calm and look carefully:

  • (A, B, J) When $_USR = new User() is created, the constructor connects to the database. The destructor closes the connection.
  • (C) query() A helper function to run an SQL query.
  • (I) The database settings, remember to change to your own.

 

2B) LOGIN & SESSION

2-lib-user.php
// (D) LOGIN
function login ($email, $password) {
  // (D1) GET USER & CHECK PASSWORD
  $this->query("SELECT * FROM `users` JOIN `roles` USING (`role_id`) WHERE `user_email`=?", [$email]);
  $user = $this->stmt->fetch();
  $valid = is_array($user);
  if ($valid) { $valid = $password == $user["user_password"]; }
  if (!$valid) {
    $this->error = "Invalid email/password";
    return false;
  }
 
  // (D2) GET PERMISSIONS
  $user["permissions"] = [];
  $this->query(
    "SELECT * FROM `roles_permissions` r
    LEFT JOIN `permissions` p USING (`perm_id`)
    WHERE r.`role_id`=?", [$user["role_id"]]
  );
  while ($r = $this->stmt->fetch()) {
    if (!isset($user["permissions"][$r["perm_mod"]])) {
      $user["permissions"][$r["perm_mod"]] = [];
    }
    $user["permissions"][$r["perm_mod"]][] = $r["perm_id"];
  }
 
  // (D3) DONE
  $_SESSION["user"] = $user;
  unset($_SESSION["user"]["user_password"]);
  return true;
}

Next, login() is what we use to sign in as a user. Take note of how the permissions are stored into $_SESSION.

  • $_SESSION["user"] User ID, email, role.
  • $_SESSION["user"]["permissions"] Allowed actions, in the format of MODULE => [PERMISSION ID, PERMISSION ID, ...].

P.S. This is a lazy example, passwords should be encrypted – PHP password hash and verify is a good start.

P.P.S. $_SESSION is not the only “login mechanism”, there is also JSON Web Token. A little on the advanced side, but I will leave links below if you want to learn more.

 

 

2C) PERMISSIONS & FUNCTIONS

2-lib-user.php
// (E) CHECK PERMISSION
function check ($module, $perm) {
  $valid = isset($_SESSION["user"]);
  if ($valid) { $valid = in_array($perm, $_SESSION["user"]["permissions"][$module]); }
  if ($valid) { return true; }
  else { $this->error = "No permission to access."; return false; }
}
 
// (F) GET USER
function get ($email) {
  if (!$this->check("USR", 1)) { return false; }
  $this->query("SELECT * FROM `users` JOIN `roles` USING (`role_id`) WHERE `user_email`=?", [$email]);
  return $this->stmt->fetch();
}
 
// (G) SAVE USER
function save ($email, $password, $role, $id=null) {
  if (!$this->check("USR", 2)) { return false; }
  $sql = $id==null
    ? "INSERT INTO `users` (`user_email`, `user_password`, `role_id`) VALUES (?,?,?)"
    : "UPDATE `users` SET `user_email`=?, `user_password`=?, `role_id`=? WHERE `user_id`=?" ;
  $data = [$email, $password, $role];
  if ($id!=null) { $data[] = $id; }
  $this->query($sql, $data);
  return true;
}
 
// (H) DELETE USER
function del ($id) {
  if (!$this->check("USR", 3)) { return false; }
  $this->query("DELETE FROM `users` WHERE `user_id`=?", [$id]);
  return true;
}
  • (F, G, H) The “actual user library functions”.
    • get() Get a user by email.
    • save() Add or update a user.
    • del() Delete a user.
  • (D) Take note of how get(), save(), and del() all do a permission check() before they are allowed to run. check() simply does a check against $_SESSION["user"]["permissions"] if the current user has permission to run the function.

 

PART 3) INTEGRATION & TESTING

3A) LOGIN

3a-login.php
<?php
// (A) LOAD LIBRARY
require "2-lib-user.php";

// (B) TRY LOGGING IN AS DIFFERENT USERS
echo $_USR->login("jon@doe.com", "123456") ? "OK" : $_USR->error;
// echo $_USR->login("joe@doe.com", "123456") ? "OK" : $_USR->error;

// (C) TO LOGOUT
// if (isset($_SESSION["user"])) { unset($_SESSION["user"]); }
 
// (D) WHO AM I?
print_r($_SESSION);

This should be straightforward – Simply use $_USR->login(EMAIL, PASSWORD) to sign in. In this example, we will sign in as jon@doe.com.

 

 

3B) TEST RUN

3b-run.php
<?php
// (A) LOAD LIBRARY
require "2-lib-user.php";

// (B) GET USER
$user = $_USR->get("jon@doe.com");
if ($user===false) { echo $_USR->error . "\r\n"; }
print_r($user);

// (C) SAVE USER
echo $_USR->save("job@doe.com", "123456", 1) ? "OK" : $_USR->error . "\r\n" ;
// echo $_USR->save("joy@doe.com", "123456", 1) ? "OK" : $_USR->error . "\r\n" ;

// (D) DELETE USER
echo $_USR->del(123) ? "OK" : $_USR->error . "\r\n" ;
// echo $_USR->del(4) ? "OK" : $_USR->error . "\r\n" ;

Finally, to verify that the permission checks work –

  • jon@doe.com is a Supervisor.
  • Can only get users.
  • Cannot save and delete users.

Yep, it works. Go ahead and login as joe@doe.com next and run this again – Joe should have permission to save and delete users.

 

EXTRA BITS & LINKS

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

 

CONTROLLING ACCESS TO HTML PAGES

<?php
// (A) LOAD LIBRARY & ACCESS CHECK
require "2-lib-user.php";
if ($_USR->check(MODULE, PERMISSION ID) === false) {
  exit("No access");
  // or redirect somewhere else
}

// (B) HTML PAGE AS USUAL
// ...

 

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 will increase exponentially. You need to check for duplicate permissions and even a possible clash of interests. 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!

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

  1. Well, thank you sir for this well written tutorial and the time spent to do it. Your instructions are clear and the site is well done. Again, thousands thanks, bless you.

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

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

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

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