Article
· Jan 9, 2017 3m read

How to determine row level security at runtime

In addition to its general security, Caché offers SQL security with a granularity of a single row. This is called row-level security. With row-level security, each row holds a list of authorized viewers, which can be either users or roles. By default access is determined at object modification Some time ago I became interested in determining row-level security at runtime. Here's how to implement it.

Some notes on RLS:

  • Row-level security is only available for persistent classes.
  • Row-level security is only available for tables instantiated on the Caché server. It is not available for link tables (that is, those that are instantiated on foreign servers).
  • Row-level security is only enforced when accessing rows from SQL. It is not enforced when directly accessing globals or when accessing globals via the object interface (define %OnOpen callback  to add rls for objects).

Here's the simple example of rls enabled class:

Class Utils.RLS Extends %Persistent
{
Parameter ROWLEVELSECURITY = 1;

Property %READERLIST As %String;
}

Value of %READERLIST property is a comma-delimited string listing users or roles that may view the row (empty string for all users).

So, for example if  %READERLIST for one row is "_SYSTEM,%Development" then _SYSTEM user can access the row and all users who hold %Development role.

%READERLIST value is used in %RLS index which in turn is used by SQL to determine access.

Dynamic row-level security

By default %READERLIST is provided by user, stored, indexed and used to determine access. But I wanted dynamic row-level security, for example during each access attempt call a method which determines - does the user has access or not. To achieve that result two things are required:

  • Disable %RLI index (or use %IGNOREINDEX in every query)
  • Make %READERLIST property always calculated

Thankfully both of these things are easily achievable, to disable %RLI index for selected class execute once:

do $System.SQL.SetMapSelectability(class, "%RLI", $$$NO)

You may also need to purge old queries which could use the index:

do $System.SQL.PurgeForTable(class)

That done, lets make %READERLIST property always calculated:

Property %READERLIST As %String [ Calculated, Private, SqlComputeCode = {s {*} = ##class(Utils.RLS).GetAccess({ID})}, SqlComputed ];

ClassMethod GetAccess(Id) As %String
{
    return:Id>3 "_SYSTEM"
    return "%All"
}

Here only _SYSTEM user can access all records with Id>3 and for Id 1 and 2 only users with %All can have access.

And here's the complete example:

Class Utils.RLS Extends %Persistent
{

Parameter ROWLEVELSECURITY = 1;

Property %READERLIST As %String [ Calculated, Private, SqlComputeCode = {s {*} = ##class(Utils.RLS).GetAccess({ID})}, SqlComputed ];

Property data As %String;

ClassMethod GetAccess(Id) As %String
{
    return:Id>3 "_SYSTEM"
    return "%All"
}

/// do ##class(Utils.RLS).Fill()
ClassMethod Fill(N = 5)
{
    do ..%KillExtent()
    for i=1:1:N {
        &sql(insert into Utils.RLS(data) values(:i))
    }
    do $SYSTEM.SQL.SetMapSelectability($classname(),"%RLI",$$$NO)
    do $system.SQL.PurgeForTable($classname())

    do ##class(%SQL.Statement).%ExecDirect(,"select * from "_$classname()).%Display()
}
}

If I execute:

do ##class(Utils.RLS).Fill()

As a user with %All I receive the following output:

ID      data
1       1
2       2
3       3
 
3 Rows(s) Affected

And as _SYSTEM I receive all 5 rows:

ID      data
1       1
2       2
3       3
4       4
5       5
 
5 Rows(s) Affected

Advantages of dynamic row-level security

  • You can determine access based on external state: global value, day of week, holidays, %request state, etc.
  • You don't need to modify %READERLIST to change access permission, only modification of GetAccess method is required

Disadvantages of dynamic row-level security

  • Slow
  • Uncompilation enables %RLI index. UnCompilation hook is required to disable index again 

Links

 

Author would like to thank an engineer who provided main implementation ideas for this article at Russian Caché forum.

Discussion (9)1
Log in or sign up to continue

You could do the same thing by creating a View with a function in the WHERE clause that called the method to figure out if you should see the row or not.

  1. responsibility lies on developer don't to forget add additional conditions: WHERE, %IGNOREINDEX, etc.
  2. the query becomes more complicated

I think this is a cleaner solution as you do not need to worry about the %RLI becoming available again. 

What prevents force disable %RLI in ^%ZSTART or $SYSTEM.SQL.SetServerInitCode() ?

Huh.  I have been unable to have my class update %READERLIST and the %RLI index when I update a row.  It's set when I create/save an object, but it's not updated when I update the object.  Can I show you some simple test classes?

My %READERLIST is based on a role name.

 

From OVERRIDEing the Parameter ROWLEVELSECURITY:

"

/// ROWLEVELSECURITY = 1 | <property> means that row level security is active and the list
/// of users/roles for a given instance/row is contained in a generated property. If
/// the value of this parameter is a valid property name then that property will be
/// used as the reader list and only generated if not already defined.

ROWLEVELSECURITY = 1;

"

?

Using SQLComputeCode and SQLComputeOnChange %READERLIST property is stored but gets recalculated on every SQL and Object INSERT/UPDATE/%Save:

Class Utils.RLS Extends %Persistent
{

Parameter ROWLEVELSECURITY = 1;

Property %READERLIST As %String [ SqlComputeCode = {set {*} = ##class(Utils.RLS).GetAccess({%%ID})}, SqlComputed, SqlComputeOnChange = (%%INSERT, %%UPDATE) ];

Property data As %String;

ClassMethod GetAccess(Id) As %String
{
    return:Id>3 "_SYSTEM"
    return "%All"
}

/// do ##class(Utils.RLS).Fill()
ClassMethod Fill(N = 5)
{
    do ..%KillExtent()
    for i=1:1:N {
        &sql(insert into Utils.RLS(data) values(:i))
    }

    zw ^demo.testD,^demo.testI

    do ##class(%SQL.Statement).%ExecDirect(,"select * from "_$classname()).%Display()
}

Yes, I needed the SqlComputeOnChange = property;  now it's getting updated for every insert/update when the {property} changes, which is great.  The only problem is, my object is not limited to one row.  It's an object with dynamic properties, where one object is several rows, and one row is the Category and Value of one property.

 

This is fine when I insert or update ALL the rows for one object, but not when I update just one row.  I can't think of a good way to force update on %READERLIST  for other rows, based on a WHERE clause, maybe.

ROWLEVELSECURITY = 1;

///Updated for each row when the property 'Value' changes

Property %READERLIST As %String [ SqlComputeCode = {set {*} = ##class(Users).%SecurityPolicy()}, SqlComputed, SqlComputeOnChange = Value ];

Here's an example of my data:

ID     %READERLIST     Category     Value     Username

1      DEV              Color       Blue      Laura

2      DEV              Number      555       Laura

3      QA               Color       Pink      Jane

4      QA               Number      87        Jane

 

I'll be mulling this over for a while, but would love any other ideas.

Thanks,

Laura

Is it possible to change underlying data model so that 1 row = 1 main object?

Class UserPrefs {

Property Username;

///  Color/Number are keys, and this can be indexed
Property Preferences As Array Of %String(SQLPROJECTION = "table/column");

Parameter ROWLEVELSECURITY = 1;

///Updated for each row when the property 'Preferences' changes
Property %READERLIST As %String [ SqlComputeCode = {set {*} = ##class(Users).%SecurityPolicy()}, SqlComputed, SqlComputeOnChange = Preferences ];

}

You can use SQL to insert into child table. Not sure if it would actually trigger a %READERLIST recalculation (maybe try %%INSERT) but worth a try.