wissel.net

Usability - Productivity - Business - The web - Singapore & Twins

Access protected Notes documents - RDBMS style


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:
RelationalAccessControl400.png
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.

Posted by on 15 October 2008 | Comments (3) | categories: Show-N-Tell Thursday

Comments

  1. posted by Marius Neumann on Thursday 16 October 2008 AD:
    Thanks for this great comparison! I am sure I can use it in one of my next performance discussions Emoticon wink.gif
  2. posted by Patrick Kwinten on Tuesday 04 November 2008 AD:
    I am still curious which steps you would take to make the performance in the Notes situation as performance effective as possible. We have similar databases and just wondering if I have taken all the steps that would improve performance.

    Thank you in advance,

    Patrick
  3. posted by Stephan H. Wissel on Tuesday 11 November 2008 AD:
    @Patric: Use single category views with the Readers Field(s) as category. Then Notes only checks document the current user can actually see.