Pentaho row level security example

Using the Metadata Editor, it should be placed at the Business Model layer, above any business tables. Click the data constraints section (or add that section with the olive green plus button in the screenshot below, if necessary), select the group/user to which the rule applies…

…and add the rule with form [BUSINESS_TABLE_NAME.COLUMN_ID]=value

This was very difficult to find, and is not clear in the documentation, so hopefully this helps.

You can then publish the model to the BA server, and it should be picked up by the associated report.

It should be noted that if a user has more than one rule which applies, both will be used. This can create conflicts that are not detectable until noticed. For example, if you have a constraint for a group membership that states [BT.COL_ID]=1 and a constraint for a user in that group that states [BT.COL_ID]<>1, you will see the row.

There doesn’t appear to be any override as it relates to this. In other words, if the user can see it, but the group can’t, you will still see it. If these are reversed, you will still see it.

Lastly, the filter itself appears to be sent to the database, at least in the case of Oracle. We proved this by initiating a SQL trace against the Pentaho database session, and saw the business model filter shown above.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.