Welcome to tutorial How to create a user role and
permission management system with PHP MySQL Server and PDO. A project that
needs to identity user and restrict each user who you are? (Authentication) And
Those users can do in the system (Authorization). This simple user role
permission structure in this guide, step by step.
Table of contents
- Database Diagram of User
Role Permission System
- Create Database and Table
- Implement code
- Database Diagram of User Role Permission System
2. Create Database and Table
2.1 Create database
CREATE DATABASE user_role_permission;
USE user_role_permission;
`role_id` bigint NOT NULL,
`role_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE `roles`
ADD PRIMARY KEY (`role_id`),
ADD UNIQUE KEY `role_name` (`role_name`);
ALTER TABLE `roles`
MODIFY `role_id` bigint NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
INSERT INTO `roles` (`role_id`, `role_name`) VALUES
(3, 'Administrator'),
(1, 'Manager'),
(4, 'Sale'),
(2, 'Supervisor');
· role_id
Primary key and auto increment.
· role_name
Name of the role name
Role needs to have role name in the table,
we have Administrator, Manager, Supervisor and Sale.
2.3 Create Permission table
CREATE TABLE `permissions` (
`perm_id` bigint NOT NULL,
`perm_mod` varchar(5) NOT NULL,
`perm_desc` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE `permissions`
ADD PRIMARY KEY (`perm_id`),
ADD KEY `perm_mod` (`perm_mod`);
ALTER TABLE `permissions`
MODIFY `perm_id` bigint NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
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 instance, USR for users, UEDIT user edit, DUSER Delete user
and so on.
· perm_desc
for a description of the action of perm_mod column.
After that, we have a table to hold the
list of role_id and perm_id we can call this table as bridge between two table
Role and Permission table. This table hold actions that you want to control
role and permission. We will keep things simple in this example Get, Save,
Update, and Delete users.
2.4 Role
Permission Table
CREATE TABLE `roles_permissions` (
`role_id` bigint NOT NULL,
`perm_id` bigint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE `permissions`
MODIFY `perm_id` bigint NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
ALTER TABLE `roles_permissions`
ADD CONSTRAINT `roles_permissions_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT `roles_permissions_ibfk_2` FOREIGN KEY (`perm_id`) REFERENCES `permissions` (`perm_id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `permissions`
ADD PRIMARY KEY (`perm_id`),
ADD KEY `perm_mod` (`perm_mod`);
ALTER TABLE `roles_permissions`
ADD PRIMARY KEY (`role_id`,`perm_id`),
ADD KEY `roles_permissions_ibfk_2` (`perm_id`);
ALTER TABLE `roles_permissions`
ADD PRIMARY KEY (`role_id`,`perm_id`),
ADD KEY `roles_permissions_ibfk_2` (`perm_id`);
INSERT INTO `roles_permissions` (`role_id`, `perm_id`) VALUES
(1, 1),
(2, 1),
(1, 2),
(1, 3);
· role_id
is foreign key column for hold value of primary key of role table.
· perm_id
is foreign key column for hold value of primary key of permission table.
While role has permission to do what?
In the example
· Supervisor
can only get users.
· Manager
can get, save, update and delete users.
2.5 User table
CREATE TABLE `users` (
`user_id` bigint NOT NULL,
`user_email` varchar(255) NOT NULL,
`user_password` varchar(255) NOT NULL,
`role_id` bigint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
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 NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
ALTER TABLE `users`
ADD CONSTRAINT `users_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
COMMIT;
INSERT INTO `users` (`user_id`, `user_email`, `user_password`, `role_id`) VALUES
(1, 'admin@email.com', '123456', 1),
(2, 'user@email.com', '123456', 2),
(3, 'james@email.com', '12345', 1);
· user_id
Primary key and auto increment by 1
· user_email
is User’s email, unique to prevent duplicates value
· user_password
is User’s password.
· role_id
is User’s role and foreign key column for hold value of primary key of the
table Role table.
Lastly,
this is a existing user table, with an addition role_id field. In this
instance:
admin@email.com
user@email.com
james@email.com
3. Implement code
3.1 User.php
<?php
session_start();
define("DB_HOST", "localhost");
define("DB_NAME", "user-role-permission");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");
//CLASS USER
class User {
//PROPERTY
private $pdo = null;
private $stmt = null;
public $error;
//CONSTRUCTOR
function __construct () {
$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
]);
}
//DESTRUCTOR
function __destruct () {
if ($this->stmt !== null) { $this->stmt = null; }
if ($this->pdo !== null) { $this->pdo = null; }
}
//QUERY statement
function query($sql, $data=null) : void {
$this->stmt = $this->pdo->prepare($sql);
$this->stmt->execute($data);
}
//LOGIN
function login ($email, $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;
}
$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"];
}
$_SESSION["user"] = $user;
unset($_SESSION["user"]["user_password"]);
return true;
}
//CHECK SESSION
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; }
}
// 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();
}
// 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;
}
// DELETE USER
function del ($id) {
if (!$this->check("USR", 3)) { return false; }
$this->query("DELETE FROM `users` WHERE `user_id`=?", [$id]);
return true;
}
}
$_USR = new User();
3.2 Login.php
<?php
require "user.php";
echo $_USR->login("admin@email.com", "123456") ? "OK" : $_USR->error;
// echo $_USR->login("admin@email.com", "123456") ? "OK" : $_USR->error;
// TO LOGOUT
// if (isset($_SESSION["user"])) { unset($_SESSION["user"]); }
// WHO AM I?
print_r($_SESSION);
The result:
OKArray
(
[user] => Array
(
[role_id] => 2
[user_id] => 2
[user_email] => admin@email.com
[role_name] => Supervisor
[permissions] => Array
(
[USR] => Array
(
[0] => 1
)
)
)
)
3.3 Result
result.php
<?php
require "user.php";
//GET USER
$user = $_USR->get("admin@email.com");
if ($user===false) { echo $_USR->error . "\r\n"; }
print_r($user);
// SAVE USER
echo $_USR->save("sale@email.com", "123456", 1) ? "OK" : $_USR->error . "\r\n" ;
// echo $_USR->save("james@email.com", "123456", 1) ? "OK" : $_USR->error . "\r\n" ;
// DELETE USER
echo $_USR->del(123) ? "OK" : $_USR->error . "\r\n" ;
// echo $_USR->del(4) ? "OK" : $_USR->error . "\r\n" ;
Result:
Array
(
[role_id] => 2
[user_id] => 2
[user_email] => sale@email.com
[user_password] => 123456
[role_name] => Supervisor
)
No permission to access.
No permission to access.
