When designing an internal application, you must provide a form of access control through authentication and authorization. You use authentication to confirm whether users have the right to access your system by validating their usernames and passwords against their account values. On the other hand, authorization allows you to verify whether an authenticated user has the correct permission to access a particular resource. For instance, to delete or update a product.
While the MySQL database provides roles-based permissions, it is not practically possible to create database usernames for all users accessing your system unless they're connecting directly via the command-line interface. In an optimal PHP application, your scripts connect to the database server through a single MySQL user account. Then, it is your job to code an access control functionality using the different features of PHP and MySQL.
In this guide, you'll set up a role-based permission feature for your application with PHP and MySQL on your Ubuntu 20.04 server. Instead of assigning permissions to users directly, you'll group the permissions and assign them to a role. Then, once you tie up users to a role, they will automatically inherit the privileges assigned to that role.
To complete this guide, you require:
SSH to your server to complete the following steps.
Log in to your MySQL database server as root.
$ sudo mysql -u root -p
Enter your root password when prompted and press ENTER to proceed. Then, set up a sample erp_db database and user account. Replace EXAMPLE_PASSWORD with a strong value.
mysql> CREATE DATABASE erp_db;
CREATE USER 'erp_db_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
GRANT ALL PRIVILEGES ON erp_db.* TO 'erp_db_user'@'localhost';
FLUSH PRIVILEGES;
Switch to the new erp_db database.
mysql> USE erp_db;
In this sample application, you'll create a products table to store items available for sale in your business. This is a simple resource that you want to protect in your application. While only authenticated users will be able to access it, you don't want anyone to mess around with the price list or probably edit the product names unless they have the right permissions.
To achieve this form of security, you'll later create roles and permissions and define what a specific user can do in regards to the products resource. For instance, you can allow all users to list the items in the products resource and only restrict product updates and deletion to a few administrators.
Create the products table.
mysql> CREATE TABLE products (
product_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(50),
retail_price DOUBLE
) ENGINE = InnoDB;
Populate the products table with some records.
mysql> INSERT INTO products(product_name, retail_price) VALUES ('VIRTUAL PRIVATE SERVER', 6);
INSERT INTO products(product_name, retail_price) VALUES ('BARE METAL', 185);
INSERT INTO products(product_name, retail_price) VALUES ('DEDICATED CLOUD', 60);
Confirm the entries in the products table.
mysql> SELECT
product_id,
product_name,
retail_price
FROM products;
Output.
+------------+------------------------+--------------+
| product_id | product_name | retail_price |
+------------+------------------------+--------------+
| 1 | VIRTUAL PRIVATE SERVER | 6 |
| 2 | BARE METAL | 185 |
| 3 | DEDICATED CLOUD | 60 |
+------------+------------------------+--------------+
3 rows in set (0.00 sec)
You require 3 MySQL tables to create an access control module for your PHP application.
Create the system_permissions table. This table stores your application's permissions in a human-readable format.
mysql> CREATE TABLE system_permissions (
permission_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
permission_name VARCHAR(50)
) ENGINE = InnoDB;
Populate the system_permissions table. Since you only have 1 resource for demonstration purposes, you'll create 4 permissions. In a real-life application with lots of resources, you might require hundreds or even thousands of permissions depending on the complexity of the software.
mysql> INSERT INTO system_permissions(permission_name) VALUES ('CREATE PRODUCT');
INSERT INTO system_permissions(permission_name) VALUES ('EDIT PRODUCT');
INSERT INTO system_permissions(permission_name) VALUES ('DELETE PRODUCT');
INSERT INTO system_permissions(permission_name) VALUES ('LIST PRODUCTS');
Verify the new permissions.
mysql> SELECT
permission_id,
permission_name
FROM system_permissions;
Output.
+---------------+-----------------+
| permission_id | permission_name |
+---------------+-----------------+
| 1 | CREATE PRODUCT |
| 2 | EDIT PRODUCT |
| 3 | DELETE PRODUCT |
| 4 | LIST PRODUCTS |
+---------------+-----------------+
4 rows in set (0.00 sec)
Create a roles table. A role allows you to group multiple permissions together.
mysql> CREATE TABLE roles (
role_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
role_name VARCHAR(50)
) ENGINE = InnoDB;
Populate the roles table with 3 records. You'll later assign permissions to these roles.
mysql> INSERT INTO roles(role_name) VALUES ('ERP MANAGER');
INSERT INTO roles(role_name) VALUES ('LEVEL 1 ADMIN');
INSERT INTO roles(role_name) VALUES ('ERP CLERKS');
Confirm the records from the roles table.
mysql> SELECT
role_id,
role_name
FROM roles;
Output.
+---------+---------------+
| role_id | role_name |
+---------+---------------+
| 1 | ERP MANAGER |
| 2 | LEVEL 1 ADMIN |
| 3 | ERP CLERKS |
+---------+---------------+
3 rows in set (0.00 sec)
Please note: Although you've defined 3 roles in this sample application, there is no limit to the number of system users that you can later tie to them. For instance, in a real-life scenario, you can have 5 ERP MANAGERs, 12 LEVEL 1 ADMINs, and 125 ERP clerks.
Next, create a system_permission_to_roles table. You'll use this table to assign permissions to each role. Here, don't repeat the names of the permission and roles. You'll simply use the role_id and permission_id to optimize the table and avoid bloating your database. In simple terms, you're creating a many-to-many relationship between the roles and system_permissions tables. This is because a single role can have many different permissions and you can assign single permission to many different roles.
mysql> CREATE TABLE system_permission_to_roles (
ref_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
role_id BIGINT,
permission_id BIGINT
) ENGINE = InnoDB;
For now, you'll create the following roles and permissions for your sample application.
ERP MANAGER: Has a right to CREATE PRODUCT, EDIT PRODUCT, LIST PRODUCTS, and DELETE PRODUCT.
LEVEL 1 ADMIN: Has a right to CREATE PRODUCT and LIST PRODUCTS.
ERP CLERKS: Has a right to LIST PRODUCTS only.
Add those roles and their respective permissions into the system_permission_to_roles table.
The ERP MANAGER's permissions:
mysql> INSERT INTO system_permission_to_roles (role_id, permission_id) VALUES (1, 1);
INSERT INTO system_permission_to_roles (role_id, permission_id) VALUES (1, 2);
INSERT INTO system_permission_to_roles (role_id, permission_id) VALUES (1, 3);
INSERT INTO system_permission_to_roles (role_id, permission_id) VALUES (1, 4);
The LEVEL 1 ADMIN's permissions:
mysql> INSERT INTO system_permission_to_roles (role_id, permission_id) VALUES (2, 1);
INSERT INTO system_permission_to_roles (role_id, permission_id) VALUES (2, 4);
The ERP CLERKS' permissions:
mysql> INSERT INTO system_permission_to_roles (role_id, permission_id) VALUES (3, 4);
To log in(authenticate) to your system, a system user will require a username and a password. To provide the access control functionality, you'll later in this guide assign such users some roles depending on the privileges that you want to assign to them(authorization).
Create a system_users table. This table stores the real names, usernames, and hashed passwords for the system users.
mysql> CREATE TABLE system_users (
user_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
username VARCHAR(50),
pwd VARCHAR(255)
) ENGINE = InnoDB;
Add some records into the system_users table. For demonstration purposes, all the 3 users are using the same password(EXAMPLE_PASSWORD) hashed with the bcrypt library. In a production environment, you should design a register script to allow users to sign up to your application and this is where you should hash the passwords before posting them in the system_users table using the syntax `passwordhash(SAMPLEPASSWORD, PASSWORD_BCRYPT).
For now, populate the system_users table with the pre-hashed EXAMPLE_PASSWORD password.
mysql> INSERT INTO system_users (first_name, last_name, username, pwd) VALUES ('JOHN', 'DOE', 'john_doe','$2a$12$JOe5OFLD9dFkI.KJ1k9TP.ixWX/YtYArB/Yv.A8XSeIcCBkIlPvoi');
INSERT INTO system_users (first_name, last_name, username, pwd) VALUES ('MARY', 'SMITH', 'mary_smith','$2a$12$JOe5OFLD9dFkI.KJ1k9TP.ixWX/YtYArB/Yv.A8XSeIcCBkIlPvoi');
INSERT INTO system_users (first_name, last_name, username, pwd) VALUES ('STEVE', 'RIA', 'steve_ria','$2a$12$JOe5OFLD9dFkI.KJ1k9TP.ixWX/YtYArB/Yv.A8XSeIcCBkIlPvoi');
Confirm the records in the system_users table.
mysql> SELECT
user_id ,
first_name,
last_name,
username,
pwd
FROM system_users;
Output.
+---------+------------+-----------+------------+--------------------------------------------------------------+
| user_id | first_name | last_name | username | pwd |
+---------+------------+-----------+------------+--------------------------------------------------------------+
| 1 | JOHN | DOE | john_doe | $2a$12$JOe5OFLD9dFkI.KJ1k9TP.ixWX/YtYArB/Yv.A8XSeIcCBkIlPvoi |
| 2 | MARY | SMITH | mary_smith | $2a$12$JOe5OFLD9dFkI.KJ1k9TP.ixWX/YtYArB/Yv.A8XSeIcCBkIlPvoi |
| 3 | STEVE | RIA | steve_ria | $2a$12$JOe5OFLD9dFkI.KJ1k9TP.ixWX/YtYArB/Yv.A8XSeIcCBkIlPvoi |
+---------+------------+-----------+------------+--------------------------------------------------------------+
3 rows in set (0.00 sec)
Create a system_users_to_roles table. You'll use this table to tie up users to the roles you've created in the previous steps.
mysql> CREATE TABLE system_users_to_roles (
ref_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT,
role_id BIGINT
) ENGINE = InnoDB;
Assign users to the respective roles depending on the permissions that you want to inherit..
Assign JOHN DOE to the ERP MANAGER's role:
mysql> INSERT INTO system_users_to_roles (user_id,role_id) VALUES (1, 1);
Assign MARY SMITH to the LEVEL 1 ADMIN role:
mysql> INSERT INTO system_users_to_roles (user_id,role_id) VALUES (2, 2);
Assign STEVE RIA to the ERP CLERKs role:
mysql> INSERT INTO system_users_to_roles (user_id,role_id) VALUES (3, 3);
Verify if you've assigned the roles correctly by running a JOIN statement against the two tables.
mysql> SELECT
system_users.user_id,
system_users.username,
system_users.first_name,
system_users.last_name,
system_users_to_roles.role_id,
roles.role_name
FROM system_users
LEFT JOIN system_users_to_roles
ON system_users.user_id = system_users_to_roles.user_id
LEFT JOIN roles
ON system_users_to_roles.role_id = roles.role_id;
As you can see from the following output, you were able to match the users to the respective roles correctly.
+---------+------------+------------+-----------+---------+---------------+
| user_id | username | first_name | last_name | role_id | role_name |
+---------+------------+------------+-----------+---------+---------------+
| 1 | john_doe | JOHN | DOE | 1 | ERP MANAGER |
| 2 | mary_smith | MARY | SMITH | 2 | LEVEL 1 ADMIN |
| 3 | steve_ria | STEVE | RIA | 3 | ERP CLERKS |
+---------+------------+------------+-----------+---------+---------------+
3 rows in set (0.00 sec)
Log out from the MySQL server.
mysql> EXIT;
You've set up the appropriate data tables for managing access control in your MySQL database. You'll now create a web resource using PHP. When users access your sample application, you'll only allow them to perform tasks depending on the permissions tied to their assigned roles.
Create a new db_function.php file under the root directory of your web server.
$ sudo nano /var/www/html/db_function.php
Paste the information below into the file. In this file, you're defining variables to connect to your database via the PHP Data Object(PDO). This file has a single pdoConnection() function that you'll use later in this guide. Change EXAMPLE_PASSWORD with the correct password.
<?php
function pdoConnection() {
try {
$db_name = 'erp_db';
$db_user = 'erp_db_user';
$db_password = 'EXAMPLE_PASSWORD';
$db_host = 'localhost';
$pdo = new PDO('mysql:host=' . $db_host . '; dbname=' . $db_name, $db_user, $db_password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $pdo;
} catch (PDOException $e) {
echo $e->getMessage();
}
}
Save and close the file.
Next, create an authentications.php script. This script handles the authentication part of your application. It checks whether a user has a valid username and password.
$ sudo nano /var/www/html/authentications.php
Paste the following information into the file. In this file, you'll retrieve the user_id from the MySQL database and assign it to the variable $user_id which you'll later use to check the permissions that a user has in the system. You're accomplishing this using the statement $user_id = $user_details['user_id'];.
<?php
if (!isset($_SERVER['PHP_AUTH_USER']) || !isset($_SERVER['PHP_AUTH_PW'])) {
header("HTTP/1.0 401 Unauthorized");
echo '"{"error": "Authentication failed."}';
exit();
} else {
$username = $_SERVER['PHP_AUTH_USER'];
$password = $_SERVER['PHP_AUTH_PW'];
}
$sql_authenticate = 'select * from system_users where username = :username';
$data = [
'username' => $username
];
$stmt = $pdo->prepare($sql_authenticate);
$stmt->execute($data);
$user_details = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$user_details = $row;
}
if (password_verify($password, $user_details['pwd']) == true) {
$user_id = $user_details['user_id'];
} else {
header("HTTP/1.0 401 Unauthorized");
echo '{"error": "Invalid username or password."}';
exit();
}
Save and close the file.
Create a system_permissions.php script.
$ sudo nano /var/www/html/system_permissions.php
Paste the information below into the file. In this file, you're checking if a user has the right permissions to perform a certain task by running a JOIN statement against the system_users_to_roles and system_permission_to_roles tables. This function accepts two variables. To check if authenticated users have permissions to perform a certain task, you simply send their user_id that you've retrieved from the authentications.php script and the permission_id of a resource to this function. The function then returns either true or false and allows you to either restrict or grant access to the resource.
<?php
function checkPermissions($user_id, $permission_id) {
require_once('db_function.php');
$pdo = pdoConnection();
try {
$sql = 'select
count(*) as total_permissions
from system_permission_to_roles
left join system_users_to_roles
on system_permission_to_roles.role_id = system_users_to_roles.role_id
where system_users_to_roles.user_id = :user_id
and permission_id = :permission_id
';
$data = [
'user_id' => $user_id,
'permission_id' => $permission_id
];
$stmt = $pdo->prepare($sql);
$stmt->execute($data);
$row = $stmt->fetch();
$authorized = '';
if ($row['total_permissions'] > 0) {
$authorized = "true";
} else {
$authorized = "false";
}
return $authorized;
} catch (Exception $e) {
echo $e->getMessage();
}
}
Save and close the file.
Create the products.php resource file. This is the main endpoint that you want to secure with authentication and authorization features. Users accessing your application will request this page through a client application(For example, a mobile app or desktop software). For this guide, you'll later use curl to retrieve the page.
$ sudo nano /var/www/html/products.php
Paste the information below into the file. Please note, this guide doesn't address data validation in your PHP files. When working in a production environment, consider a form of data validation. To understand the CRUD (Create, Read, Update, or Delete)function the user wants to perform in your application, you're using the statement $http_verb = $_SERVER['REQUEST_METHOD'];.
Then, you're checking if a user is authorized to perform the task using the statement if (checkPermissions($user_id, 1) == "false") {...}.
<?php
require_once('db_function.php');
require_once('system_permissions.php');
$pdo = pdoConnection();
$http_verb = $_SERVER['REQUEST_METHOD'];
$params = json_decode(file_get_contents('php://input'));
require_once('authentications.php');
if ($http_verb == 'POST') {
if (checkPermissions($user_id, 1) == "false") {
header("HTTP/1.0 403 Forbidden");
echo '{"error": "You do not have permissions to create a product."}' . '\n';
exit();
}
$sql = 'insert into products (
product_name,
retail_price
) values (
:product_name,
:retail_price
)';
$data = [
'product_name' => $params->product_name,
'retail_price' => $params->retail_price
];
$stmt = $pdo->prepare($sql);
$stmt->execute($data);
echo '{"message": "Product created successfully."}' . '\n';
} elseif ($http_verb == 'GET') {
$me = checkPermissions($user_id, 4);
if (checkPermissions($user_id, 4) == "false") {
header("HTTP/1.0 403 Forbidden");
echo '{"error": "You do not have permissions to list products."}' . '\n';
exit();
}
$sql = 'select * from products';
$stmt = $pdo->prepare($sql);
$stmt->execute();
$data = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$data[] = $row;
}
$reponse = [];
$response['data'] = $data;
echo json_encode($response, JSON_PRETTY_PRINT) . "\n";
} elseif ($http_verb == 'PUT') {
if (checkPermissions($user_id, 2) == "false") {
header("HTTP/1.0 403 Forbidden");
echo '{"error": "You do not have permissions to update a product."}' . '\n';
exit();
}
$sql = 'update products set
product_name = :product_name,
retail_price = :retail_price
where product_id = :product_id
';
$data = [
'product_id' => $params->product_id,
'product_name' => $params->product_name,
'retail_price' => $params->retail_price
];
$stmt = $pdo->prepare($sql);
$stmt->execute($data);
echo '{"message": "Product updated successfully"}' . '\n';
} elseif ($http_verb == 'DELETE') {
if (checkPermissions($user_id, 3) == "false") {
header("HTTP/1.0 403 Forbidden");
echo '{"error": "You do not have permissions to delete a product."}';
exit();
}
$sql = 'delete from products
where product_id = :product_id
limit 1
';
$data = [
'product_id' => $params->product_id
];
$stmt = $pdo->prepare($sql);
$stmt->execute($data);
echo '{"message": "Product deleted successfully."}' . '\n';
}
Save and close the file.
Your database tables and PHP scripts are now in place.
Use curl to access the URL http://localhost/products.php. Begin by entering the wrong credentials to check whether the authentication logic is working.
$ curl -u john_doe:WRONG_PASSWORD http://localhost/products.php
Since you've entered the WRONG_PASSWORD for user john_doe, you should receive the following error.
{"error": "Invalid username or password."}
Next, attempt to list products using the 3 sample users. This time around, use the correct password. In this case, EXAMPLE_PASSWORD.
$ curl -u john_doe:EXAMPLE_PASSWORD http://localhost/products.php
$ curl -u mary_smith:EXAMPLE_PASSWORD http://localhost/products.php
$ curl -u steve_ria:EXAMPLE_PASSWORD http://localhost/products.php
Since all the 3 users have the 'LIST PRODUCTS' permission, you should get the following output after running the above curl commands.
{
"data": [
{
"product_id": "1",
"product_name": "VIRTUAL PRIVATE SERVER",
"retail_price": "6"
},
{
"product_id": "2",
"product_name": "BARE METAL",
"retail_price": "185"
},
{
"product_id": "3",
"product_name": "DEDICATED CLOUD",
"retail_price": "60"
}
]
}
Next, you've only granted the CREATE PRODUCT permission to john_doe and mary_smith. Execute the following curl command using those users' credentials to add more products into your database.
$ curl -u john_doe:EXAMPLE_PASSWORD --header "Content-Type: application/json" --request POST --data '{"product_name":"SAMPLE_PRODUCT_4","retail_price":"99.99"}' http://localhost/products.php
$ curl -u mary_smith:EXAMPLE_PASSWORD --header "Content-Type: application/json" --request POST --data '{"product_name":"SAMPLE_PRODUCT_5","retail_price":"299.20"}' http://localhost/products.php
Since the 2 users have the right permission, you should get the output below.
...
{"message": "Product created successfully."}
Verify if you've successfully inserted the products into the database by listing them one more time.
$ curl -u john_doe:EXAMPLE_PASSWORD http://localhost/products.php
As you can see from the output below, you've added the SAMPLE_PRODUCT_4 and SAMPLE_PRODUCT_5 into the products table.
{
"data": [
{
"product_id": "1",
"product_name": "VIRTUAL PRIVATE SERVER",
"retail_price": "6"
},
{
"product_id": "2",
"product_name": "BARE METAL",
"retail_price": "185"
},
{
"product_id": "3",
"product_name": "DEDICATED CLOUD",
"retail_price": "60"
},
{
"product_id": "4",
"product_name": "SAMPLE_PRODUCT_4",
"retail_price": "99.99"
},
{
"product_id": "5",
"product_name": "SAMPLE_PRODUCT_5",
"retail_price": "299.2"
}
]
}
Attempt to create a product with steve_ria's credentials.
$ curl -u steve_ria:EXAMPLE_PASSWORD --header "Content-Type: application/json" --request POST --data '{"product_name":"SAMPLE_PRODUCT_6","retail_price":"340.63"}' http://localhost/products.php
Since you've not granted steve_ria's the CREATE PRODUCT permission, the operation should fail with the following error.
{"error": "You do not have permissions to create a product."}
Next, only john_doe( The ERP MANAGER) can update or delete products. Execute the commands below with his credentials.
$ curl -u john_doe:EXAMPLE_PASSWORD --header "Content-Type: application/json" --request PUT --data '{"product_id":"1","product_name":"VPS MACHINE","retail_price":"6.00"}' http://localhost/products.php
$ curl -u john_doe:EXAMPLE_PASSWORD --header "Content-Type: application/json" --request DELETE --data '{"product_id":"5"}' http://localhost/products.php
You should be able to update and delete the product and get the following responses.
{"message": "Product updated successfully"}
{"message": "Product deleted successfully."}
Verify whether you've updated and deleted the products by listing the products.
$ curl -u john_doe:EXAMPLE_PASSWORD http://localhost/products.php
You've indeed updated the first product from VIRTUAL PRIVATE SERVER to VPS MACHINE and you've successfully deleted SAMPLE_PRODUCT_5.
"data": [
{
"product_id": "1",
"product_name": "VPS MACHINE",
"retail_price": "6"
},
{
"product_id": "2",
"product_name": "BARE METAL",
"retail_price": "185"
},
{
"product_id": "3",
"product_name": "DEDICATED CLOUD",
"retail_price": "60"
},
{
"product_id": "4",
"product_name": "SAMPLE_PRODUCT_4",
"retail_price": "99.99"
}
]
}
However, if you attempt to either update or delete a product with the mary_smith's or steve_ria's credentials, the operations should fail.
$ curl -u mary_smith:EXAMPLE_PASSWORD --header "Content-Type: application/json" --request PUT --data '{"product_ID":"1","product_name":"VPS MACHINE","retail_price":"6.00"}' http://localhost/products.php
$ curl -u mary_smith:EXAMPLE_PASSWORD --header "Content-Type: application/json" --request DELETE --data '{"product_ID":"5"}' http://localhost/products.php
$ curl -u steve_ria:EXAMPLE_PASSWORD --header "Content-Type: application/json" --request PUT --data '{"product_ID":"1","product_name":"VPS MACHINE","retail_price":"6.00"}' http://localhost/products.php
$ curl -u steve_ria:EXAMPLE_PASSWORD --header "Content-Type: application/json" --request DELETE --data '{"product_ID":"5"}' http://localhost/products.php
Output.
{"error": "You do not have permissions to update a product."}
{"error": "You do not have permissions to delete a product."}
{"error": "You do not have permissions to update a product."}
{"error": "You do not have permissions to update a product."}
Your permission settings are now working as expected.
You've seen the power of role-based access control in your application. Here are a few real-life scenarios where the technology is used.
In a banking application, cashiers have the right to deposit and withdrawal money in the system. However, the reversal right for erroneously entered data may be preserved to supervisors only. Also, only a loan officer can disburse loans to avoid fraud.
In a content management system where users are paid to submit articles to a blog, an editor may be assigned the right to approve the articles while an accountant may be the only authorized person to make payments for the completed articles.
In a grocery store software, cashiers can ring sales and check their end-of-day totals but they can't delete a sales record unless it is done by the manager(For instance, during a return).
In this guide, you've implemented role-based access control with PHP and MySQL on your Ubuntu 20.04 server. Implement the knowledge in this guide to offer tight authentication and authorization when designing your next PHP project.
Refer to the documents below for more information on using PHP and PDO.