CODEWITHSUNDEEP
phpmysqlauthentication
Carlos
Carlos

Reputation: 23

Roles and permissions in php

I am making a menu, but I want to limit it to only some users with a specific permission can see it.

the query:

SELECT idpermission,userid FROM user_permissions WHERE userid = "U001"

Result: User U001 has 3 permissions stored.

idpermission,userid
 IDP001,U001
 IDP002,U001
 IDP003,U001

I have 3 tables (users,permissions and user_permissions), in user_permissions I store each permission of each user

Example:

Table

user | permission | user_permissions

result:

user: u001,carlos,carlos@...

permissions: IDP001 = book | IDP002 = create_book | IDP003 = edit_book | IDP004 = user | IDP005 = edit_user...

user_permissions:
IDP001,U001
IDP002,U001
IDP003,U001
IDP001,U002
IDP003,U002...

when i call the query

while($sqlRow=$sqlPermissions->fetch(PDO::FETCH_ASSOC))
 {
   if ($sqlRow['idpermission'] == 'IDP001' || $sqlRow['idpermission'] == 'IDP002' || $sqlRow['idpermission'] == 'IDP003')
     {
        echo "user menu ";
     }
 }

but the result that appears to me is 3:

user menu user menu user menu

You should only show me one, making only a comparison if you have such permission shows but shows nothing.

What I want to reach is that the user can have many permissions for different items on the menu, such as view, edit, create, delete, each one is a permission, and each item (user menu, books menu) is another permission.

Upvotes: 2

Views: 5787

Answers (2)

Uzair Hayat
Uzair Hayat

Reputation: 540

I'm not sure if your question was lacking information, or I may have missed a point.

Your example uses U001 as a user id. The while loop seems to be correct in showing three "user menu" echos as U001 has permission to view IDP001, IDP002, and IDP003.

If however, you try viewing U002, considering U002 does not have permission to view IDP002, and IDP003. You won't see three echos.

However, I'd approach this a little differently than you did. Maybe this might make things simpler for you.

$userPermissions = array(
    'book'        => 1,
    'create_book' => 0,
    'edit_book'   => 0
);

$menuItems = array(
    'book' => 'View Booking',
    'create_book' => 'Create Booking',
    'edit_book' => 'Edit Booking'
);

foreach ($menuItems as $key => $value) {

    if ($userPermissions[$key]) {
        echo $value;
    }

}

In the above scenario, you can store $userPermissions as serialized in your database. Then, once it's needed, get it from the Database and using the foreach loop create a menu by echoing the values of $menuItems if the user has permission to view it.

Your table (from my example) may look something like this:

id | user_id | permissions

Upvotes: 0

mickmackusa
mickmackusa

Reputation: 47883

Use a pivot technique to gather all permissions for a single user and form a single row to fetch with pdo.

This way all expected columns are declared and you can use simple truthy/falsey conditional checks in your php for any of the permission settings.

So long as the userid exists in the table, you will have a fully populated row to access. This will be clean, direct, efficient, and easy to maintain.

Schema (MySQL v5.7)

CREATE TABLE user_permissions ( 
  idpermission VARCHAR(20),
  userid VARCHAR(20)
);

INSERT INTO user_permissions VALUES
('IDP001', 'U001'),
('IDP002', 'U001'),
('IDP003', 'U001'),
('IDP001', 'U002'),
('IDP003', 'U002');

Query #1

SELECT MAX(IF(idpermission = 'IDP001', 1, 0)) AS book,
       MAX(IF(idpermission = 'IDP002', 1, 0)) AS create_book,
       MAX(IF(idpermission = 'IDP003', 1, 0)) AS edit_book,
       MAX(IF(idpermission = 'IDP004', 1, 0)) AS user,
       MAX(IF(idpermission = 'IDP005', 1, 0)) AS edit_user
FROM user_permissions
WHERE userid = 'U001'
GROUP BY userid;

Result set:

| book | create_book | edit_book | user | edit_user |
| ---- | ----------- | --------- | ---- | --------- |
| 1    | 1           | 1         | 0    | 0         |

View on DB Fiddle

Upvotes: 1

Related Questions