How to organize user credential in DB?

Question

What would be the best approach to storing user credentials in MySQL database. I mean structure, not the data (hash vs clean text).

I have site where we got

Root Admin (1)

Client Admin (1) 
Client Sub-Admins (N)

Employer Admin (N)
Employer Sub-Admins (N)

Above users login at back end page

Users (N)

Users login at front end of the site

N - means unlimited

Guess my question is should i put all admins in same table? However employers are not so trusted as Client Admins.

If i put admins in different tables, how i do authentication in PHP? Take data from both tables and merge into array then look in array if user exists?

Is there such a fundamental difference between an admin and a user that you can't just have a `rank` field? — Waleed Khan, Oct 23 '12 at 19:04
Not a god security practice in my opinion to have user and admin in same table or even same login page. Besides if pages and tables different i can limit admin page by IP address to only trusted people. — John Smith, Oct 23 '12 at 19:11
Also functional difference, admin don't do same things as user and we dont need confuse ourselves with admin entries in user table. About all admin admin needs is user name and password opposite to contact info address dates etc that goes in user table. — John Smith, Oct 23 '12 at 19:19
I am not opposed to the idea of separating administrators and users, but, if they could be stored in the same table, what makes you say that it is a bad idea to have admins and users in the same table? — Waleed Khan, Oct 23 '12 at 19:57
As i said above i dont have the same data for admin as for user. e.g. i have to create extra fields in user table for admin access to certain features of site that user dont need and its just cleaner to separate this in two tables. If you have 5000 users on site you have to keep user table as small as possible for performance. Also from security perspective with all forms of web attacks i just cant sleep safe when admins are in user table, its like putting president of US in the same open building where regular people live and hope he be safe there. I can limit access to admin table by IP. — John Smith, Oct 24 '12 at 18:55
*Also from security perspective with all forms of web attacks i just cant sleep safe when admins are in user table* I am asking why you think it is a bad idea to put admins and users in the same table. I can't imagine any web attacks that would be thwarted by putting them in different tables. — Waleed Khan, Oct 24 '12 at 19:33
I said " I can limit access to admin table by IP" meaning any admin pages. e.g folder mysite.com/admin/index.php so anything in folder /admin can be only accessible by users who's IP address i specify. However if i put it in user table then that table open to public they can do SQL injection etc and perhaps steal the admin credentials or try to login with his user credentials to admin table again with SQL injection adding to the end of query smth like " OR 1=1" so it will ignore `rank` field. — John Smith, Oct 24 '12 at 19:42
@JohnSmith The problem is that granting different access rights based on IP address won't prevent SQL injection, if your **code** is vulerable it will happen regardless. I emphasize **code** because the most you can (should) do is at that level. I found the article [SQL Injection Prevention Cheat Sheet](https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet) quite useful. I am not a PHP expert but the top answer to the question [Best way to prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) has really high rating too. — Attila T, Nov 03 '12 at 13:16
@JohnSmith Also, are you planning to maintain N user accounts at the database level? Are you going to synchronize these accounts with the ones at the application level? I'm not implying that it's wrong, it's just common practice that web applications have a single dedicated database account with limited access as opposed to per application user schema. — Attila T, Nov 03 '12 at 13:21

score 3 · Answer 1 · answered Nov 01 '12 at 17:37

If you're only ever going to have a single root/client admin I would suggest simply storing their user_id values separately, possibly in a configuration file that is difficult to modify by something like SQL injection. Otherwise the following scheme should allow you a fair amount of flexibility to assign as many users to as many groups as you want.

TABLE users
  user_id INT PK AUTO_INCREMENT
  user_name VARCHAR
  user_password VARCHAR
  ...

TABLE groups
  group_id INT PK AUTO_INCREMENT
  group_name VARCHAR
  [possible permissions declarations, 
    or create a similar group_perms table]
  ...

TABLE user_group_map
  user_id INT PK
  group_id INT PK

score 1 · Answer 2 · answered Oct 29 '12 at 18:16

1

One way to do it is to separate the authentication/user profile data from its possible array of roles. So, assign roles to your users. Hell, assign contexts to your roles. The way I see it, you have a 1-to-many relation between user and roles, in which the roles are assigned a context (Admin of Employers, Admin of Clients, General Admin...).

answered Oct 29 '12 at 18:16

ChuckE

5,610
4
31
59

and if you need to save more information for an admin than you would need for the average user, please run away from STI, and use Class Table Inheritance. http://community.devexpress.com/forums/p/95346/327589.aspx it just seems the use case. – ChuckE Oct 29 '12 at 18:18

Attila T · Answer 3 · 2012-11-02T19:25:16.820

I would not use multiple tables just to separate users based on how much I trust them. After reading your question and comments in your case you would have to create individual tables for root admins, client admins, employer admins and regular users since your trust level is different for each group. Implementation / maintenance can become really ugly when you start coding even with only two tables...

You seem to be very determined to go with separate tables though and if you choose to do so there are techniques you can "hide" this, for example, create a view to merge these tables, use SQL union in your DAO, or read each table and merge the data programmatically, etc. Again, as you build your application you will most likely have issues with this design.

I would rather use a single table for users and introduce the concept of roles with a many-to-many relation between them. Upon user creation I would associate each user with a default role (eg. user root-admin has a role of root-admin, etc.) along with any other roles that they seem to fit (eg. user cleint-admin has an additional role of client-sub-admin, etc.) Based on the requirements there could be roles that do not relate to users directly, eg. guest, member, etc.

I would also introduce the concept of application contexts with an optional many-to-many relation to roles then initially associate the role root-admin with the application context all meaning that s/he has no restrictions. Additional contexts would be created based on business requirements (eg. role employer-admin is part of the context hire-employees, etc.)

When authorizing, a security manager component can grant / deny access based on the user and its associated roles / contexts with additional logic included if necessary (eg. user is disabled).

Your next concern was that you have additional data for certain users which does not apply to others. In order to resolve this I would introduce the profiles table with an optional one-to-one relation to the users table (not every user has a profile, eg. user root-admin does not).

Schema (draft):

 __________________                    __________________
|USERS             |                  |ROLES             |
|==================|                  |==================|
|id                |                  |id                |
|username          |  1..*      1..*  |name              |
|password          |  --------------  |description       |
|failedattempts    |                  |...               |
|disabled          |                  |                  |
|...               |                  |                  |
|__________________|                  |__________________|

        | 1                                   | 0..*
        |                                     |
        |                                     |
        |                                     |
        | 0..1                                | 0..*
 __________________                    __________________
|PROFILES          |                  |CONTEXTS          |
|==================|                  |==================|
|id                |                  |id                |
|firstname         |                  |name              |
|lastname          |                  |description       |
|email             |                  |...               |
|dob               |                  |                  |
|...               |                  |                  |
|__________________|                  |__________________|

score 0 · Answer 4 · edited May 23 '17 at 11:56

0

I've described a user and group permission system in this post (ignore the title -- the original question was poorly worded): Applying column permissions for a table over a trigger

edited May 23 '17 at 11:56

Community

1
1

answered Nov 04 '12 at 02:25

Gavin Towey

3,132
15
11

How to organize user credential in DB?

4 Answers4