design users on different levels etc...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

design users on different levels etc...

Post by jmut »

Hi,
Do you think it's worth separating different user levels in defferent tables...I hear some people suggest that, so that we can set the sql injection demage to minimum.
Otherwise I could always use one table with "user_level" column that will be e.g (admin, regular user, whatever).
Any thoughts about this...
Any links or comments on what good user table design would satisfy...
Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

management between multiple tables could become very painful, especially if the number of levels is allowed to change. Storing the data in a central location is good, you could have a level specific table elsewhere that uses a many-to-many or many-to-one relationship with the user table. You'd then seperate the user level away from the user off into a seperate area altogether.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Keep the data where it belongs from a relational model standpoint. Create multiple users with different permissions on the tables and have your script only connect as the lowest user needed for its tasks.

I tend to have three database users for every application:
application_unuath_user -- non logged in site visitors
application_auth_user -- logged in site visitors
application_admin_user -- logged in site admins

unauth_user typically only has select permissions and whatever permissions are needed to sign up for an account and insert into logging tables.

auth_user has a lot more permissions, but often lacks deletes. In some cases they can mark a record for pending delete, but some places need admin approval, etc.
Post Reply