Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We are looking at implementing an ACL that fulfills the following requirements:

  1. Most users have access to an item (access to the item by default), but a few is denied access.
  2. Most users do not have access to an item (no access to the item by default), but a few is granted access.
  3. A user only has access to a few items.
  4. Default is that all users have access to an items.

We are talking about hundreds of thousands of items and tens of thousands of users. They are all stored in a relational DB. We think that having a many-to-many relation between items and users and defining the access in this relation would be ineffective, but are open for comments about this. We also think that access groups are irrelevant here, but we might be wrong.

What are your thoughts on how to do this?

share|improve this question

migrated from Jan 25 '12 at 14:35

This question came from our site for professional and enthusiast programmers.

items 1 and 4 contradict items 2 and 3... – gbn Jan 25 '12 at 14:27
And therein lies the problem, or challenge ;) – chriscena Jan 26 '12 at 10:11

Your many-many table is a good idea. In this table, the existence of a row can mean "no access" or "has access" depending on your need.

I'd aim to store least data and make "no row" mean the most common state by implication.

Looking at your requirement 2, I'd have it so that "row exists = has access". However, the opposite can be be read from item 1. Ditto for 3 and 4. So you need to explain this more

I'd also look at grouping users. Do you really permission users individually? Ditto for items: can thes be grouped somehow?

share|improve this answer
I am reconsidering the grouping solution, having both many items and users as members of a group with specification about access on the group. A group may be allowed or denied access to the related item(s) and non-members will have the opposite access rights, following the least-data-strategy. But I'm concerned about the performance implications this solution might have. – chriscena Jan 26 '12 at 10:25

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.