November 23rd, 2013, 04:38 PM
Database with two user types - A query or two tables best?
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.
November 23rd, 2013, 05:57 PM
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.
November 23rd, 2013, 06:12 PM
So would it be right to have:
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.
November 23rd, 2013, 07:48 PM
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.
Originally Posted by DanJames
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.
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.
Originally Posted by DanJames
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.
November 23rd, 2013, 09:10 PM
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):
For example in the e-shop it would look OK if admin could also perform manager tasks.
create table users (
create table roles (
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');