wissel.net

An interesting discussion happened today around Notes performance. In a rather large database (> 500k records) all documents are protected with Author and Reader fields. The access is rather narrow, so any user might see just about 1000 of the 500000 documents. Opening a view in the Notes client is rather slow and the old "rah. rah Notes is bad" song is performed. Notes performance is discussed in great length at other places, so this isn't what this post is about. I was wondering how one would implement access control on a record level in a relational database. This would be the specifications:

• Design a view that restricts access to a subset of table data (we simplify here by excluding multi-value data fields)
• A record can have zero or many readers, who are allowed to see the record
• A record can have zero or many authors, who are allowed to see the record and later update them (eventually)
• If a record has no readers any user with access to the database can see the record
• If one or more readers are present only the sum of readers and authors can see the document
• A reader or author can be of type: Person, Group, Role
• A role can be assigned to one or more Persons or Groups
• A group can contain Groups and People (we simplify here and omit the * operator)
• A group can have zero or more roles
• A person can have zero or more roles
• A person can be member in zero or more groups

Graphically it would look somehow like this:

Now how would an SQL statement look like? I'm not an SQL expert, so I might get quite some stuff wrong. But here is my go:

SELECT * FROM maintable
   WHERE maintable.id IN (SELECT readertable.maintableid FROM readertable
   WHERE readertable.entry = @CurrentUser
   OR readertable.entry IN (SELECT roletable.roleid FROM roletable WHERE roletable.entry = @CurrentUser)
   OR readertable.entry IN (SELECT grouptable.groupid FROM grouptable WHERE grouptable.entry = @CurrentUser)
   OR readertable.entry IN (SELECT roletable.roleid FROM roletable WHERE roletable.entry IN
   (SELECT grouptable.groupid FROM  grouptable WHERE grouptable.entry = @CurrentUser)
   )
   OR maintable.id NOT IN (SELECT readertable.maintableid FROM readertable)
   OR maintable.id IN (SELECT authortable.maintableid FROM authortable
   WHERE authortable.entry = @CurrentUser
   OR authortable.entry IN (SELECT roletable.roleid FROM roletable WHERE roletable.entry = @CurrentUser)
   OR authortable.entry IN (SELECT grouptable.groupid FROM grouptable WHERE grouptable.entry = @CurrentUser)
   OR authortable.entry IN (SELECT roletable.roleid FROM roletable WHERE roletable.entry IN
   (SELECT grouptable.groupid FROM  grouptable WHERE grouptable.entry = @CurrentUser)
   )

And that's without taking into account that a group could contain a group. Looks like a performance pig to me. Luckily in Domino we can use categorized views to make access fast. Of course I'm happy to learn that there are smarter SQL queries around.