Hitachi Vantara Pentaho Community Wiki
Child pages
  • 06. Adding Row Level Security to a Pentaho Metadata Model

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

From the Model Properties dialog, select the General -> Data Constraints Property:
 
 By default, Row Level Security is not enabled.  There are two forms of Row Level Security in Pentaho Metadata, Global Constraint and Role Based Constraints.

Global Constraint

If using the Global Constraint, a single MQL Formula is used to define security for all users.  In addition to the standard MQL Functions available, there are also two additional functions available.

...

Code Block
IN("Admin"; ROLES())

Role Based Constraints

If using Role Based Constraints, the Metadata engine determines which MQL constraints apply to the current user and apply them to the current query.  Constraints may be added for each Role and User within a system.  If zero constraints match a user and their roles, no data is returned by the MQL query.  If more than one constraint applies to a user, then the constraints are OR'ed together to determine row visibility.

Example of Role Based Constraints

This example defines an MQL Formula for three different roles.  The Admin Role has full row visibility, the Sales and Engineering Roles may only see data that joins to rows with their particular department.

Role

Constraint

Admin

TRUE()

<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="a50c0cd1-9065-4c86-9c30-22c5ee19c7f6"><ac:plain-text-body><![CDATA[

Sales

[BC_DEPARTMENT]="Sales"

]]></ac:plain-text-body></ac:structured-macro>

<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="5d50331e-b140-415f-8ee4-b579d8a818bd"><ac:plain-text-body><![CDATA[

Engineering

[BC_DEPARTMENT]="Engineering"

]]></ac:plain-text-body></ac:structured-macro>

Important Note

Row Level Security Constraints are applied at the MQL Layer.  The Business Columns referenced in the MQL Security Constraints will be resolved down to SQL Table Columns.  The Tables which contain column references included in security constraints will be joined to your query, based on the relationships defined in the Business Model.  It is recommended that you do not use outer joined business columns for the purposes of security constraints.