defining access right for each database entry

I have a table with information about companies and a table
with several users.

Now I need a solution where I can define access rights for each
entry in the company table.

    user1 | user2

I have designed several user management systems (and eventually merged all the features into one flexible, yet easy to use reusable system (got tired of rewriting them). Based on that experience, I have some observations.

First, does company1 and company2 imply the company the user works for? Or are company1 and 2 records just data records, and could just as easily be widet1 and widget2?

What I'm asking is whether you really need each user to have unique permissions per individual record of some data table (that's a rather immense implication).

Let's start with that question first.

No, the users did not work for the company. "company" could be
replaced with anything else.

My suggestions was to define a extra table with somthing like this

company_id 12
user_id 1
rights 4

v = 1
e = 2
d = 3

So the entry above would mean that user 1 can view and delete (v = 1
and d = 3)

Just an observation... you might want to use prime numbers as the
value of v, e, and d then multiply the users permissions to arrive at
their 'rights' value ({'v' => 3, 'e' => 5, 'd' => 7). That way to find
out if a user has permission to some feature you can test if their
rights value can be factored by the prime associated with that


Except that an entry of 3 could mean d only, or v + e -- so a little better number system will be needed for that plan.

Being blind as to the exact data/domain nature, my first instinct is to say that having that high of a resolution where every user needs explicitly unique rights to every company is excessive. You're essentially saying there is absolutely nothing in common among all companies and how they relate to users.

I may be wrong, but I would expect there to be some way to categorize companies from the perspective of the user: companies in a given arbitrary territory or classification -- something that allows user2 to always be able to "edit" a company based on some rule.

I have always ascribed to the notion that if a human can logic out something, so can a program. If you have people deciding based on rules (even if there's a lot of exceptions) and not emotion, they can be codfied.

So, assuming that, I prefer to define two distinct categories of rights. First is privileges--the ability to view, edit, delete. No particular data assigned yet. A person either has the right to edit nothing at all, or they have the right to edit at least something. If so then the person gets an edit privilege.

Second, we determine what is allowed to be edited through what I call filters. If a person is allowed to view, well, _what_ can he view? I codify filters as query fragements with a label, table, field, match operator, and match value. So, a person is allowed to view records where the field "territory" in table "customers" is "equal" to "southwest." That info can be used to modify queries on the fly to present lists of data the user is allowed to view, or in a mixed list where maybe he's allowed to view two territories but edit only one of them, then that same filter spec can be used to conditionally enable Edit buttons/links.

By using certain data structures and UIs for defining users, it becomes very easy to modify the rules on the fly without impacting existing user definitions.

One of the advantages this system has is that it gets away from the classic concept of a group or a role -- both of which I find to be utter failures everywhere I've used them. There's _always_ an exception, and this system allows that. However, I can still embrace the idea of a user type (or role I guess), which allows me to define macros of default settings as a starting point to minimize the admin's task of defining new users.

It's kind of difficult to explain the whole system in a couple of email paragraphs, and I find that the system is unique enough that it takes most people a round or two to understand how it is different and see the advantages, but once they get it, it's an "of course" moment. If it sounds like I'm striking a chord, let me know and I'll delve further.

-- gw