Dynamic row-level security

This is a translation of the following article. Thanks [@Evgeny Shvarov] for the help in translation.

Someone posted a question on DC asking whether it was possible to determine access rights for a particular table row always at runtime, and if it was, how could one do that?
Answer: it is possible and it’s not hard at all.

Here’s what you need to do:

• stop hanging out on DC only :)
• disable the %RLI index on the SQL engine level (detailed instructions can be found here) to avoid doing this in every query using %IGNOREINDEX
• make the %READERLIST field always calculated.
  

  Note: If you want to, you can “zero out” access rights during each calculation triggered by an INSERT/DELETE operation, which is defined by the %oper variable. For obvious reasons, it works for SQL only.

Let’s create the following class to demonstrate different approaches:

Class demo.test Extends %Persistent
{

Parameter ROWLEVELSECURITY = 1;

Property %READERLIST As %String [ Calculated, Private, SqlComputeCode = {s {*} = $s(%oper="":"%All",$$$YES:$$$SQLEmptyString)}, SqlComputed ];

Property f1 As %String;

Property f2 As %Integer;

/// d ##class(demo.test).Fill()
ClassMethod Fill(N = 3)
{
  d ..%KillExtent()
  f i=1:1:N &sql(insert into demo.test(f1,f2) values('f'||:i,:i))
  
  zw ^demo.testD,^demo.testI w !
  
  d ..Test()
}

ClassMethod Test()
{
  f i=$$$YES,$$$NO {
    d $SYSTEM.SQL.SetMapSelectability($classname(),"%RLI",i), $system.SQL.PurgeForTable($classname())
    
    w $s(i=$$$YES:"yes",1:"no")_" 1) -----",!!
    d ##class(%SQL.Statement).%ExecDirect(,"select * from %IGNOREINDEX %RLI "_$classname()).%Display() w !!
    w $s(i=$$$YES:"yes",1:"no")_" 2) -----",!!
    d ##class(%SQL.Statement).%ExecDirect(,"select * from "_$classname()).%Display() w !!
  }
}

}

Now, let's use the terminal to run the Fill method as a user with the %All role:

USER>d ##class(demo.test).Fill()
^demo.testD=3
^demo.testD(1)=$lb("","f1",1)
^demo.testD(2)=$lb("","f2",2)
^demo.testD(3)=$lb("","f3",3)
^demo.testI("$test",1)=$zwc(412,1,0)/*$bit(2..4)*/
^demo.testI("%RLI"," "_$c(0),1)=$zwc(412,1,0)/*$bit(2..4)*/
 
yes 1) -----
 
ID      f1      f2
1       f1      1
2       f2      2
3       f3      3
 
3 Rows(s) Affected
 
yes 2) -----
 
ID      f1      f2
 
0 Rows(s) Affected
 
no 1) -----
 
ID      f1      f2
1       f1      1
2       f2      2
3       f3      3
 
3 Rows(s) Affected
 
no 2) -----
 
ID      f1      f2
1       f1      1
2       f2      2
3       f3      3
 
3 Rows(s) Affected

PS: it goes without saying that in a real project, the calculation procedure will be a bit more complex than the banal %All, and may include several roles/logins separated with a comma.