User permissions implemented in a database

[agnitrate]

I'm trying to plan a user privileges system for my site and I'm having a hard time trying to figure out how to store the privileges in a database. Ideally, I would like to have permissions for posting that include new, edit, and delete, permissions for pictures that would include the same, and permissions for user registration that would also include the above three.

The question is how to store it in a database that would allow for ease of use, efficient space usage, and scalability should I wish to expand the permissions at a later time. I've googled to find how it is implemented in large systems such as a BBS or login system, but I've not been able to find any implementation notes.

My idea is to potentially use bits to represent permissions such as the unix permission scheme (i.e. new/edit/delete -> XXX, 100 = new only, 110 = new,edit, etc, etc) and store them as bits in the database. I could then find user permissions by bit-shifting operators and such. This seems to be the most efficient way to do it, but since I'm new to both SQL and relational database design I'm not very confident in my abilities to determine the 'right' way yet or not.

Does anybody have experience in storing user permissions in a database and how you implemented it? I would be very interested in a discussion of the best way to go about using such a scheme.

 

[EagleKeeper]

I am working on a project with similar conditions.

The major user access areas have a corresponding bit like you indicate.

I then encrypt the bit pattern, convert it into an ascii hex string and store the encrypted hex pattern in the database with the user record.

The program then reverses the process to determine the bit pattern access codes for the user and checks if the proper bit is set to allow a command to become enabled for selection.

 

[Armitage]

I've done something similar - but maybe not to the level of detail that you're working on.
I just use something like a unix group scheme.

I have a table with various group definitions - just two columns: group ID (gid), and description.
Then I have a group membership table with two columns: group ID (gid) and user ID (uid).

I like being able to directly access the permissions without mucking around with bit operations or otherwise interpreting some field in the user table.

SELECT username, description FROM users, groups, group_def WHERE users.uid=groups.uid AND groups.gid=group_def.gid ORDER BY username;

The number of groups is unlimited, and it's trivial to add a new group. I can also lock down the groups & group_def tables tighter then I can lock down the user table, which has to give insert & update permissions to the web server.