Multiple Groups in User Management

Fooglmog

Hey Everyone,
I've been trying to figure out in my head what the best way of having users in multiple groups is... but I'm not sure, so I'm looking for some advice.

I have a user management system. My DB has 2 tables, one for users and one for groups. The Users table has a lot of information in it, but the important pieces are USER_ID, USER_NAME and GROUP_ID. The group table only has GROUP_ID and GROUP_NAME.

On the website, specific pages are only accessible to logged in users who are members of specific groups. I'm basically using a hard-coded if statement to either allow or deny access to specific pages.

What I'm wanting to do, though, is to be able to put users in multiple groups. So, if I have 1 page which is hard-coded to allow Group 1 access, and another for Group 2... but I want some people to be able to access both -- I don't have to create Group 3 and add them to the hard-coded if statement. I can just put specific users in both Group 1 and 2.

I'm not sure how to best store the information, though. I was thinking of using serialize/unserialize and storing it that way in the "GROUP_ID" field of the user table... but that doesn't seem like a very elegant solution, and I was wondering if someone here might have thoughts as to a better way of doing this.

Fooglmog

The other possibility I was considering is to get rid of GROUP_ID from the users table, and create a third table with just a column for USER_ID and GROUP_ID... and have a different row in that for every group each user is a member of...

But that seems like it will add up to a lot of rows awfully quickly.

Weedpacket

Have you considered using the access control mechanisms built into the DBMS (if you're using one)?

Database access is usually controlled by granting permission to do things to those uses who should have permission to do them - typically assisted by constructing a set of views in the database for each type of user; a Group 1 user (though a more descriptive title would be easier to work with) would have permission to view/work with those parts of the database for which permission has been granted to Group 1.

(Incidentally, it is for this reason that some DBMSs prefer to use the term "role" instead of "user". In a forum application, an anonymous visitor might be logged in to the database as a "visitor" and have permission to view forum posts, but won't have permission to add or delete posts; someone logging in as "moderator" on the other hand would have permission to do all three things.)

If you use this method I guess two connections to the database would happen: first, a (very brief) connection as "login" which is used at log in to determine what - if any - role the person currently trying to log in has; the "login" role doesn't need access to much - only barely enough to make that determination. Then, reconnect to the database using that role.