Login script. Join table "user" to table "admin"

[Andy_Kemp]

Admin panel login. Am I doing it right?

$query = 'SELECT a.id, a.username, b.password AS admin_password, b.status AS admin_status
	  FROM user AS a
	  INNER JOIN admin AS b
	  ON a.id = b.user_id
	  WHERE a.username = :username';

Table name: user
status - active, inactive, un-verified, suspended

+ -- + -------- + --------- + --------------- + ------- + ---------- + ----------- +           + -------- +
| id | username | password  | email           | last_ip | last_login | last_active | ......... | status   |
+ -- + -------- + --------- + --------------- + ------- + ---------- + ----------- +           + -------- +
| 1  | user_1   | long_hash | email@email.com | ::1     | 0123456789 | 0123456789  | ......... | active   |
+ -- + -------- + --------- + --------------- + ------- + ---------- + ----------- +           + -------- +
| 2  | user_2   | long_hash | email@email.com | ::1     | 0123456789 | 0123456789  | ......... | inactive |
+ -- + -------- + --------- + --------------- + ------- + ---------- + ----------- +           + -------- +

Table name: admin
status - enabled, disabled

+ -- + -------- + --------- + ------- + ---------- + ----------- + -------- +
| id | user_id  | password  | last_ip | last_login | last_active | status   |
+ -- + -------- + --------- + ------- + ---------- + ----------- + -------- +
| 1  | 1        | long_hash | ::1     | 0123456789 | 0123456789  | enabled  |
+ -- + -------- + --------- + ------- + ---------- + ----------- + -------- +
| 2  | 2        | long_hash | ::1     | 0123456789 | 0123456789  | disabled |
+ -- + -------- + --------- + ------- + ---------- + ----------- + -------- +

[ginerjm]

What you are showing seems to be right. Could improve it by adding a check of the password in the where clause and not selecting the password as one of your results.

[maxxd]

Respectfully have to disagree with ginerjm.

 

I wouldn't set it up this way at all. You're duplicating a lot of information between the two tables. Is there a reason you don't add a user type column ('admin', 'public', 'superadmin', etc) in the Users table and then simply checking the user type and setting a permissions level based on that value? You can then drop the admin table entirely and replace it with a simple user type table.

 

Also, as long as you're using password_hash() and password_verify(), selecting the password from the database is perfectly acceptable, and will save you headaches while dealing with user passwords overall.

[Andy_Kemp]

Added role_id to user table

+ -- + -------- + --------- + --------------- + ------- + ---------- + ----------- +           + ------- + -------- +
| id | username | password  | email           | last_ip | last_login | last_active | ......... | role_id | status   |
+ -- + -------- + --------- + --------------- + ------- + ---------- + ----------- +           + ------- + -------- +
| 1  | user_1   | long_hash | email@email.com | ::1     | 0123456789 | 0123456789  | ......... | 1       | active   |
+ -- + -------- + --------- + --------------- + ------- + ---------- + ----------- +           + ------- + -------- +
| 2  | user_2   | long_hash | email@email.com | ::1     | 0123456789 | 0123456789  | ......... | 2       | inactive |
+ -- + -------- + --------- + --------------- + ------- + ---------- + ----------- +           + ------- + -------- +

Table name: role

+ -- + ----------- + ----------- + 
| id | name        | description | 
+ -- + ----------- + ----------- + 
| 1  | super_admin | Super Admin | 
+ -- + ----------- + ----------- + 
| 2  | user        | User        | 
+ -- + ----------- + ----------- + 

Table name: permission

+ -- + ----------- + ----------- + 
| id | name        | description | 
+ -- + ----------- + ----------- + 
| 1  | edit_news   | Edit News   | 
+ -- + ----------- + ----------- + 
| 2  | manage_news | Manage News | 
+ -- + ----------- + ----------- + 

Table name: role_permission

+ -- + -------- + ------------ + 
| id | role_id | permission_id | 
+ -- + ------- + ------------- + 
| 1  | 1       | 1             | 
+ -- + ------- + ------------- + 
| 2  | 1       | 2             | 
+ -- + ------- + ------------- + 

Do i need to add login page to user and admin side?

I have no idea what to do next.

[maxxd]

That looks good!

 

With this setup, you can assume that all non-logged in users are role 'user', and those users that do log in successfully will get their roles assigned according to the role ID associated with their user record. Then you can check the permission or role_permission before any action is taken or restricted data is displayed.

 

Make sense?

[mac_gyver]

Do i need to add login page to user and admin side?

 

 

you need to add the login form/form processing at any point that you expect a user to be logged in and she/he isn't. if you are at the point of needing a user permission system, you should probably be at the point of having a web site that handles all the processing/content through one main file, not though having a separate file for each different thing your site does.

 

when a user logs in, you are authenticating who they are. this only involves matching the username and the hashed password in the user table and storing the user id in a session variable.

 

to determine what a user may do or see on any page request, you would take the user id from your log in system and retrieve their current permissions. the reason to do this on each page request is so that any changes made to the permissions will take effect immediately. the code on your page would test if the current user has permission to perform any action or view any content that you have defined in your permission table.

[Psycho]

One suggestion:

 

I assume the super_admin role would have full permissions and should never have limited permissions. So, you don't need to have any records in the role_permissions table for the super_admin. Instead, if the role is super_admin just assume all permissions are available. The reason for this is if any records from the role_permissions table were inadvertently deleted the super_admin could lose the ability to do things that no one has the rights to do. This basically ensure the system doesn't come to a halt.

 

You would likely have a service to check a permission for a user based on their role. That service would simply return true is the user is the super_admin instead of checking the role assignments.

[maxxd]

One suggestion:

 

I assume the super_admin role would have full permissions and should never have limited permissions. So, you don't need to have any records in the role_permissions table for the super_admin. Instead, if the role is super_admin just assume all permissions are available. The reason for this is if any records from the role_permissions table were inadvertently deleted the super_admin could lose the ability to do things that no one has the rights to do. This basically ensure the system doesn't come to a halt.

 

You would likely have a service to check a permission for a user based on their role. That service would simply return true is the user is the super_admin instead of checking the role assignments.

 

Question on this set-up: how would you then differentiate between a super-user and an anonymous user? Because - the way it's occurring to me right now, and admittedly it's been a long day - is that the anonymous user wouldn't come back with a record from table users, so would have no permissions to store or check. If there are no permissions set up for super-admin, wouldn't that turn every anonymous user into a super-admin? Unless we're simply talking back-end stuff where the user would have to log in in order to see any of it at all; whereupon sure, that makes some sense. But even then, isn't it a better idea to enable an additional level of security whereupon you'd actively check that the super admin user has the permissions to do pretty much anything they want, rather than assume that a user with an active login session but no permissions is legit and not just partially hijacked?

 

Or am I just missing something blatantly obvious?

[Psycho]

Question on this set-up: how would you then differentiate between a super-user and an anonymous user? Because - the way it's occurring to me right now, and admittedly it's been a long day - is that the anonymous user wouldn't come back with a record from table users, so would have no permissions to store or check. If there are no permissions set up for super-admin, wouldn't that turn every anonymous user into a super-admin? Unless we're simply talking back-end stuff where the user would have to log in in order to see any of it at all; whereupon sure, that makes some sense. But even then, isn't it a better idea to enable an additional level of security whereupon you'd actively check that the super admin user has the permissions to do pretty much anything they want, rather than assume that a user with an active login session but no permissions is legit and not just partially hijacked?

 

Or am I just missing something blatantly obvious?

 

Not, to be rude but, yes, I think you are missing something. I am not talking about treating the "super admin" as an anonymous user by any means. Based on one of the OP's previous responses he intends to have "roles" that can be customized to have different permissions granted or not. The Roles table would still have a record for the Super Admin role (id = 1) and the same tables the OP described above would still exists to describe the permissions available for all the other role types (but not the super admin). Upon login, the system would store the role id in a session variable and upon attempting to access any functionality that requires a specific permission a service would be called to check if the user is assigned to a role with rights to that functionality. That service could look like this:

 

//Very generalized code

function checkPermision($roleID, $permissionID)
{
    //Always return true for super-admin
    if($roleID==1) { return true; }
 
    //Else, query the DB to determine if the user's role has rights to the permission

    //Will return 1 if role has the permission, else 0
    $query = "SELECT COUNT(*)
              FROM role_permission
              WHERE role_id = $roleID
                AND permission_id = '$permissionID'";
    $result = mysqli_query($link, $query);
    $mysqli->query($query)
    $row = $result->fetch_row())
    //Retuyrn 1/0 (i.e. true/false)
    return $row[0];

}

The reason to do this is it ensure that the super admin does not lose permission because the role based permissions are accidentally deleted, corrupted, whatever. Imagine the scenario where only the super admin has permissions to edit roles and somehow that permission got deleted for the super admin. There would be no way to resolve the issue through the application. It would require someone with knowledge of the application and access to the DB to create a query to manually add/edit the necessary records. This could be catastrophic if it were to occur when the application is mission critical. Sure, you can build the application to prevent such things, but those pesky business rules to mandate very narrow logic such as that are the ones that get tested the least. And - bugs happen. By always returning true for the Super Admin when checking permissions you do not need to worry about any permission being removed either intentionally or by accident.

Edited February 18, 2016 by Psycho

[maxxd]

Not, to be rude but, yes, I think you are missing something.

 

Not rude at all - it was a genuine question and thanks for taking the time to respond!

 

The reason to do this is it ensure that the super admin does not lose permission because the role based permissions are accidentally deleted, corrupted, whatever. Imagine the scenario where only the super admin has permissions to edit roles and somehow that permission got deleted for the super admin. There would be no way to resolve the issue through the application. It would require someone with knowledge of the application and access to the DB to create a query to manually add/edit the necessary records. This could be catastrophic if it were to occur when the application is mission critical. Sure, you can build the application to prevent such things, but those pesky business rules to mandate very narrow logic such as that are the ones that get tested the least.

 

Very true. I guess I rely too much on the idea that someone will be there with the system and database knowledge to fix a mission critical error. Which I really shouldn't do.

 

And - bugs happen.

 

Well now that's just crazy talk...

 

Thanks for all that - interesting ideas and excellent explanation!

Edited February 18, 2016 by maxxd

[Andy_Kemp]

Whats the best method to determine who can access to admin panel?

<?php

if (!defined('Test_script')) {
    exit('You don\'t have right permission to access this file directly.');
}

if (!is_logged_in($ui)) {
    redirect_to('../index.php?do=login');
}

# By user role
if (!user_has_role($db, $ui['id'], return_role_id($db, 'user'))) {
    redirect_to('../index.php?do=home');
}
# Or by permission
if (!user_has_permission($db, $ui['id'], return_permission_id($db, 'admin_access'))) {
    redirect_to('../index.php?do=home');
}

/* ............................... */

?>

[maxxd]

That's entirely up to you. The roles imply permissions (unless you're also tracking permissions on a per-user basis, at which point the roles become a bit unnecessary) so which way you label it is a semantic decision.