How do I: multiple entries in one field

[ExtremeGuy]

I am creating a new MySQL database table for administrators of one of my websites. I would like their "account" with me to be able to specify what they have permission to access and change, ie. user 1 can edit the news and photos section but not any other section, user 2 can edit the news, articles and trails sections, but nothing else. You can see that both users might have an overlapping permission which eliminates my ability to have "levels" of permissions.

Here are my thoughts:
1) Create a seperate field for each "permission", this might be fine for a few permissions, but if I have to add a new "permission" type, I would have to alter my table and add a new field which I would like to avoid. Also, I may have many types of permissions which would make this table large (field-wise) and if I had many users, this may start to bog down the db.

2) Store all of the types of "pernissions" into the same field called "perms", so the data within this field might look something like: "news, articles, trails". If I do this, how can I possibly run a fast/b]query to search this field for something like just "trails"? To me, this just seems like a text search query which wouldn't be too fast if I were making this query a lot.

3) Keep the data stored in a bit-field that can be deciphered using a bit-mask like you do a lot with C/C++ programming. In case, you are going to save a lot of space since you are dealing with only one field AND with a small amount of data in this field since a bitmask can be represented by a number (ie: 01101 = 13)

I would like to know how others deal with this situation.

Thanks!

[Sxooter]

Create a table of users, a table of permissions, and a multi-way join table in between:

create table users (name text, id int primary key);
create table permissions (perm text, id int primary key);
create table user_perms (uid int references users.id, pid int references permissions.id);

[mourisj]

You might also do the bit-field thing in an ASCII-like implementation:

you have to know the sequence of the permissions in the field, e.g. news, articles, trails. You the store the permissions like :
insert into users (user, perms) values ('user1', '110');
insert into users (user, perms) values ('user2', '001');
insert into users (user, perms) values ('user3', '101');

etc.

You then have to read the nth character in the field to see if the user is granted the permissions.

But, I wouldn't probably do it this way!

Regards,

JJ Mouris

[Pig]

Here's a tut on it.
http://www.webhead.cc/main.php?inv=63