#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    21
    Rep Power
    0

    Thumbs up Database with two user types - A query or two tables best?


    Hi,

    I am making a website with two user types: normal and admin. At first I thought the database would contain a table for normal users and a table for admin users. However, this didn't seem the best way to do it as the unique ID for each wouldn't be the same.

    I decided to make a tblUsers table for all users with a type field which is 0 for normal users and 1 for admins.

    Then I simply ran two queries to get the normal users and admins separate.

    This works very well in MS access, where queries are saved just like tables, but I was wondering is this a good option for use with PHPmyadmin and MySQL on the website. This would mean having to set up a query every time I want to access, say the normal users, in the MySQL code.

    Any tips on if this is the right way to go, or whether I should use two tables for the different user types.

    Thanks,
    Dan!
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    I strongly recommend separating admin accounts and regular accounts as much as possible. Ideally, admins would have their own site, database table and database user.

    This allows for proper access control and additional security features in order to protect the admin accounts. If you just have a flag named "is_admin", the only layer which protects the admins is the application code. Once an attacker manages to get through this layer (like with an SQL injection), there is no difference between a regular user and an admin. In other words, the attacker gets admin privileges for free.

    Note that you should not literally duplicate the user table. Instead, make a table for all admin-related data and link each row to a user.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    21
    Rep Power
    0
    So would it be right to have:

    tblNormalUsers
    tblAdmins

    On one website only show information relating to normal users i.e. only some database data.

    On another website only show information relating to admins i.e. all the database tables.

    My question is, if someone managed to hack into the normal user website, and somehow request information, would they be able to gain information from the tblAdmin table in the database? I'm just not sure how to separate data in databases when all the tables have relations/links between them.

    Anyone that is an admin should be taken to a secure page where they can access all data in the database.

    Anyone that is a normal user should be taken to a page where they can access some data related to them only - but the tables this data is in also contains data of others and is linked to admin data.
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by DanJames
    So would it be right to have:

    tblNormalUsers
    tblAdmins

    On one website only show information relating to normal users i.e. only some database data.

    On another website only show information relating to admins i.e. all the database tables.
    Your table names and the description are rather misleading, because it sounds like regular users and admins are two disjoint groups. But an admin is a regular user with an addional dataset attached.

    What you call "tblAdmins" should be called "tblAdminAccesses" or something. The entries do not represent user accounts. They represent a special role given to a user.



    Originally Posted by DanJames
    My question is, if someone managed to hack into the normal user website, and somehow request information, would they be able to gain information from the tblAdmin table in the database? I'm just not sure how to separate data in databases when all the tables have relations/links between them.
    Links between the tables don't matter. The point is that only the database user for the admin site can access the admin table. The regular database user only has read access to the reference column (so that you can show the admin status on the regular site). It cannot read or change the admin-related data.

    Note that this only makes sense if the admin table contains separate authentication data (password hashes etc.). Otherwise, an attacker could take over an admin account simply by changing the data of the underlying user.

    You could ask the user to define a separate password for the admin area. The hash of it would be stored in the admin table, not accessible to the standard database user. It may sound weird to store two passwords for each admin user, but it actually makes a lot of sense: While the standard password has to be used all the time, the admin password should be used as rarely as possible. The admin password also has to be particularly strong (like in: randomly generated by a password management tool). And it shouldn't be possible to reset it with the usual "I forgot my password" mechanism.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Location
    Saint-Petersburg, Russia
    Posts
    236
    Rep Power
    28
    I should say here are two significantly different approaches and all depends on your business-rules.

    As Jacques1 proposed, one way to go is to separate users and admins as much as possible.

    The other variant looks like what you were describing at beginning.

    Both have their advantages and drawbacks. Separating admins - usually even having dedicate separate application for managing site - it is good for some "superuser" privileges and actions. As an example when you are setting up your web-site via CPanel or SSH - you are acting as superadmin via dedicated application which is not intended for ordinary users. This approach could be extended and you can write your own "admin panel" for your site/webapp.

    On the other hand the way you are talking about is more suitable for "admins" which are participating in more common tasks. And here even could be several types (levels) of such admins. For example, if you create internet-shop you will want to have at least three levels of users:
    - normal user, i.e. buyer;
    - manager, who cares of processing orders, placing products;
    - admin, who's task is to assign managers and delegate rights to them.

    For such business-cases it would create the hell of architecture if you will try to store them all in separate tables, especially if you have more levels and some privileges could be turned on/off for each user (for payment).

    For such case it is better either store the role of user in the same table (as you proposed at first), or in separate table (which will allow to store several roles for any user):

    Code:
    create table users (
        id int,
        username varchar(250),
        pwdhash varchar(250),
        email varchar(250));
    
    create table roles (
        id int,
        userid int,
        role varchar(16));
    
    insert into users (username) values (admin);
    set @t = last_insert_id();
    insert into roles (userid, role) values (@t, 'admin');
    insert into roles (userid, role) values (@t, 'manager');
    For example in the e-shop it would look OK if admin could also perform manager tasks.
    CodeAbbey - programming problems for novice coders

IMN logo majestic logo threadwatch logo seochat tools logo