[Rule Request] Check if any RLS Filter Expression is blank for tables that start with "fact_"

[ViktoriaVrg]

Dear all,

for our security concept, we aim at creating an additional rule, which checks all model roles if the RowLevelSecurity FilterExpression is blank. If it is blank, we aim at fixing it by inserting the FilterExpression FALSE().

Could you provide me with some guidance on how to write the code? I would've thought that something like this would work as a first step, however I'm getting the error: no property field 'role' exists in the txpe 'ModelRole'.

Model.Roles.Any(role => role.TablePermissions != null && role.TablePermissions.Any(tablePermission => tablePermission.TableName.StartsWith('fact_') && string.IsNullOrWhiteSpace(tablePermission.FilterExpression)))

Any hints/suggestions/doucmentation are very much appreciated :)!

Thanks!

[otykier]

Some clarification:

The RowLevelSecurity property of a role is actually an indexed property, meaning it has a (string) entry for each table in the model. Tabular Editor will automatically create or delete the TablePermission object when an RLS expression is added to or removed from an entry in the RowLevelSecurity property.

In your code example, however, you ignore role/table combinations where the TablePermission object is null. With the above in mind, this is essentially ignoring all those TablePermissions with a blank RLS filter expression (unless some TablePermissions also have OLS enabled).

So the question is whether you want to check if any table across all roles, have a blank RLS filter expression? Otherwise, I'm not sure why you're explicitly only checking those tables for which a TablePermission exists.

In case you did indeed want to check all tables, you can create the BPA rule in the following way:

• Rule Scope: Model Roles
• Rule Expression: RowLevelSecurity.Any(string.IsNullOrEmpty(it))

If, however, you only want to check existing TablePermission objects for blank filter expressions, you can use the following BPA rule expression instead (same scope as above):

Model.Tables.Any(
    current.TablePermissions.FindByName(it.Name) <> null and
    string.IsNullOrEmpty(current.TablePermissions[it].FilterExpression)
)