THE SYSTEM STRUCTURE

I am sure that everyone here already has an existing website, and it follows the “universal” structure of:

• Database – Storing all your users’ data and stuff.
• Server-side Scripts – A set of library files and process handlers.
• Client-side Scripts – Front-end user interface.

Thus, I shall assume that you guys already have some sort of a user database, admin panel, and login mechanism – We will not reinvent the wheel in this guide and come up with another user login system plus admin interface. What we will focus on, are the database, the libraries, plus the concepts of a user role/permission system.

We will not build any nice looking user interfaces in this guide, but touch more on the technical changes you need to make on your existing system. Also, I shall assume that you guys are already veteran code ninjas who are comfortable with PHP, HTML, CSS, Javascript, AJAX, and SQL… That I will not need to explain the tiny boring details of “how HTML and CSS work”.

1A) THE TABLES

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 examples, administrators, editors, etc…
• Roles-Permissions – To specify which role has which permissions.
• Users -Your list of users and their roles.

1B) THE SQL

-- [USER TABLE]
-- ! JUST ADD A ROLE_ID IF YOU HAVE AN EXISTING TABLE !
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;

-- [PERMISSIONS TABLES]
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`);

If you already have an existing users table, just add a role ID to it.

That’s it for the tables, but please do remember to add more of your own permissions and roles.

1C) SAMPLE DATA

This is the set of dummy data that we will use as examples in this guide.

-- [DUMMY DATA]
-- USER PASSWORD IS 123456
INSERT INTO `users` (`user_id`, `user_email`, `user_name`, `user_password`, `role_id`) VALUES
(1, '[email protected]', 'John Doe', '$2y$10$EIPcGzgNnfpZM9gkm3GqiuNaGjZuJwdCggOVrwzJ6olNQqqRmCH5i', 1),
(2, '[email protected]', 'Jane Doe', '$2y$10$EIPcGzgNnfpZM9gkm3GqiuNaGjZuJwdCggOVrwzJ6olNQqqRmCH5i', 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);

2A) THE CONFIG FILE

If you have not already created a config file, it is time to do so. There is nothing much to it, just remember to change the database settings to your own.

2B) THE BASE LIBRARY

pdo = new PDO(
        $str, DB_USER, DB_PASSWORD, [
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
          PDO::ATTR_EMULATE_PREPARES => false
        ]
      );
      return true;
    }

    // ERROR - DO SOMETHING HERE
    // THROW ERROR MESSAGE OR SOMETHING
    catch (Exception $ex) {
      print_r($ex);
      die();
    }
  }

  function __destruct() {
  // __destruct() : close connection when done

    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
  }

  function start() {
  // start() : auto-commit off

    $this->pdo->beginTransaction();
  }

  function end($commit=1) {
  // end() : commit or roll back?

    if ($commit) { $this->pdo->commit(); }
    else { $this->pdo->rollBack(); }
  }
 
  function exec($sql, $data=null) {
  // exec() : run insert, replace, update, delete query
  // PARAM $sql : SQL query
  //       $data : array of data
 
    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($data);
      $this->lastID = $this->pdo->lastInsertId();
    } catch (Exception $ex) {
      $this->error = $ex;
      return false;
    }
    $this->stmt = null;
    return true;
  }

  function fetch($sql, $cond=null, $key=null, $value=null) {
  // fetch() : perform select query
  // PARAM $sql : SQL query
  //       $cond : array of conditions
  //       $key : sort in this $key=>data order, optional
  //       $value : $key must be provided, sort in $key=>$value order

    $result = false;
    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($cond);
      if (isset($key)) {
        $result = array();
        if (isset($value)) {
          while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
            $result[$row[$key]] = $row[$value];
          }
        } else {
          while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
            $result[$row[$key]] = $row;
          }
        }
      } else {
        $result = $this->stmt->fetchAll();
      }
    } catch (Exception $ex) {
      $this->error = $ex;
      return false;
    }
    $this->stmt = null;
    return $result;
  }

  function check($perm, $mod="") {
  // check() : checks if user has sufficient access rights
  // PARAM $perm : permission ID
  //       $mod : module code
  //       !! USER MUST BE SIGNED IN FIRST !!

    // INIT
    $pass = true;
    if ($mod=="") { $mod = $this->modcode; }

    // NOT SIGNED IN
    // OR USER DOES NOT HAVE PERMISSION TO ENTIRE MODULE
    if (!is_array($_SESSION['user']['perm'][$mod]))  { $pass = false; }

    // CHECK SPECIFIC PERMISSION
    if (!in_array($perm, $_SESSION['user']['perm'][$mod])) { $pass = false; }

    // RETURN ACCESS CHECK
    if (!$pass) { $this->error = "You do not have sufficient access rights."; }
    return $pass;
  }
}
?>

Yep, this is actually a database library that we will be building the rest of the system upon. The key function that you have to take note here is check.

3A) CHECKS IN LIBRARY FILES

Just how the heck do we use the base library and do the checks? Let us start by creating a users library as an example.

fetch(
      "SELECT * FROM `users` LEFT JOIN `roles` USING (`role_id`) WHERE `user_email`=?",
      [$email]
    );
    if (count($entry)==0) { return false; }
    else { 
      // Verify password
      $entry = $entry[0];
      if (password_verify($password, $entry['user_password'])) {
        $_SESSION['user'] = [
          "user_id" => $entry['user_id'],
          "role_id" => $entry['role_id'],
          "role_name" => $entry['role_name'],
          "user_email" => $entry['user_email'],
          "user_name" => $entry['user_name']
        ];
        // Get permissions
        $entry = $this->fetch(
          "SELECT * FROM `roles_permissions` WHERE `role_id`=?",
          [$entry['role_id']]
        );
        if (is_array($entry)) { 
          $_SESSION['user']['perm'] = [];
          foreach ($entry as $e) {
            if (!is_array($_SESSION['user']['perm'][$e['perm_mod']])) {
              $_SESSION['user']['perm'][$e['perm_mod']] = [];
            }
            $_SESSION['user']['perm'][$e['perm_mod']][] = $e['perm_id'];
          }
        }
      } else { return false; }
    }
    return true;
  }

  function get($id) {
  // get() : get user
  // PARAM $id : user ID or email

    // ACCESS CHECK
    if (!$this->check(1)) { return false; }

    // FETCH
    $sql = sprintf("SELECT * FROM `users` LEFT JOIN `roles` USING (`role_id`) WHERE `user_%s`=?",
      is_numeric($id) ? "id" : "email"
    );
    $entry = $this->fetch($sql, [$id]);
    return count($entry)==0 ? false : $entry[0] ;
  }

  function add($email, $name, $password, $role) {
  // add() : add a new user
  // PARAM $email : email address
  //       $name : user name
  //       $password : plain text password
  //       $role : role ID

    // ACCESS CHECK
    if (!$this->check(2)) { return false; }

    // INSERT
    return $this->exec(
      "INSERT INTO `users` (`user_email`, `user_name`, `user_password`, `role_id`) VALUES (?, ?, ?, ?)",
      [$email, $name, password_hash($password, PASSWORD_DEFAULT), $role]
    );
  }

  function edit($email, $name, $password, $role, $id) {
  // edit() : update an existing user
  // PARAM $email : email address
  //       $name : user name
  //       $password : plain text password
  //       $role : role ID
  //       $id : user ID

    // ACCESS CHECK
    if (!$this->check(3)) { return false; }

    // UPDATE
    return $this->exec(
      "UPDATE `users` SET `user_email`=?, `user_name`=?, `user_password`=?, `role_id`=? WHERE `user_id`=?",
      [$email, $name, password_hash($password, PASSWORD_DEFAULT), $role, $id]
    );
  }

  function delete($id) {
  // delete() : delete user
  // PARAM $id : user ID

    // ACCESS CHECK
    if (!$this->check(4)) { return false; }

    // DELETE
    return $this->exec(
      "DELETE FROM `users` WHERE `user_id`=?",
      [$id]
    );
  }
}
?> 

Let us now follow up with how the permissions checking work:

1. This users library is a child of the base class. Meaning, it inherits all the database and check functions.
2. The users library is given the module code of “USR” with protected $modcode = "USR";
3. Before running the functions get, add, edit, and delete – There will be an access permission check.
4. The check function will simply match against the user session to see if the user has access rights for the given module code and permission ID.

3B) SET THE PERMISSIONS IN THE SESSION!

So yep, the key is to first put the access permissions into session upon login – You can recycle my user library and login function in your own project if you wish.

in("[email protected]", "123456") ? "OK" : $libUsr->error ;
print_r($_SESSION);
?>

3C) EVERYTHING SHOULD AUTOMATICALLY FALL IN PLACE

Once those are done, everything should pretty much automatically fall in place. For example, the library functions will only run when the user has sufficient access rights:

add($_POST['email'], $_POST['name'], $_POST['password'], $_POST['role'])
  ? "OK" 
  : $libUsr->error ;
?>

Or you can directly use the check function to restrict access to certain pages:

check(1)) {
  die("You do not have access to this page.");
} ?>


  Yes, you can access the users management page

SUMMARY

• Assign a module code to whatever you want to build next. For example, “INV” for inventory.
• Layout the list of functions that require permissions. For example, 1 for accessing the inventory list, 2 for adding new items, 3 for editing items, etc…
• Add these permissions to the database, and assign which roles have the permissions.
• Build your library, extend the base library.
• Set the protected $modcode in your library.
• Run the checks before executing each get/add/edit/delete function.

LOGIN

Don’t have a login mechanism yet? Here is a guide on how to create one:

3 Steps to Create a Simple PHP Login Page (Free Script)

DOWNLOAD

Click here to download the source code in a zip file – I have released it under the MIT License, so feel free to build on top of it if you want to.

The post 3 Steps Simple PHP User Role Management System appeared first on Code Boxx.