Access control lists

Hi all

First question on these forums so if this is not the right place for my question please advise.

I am curious as to how ACLs are usually implemented. Lets say I have a DB user table like

Code:

---

-- Table users
id | name
----------
1  | Alice
2  | Bob

---

and a table of arbitrary objects - say instruments

Code:

---

-- Table instruments
id | name
---------
1  | Drums
2  | Guitar

---

If I were to create an ACL for the instruments I would do something like

Code:

---

-- Table instruments_acl
iid | uid | r | w | d
---------------------
1  | 1  | t | f | f
1  | 2  | t | t | t

---

where

iid - instrument id
uid - user id
r/w/d - read/write/delete

So the above table grants Alice the right to read the Drum object but she can not modify or delete it. Bob may read, write and delete the Drum object

Now say I have 1.000 users and 1.000 objects. That would mean 1.000.000 ACL entries. If the ACL is per column and not per row then the number of ACL entries grows even bigger.

Is this really how it is done? Isn't there a way to cut down on the number of ACL entries?

I figure I can make some sort of ACL heirarchy/inheritance scheme. Like defining an ACL for the table/user relation which will be used if no row/user relation exist.

Any pointers on how to go about this is very much appreciated - especially in regards to performance (I usually use PostGreSQL 8.x if it matters).

Dok