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
{
</FONT><FONT COLOR="#000080">Parameter </FONT><FONT COLOR="#000000">ROWLEVELSECURITY = </FONT><FONT COLOR="#000080">1</FONT><FONT COLOR="#000000">;
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">%READERLIST </FONT><FONT COLOR="#000080">As %String </FONT><FONT COLOR="#000000">[ </FONT><FONT COLOR="#000080">Calculated</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">Private</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">SqlComputeCode </FONT><FONT COLOR="#000000">= {</FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800080">{*} </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#0000ff">$s</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">%oper</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">"%All"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#0000ff">$$$YES</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#0000ff">$$$SQLEmptyString</FONT><FONT COLOR="#000000">)}, </FONT><FONT COLOR="#000080">SqlComputed </FONT><FONT COLOR="#000000">];
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">f1 </FONT><FONT COLOR="#000080">As %String</FONT><FONT COLOR="#000000">;
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">f2 </FONT><FONT COLOR="#000080">As %Integer</FONT><FONT COLOR="#000000">;
</FONT><FONT COLOR="#000080">/// d ##class(demo.test).Fill()
ClassMethod </FONT><FONT COLOR="#000000">Fill(</FONT><FONT COLOR="#ff00ff">N </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#000080">3</FONT><FONT COLOR="#000000">)
{
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">%KillExtent</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#0000ff">f </FONT><FONT COLOR="#800000">i</FONT><FONT COLOR="#000000">=1:1:</FONT><FONT COLOR="#800000">N </FONT><FONT COLOR="#800080">&sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">demo</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">f1</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">f2</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">values</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'f'</FONT><FONT COLOR="#000000">||</FONT><FONT COLOR="#800000">:i</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">:i</FONT><FONT COLOR="#000000">)</FONT><FONT COLOR="#800080">)
  
  </FONT><FONT COLOR="#0000ff">zw </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000000">!
  
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">Test</FONT><FONT COLOR="#000000">()
}
</FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">Test()
{
  </FONT><FONT COLOR="#0000ff">f </FONT><FONT COLOR="#800000">i</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#0000ff">$$$YES</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#0000ff">$$$NO </FONT><FONT COLOR="#800080">{
    </FONT><FONT COLOR="#0000ff">d $SYSTEM</FONT><FONT COLOR="#008080">.SQL</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetMapSelectability</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$classname</FONT><FONT COLOR="#000000">(),</FONT><FONT COLOR="#008000">"%RLI"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">i</FONT><FONT COLOR="#000000">), </FONT><FONT COLOR="#0000ff">$system</FONT><FONT COLOR="#008080">.SQL</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">PurgeForTable</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$classname</FONT><FONT COLOR="#000000">())
    
    </FONT><FONT COLOR="#0000ff">w $s</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">i</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#0000ff">$$$YES</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">"yes"</FONT><FONT COLOR="#000000">,1:</FONT><FONT COLOR="#008000">"no"</FONT><FONT COLOR="#000000">)</FONT><FONT COLOR="#008000">" 1) -----"</FONT><FONT COLOR="#000000">,!!
    </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%ExecDirect</FONT><FONT COLOR="#000000">(,</FONT><FONT COLOR="#008000">"select * from %IGNOREINDEX %RLI "</FONT><FONT COLOR="#000000"></FONT><FONT COLOR="#0000ff">$classname</FONT><FONT COLOR="#000000">()).</FONT><FONT COLOR="#0000ff">%Display</FONT><FONT COLOR="#000000">() </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000000">!!
    </FONT><FONT COLOR="#0000ff">w $s</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">i</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#0000ff">$$$YES</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">"yes"</FONT><FONT COLOR="#000000">,1:</FONT><FONT COLOR="#008000">"no"</FONT><FONT COLOR="#000000">)</FONT><FONT COLOR="#008000">" 2) -----"</FONT><FONT COLOR="#000000">,!!
    </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%ExecDirect</FONT><FONT COLOR="#000000">(,</FONT><FONT COLOR="#008000">"select * from "</FONT><FONT COLOR="#000000"></FONT><FONT COLOR="#0000ff">$classname</FONT><FONT COLOR="#000000">()).</FONT><FONT COLOR="#0000ff">%Display</FONT><FONT COLOR="#000000">() </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000000">!!
  </FONT><FONT COLOR="#800080">}
</FONT><FONT COLOR="#000000">}
}</FONT>

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

USER><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">demo.test</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Fill</FONT><FONT COLOR="#000000">()</FONT>
^demo.testD=3
^demo.testD(1)=<FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"f1"</FONT><FONT COLOR="#000000">,1)</FONT>
^demo.testD(2)=<FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"f2"</FONT><FONT COLOR="#000000">,2)</FONT>
^demo.testD(3)=<FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"f3"</FONT><FONT COLOR="#000000">,3)</FONT>
^demo.testI(<FONT COLOR="#008000">"$test"</FONT><FONT COLOR="#000000">,1)=</FONT><FONT COLOR="#0000ff">$zwc</FONT><FONT COLOR="#000000">(412,1,0)</FONT><FONT COLOR="#008000">/$bit(2..4)/</FONT>
^demo.testI(<FONT COLOR="#008000">"%RLI"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" "</FONT><FONT COLOR="#000000">_</FONT><FONT COLOR="#0000ff">$c</FONT><FONT COLOR="#000000">(0),1)=</FONT><FONT COLOR="#0000ff">$zwc</FONT><FONT COLOR="#000000">(412,1,0)</FONT><FONT COLOR="#008000">/$bit(2..4)/</FONT>
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.