Add Row Level Security to a Pentaho Metadata Model

Row Level Security allows you to control the results that are returned in a query based on a user's security level. You can specify which rows of data each User Role or User ID is allowed to retrieve from the database, based on a column of data or combination of columns of data.

The only location that this will actually produce a result is on the Business Model Level.

In the Pentaho Metadata Editor, select the model to which you want to add Row Level Security, right click on the Model, and select Edit.

Row Level Security is only in effect at the Model level. Any data constraints defined below the Model Level, such as in a Business Table or Business Column, is ignored and not used. In the Model Properties dialog box, select the General -> Data Constraints.

Global Constraint

If you are 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:

USER() - returns the name of the current user

ROLES() - returns a list of roles associated with the current user

The example below defines an MQL formula that allows administrators full access; all other users have no access,

IN("Admin"; ROLES())

Role-Based Constraints

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

This example below defines an MQL Formula for three different roles. The Admin role has full row visibility, the Sales and Engineering roles can access data that joins to rows associated with their specific department only.

Role

Constraint

Admin

TRUE()

Sales

[BC_DEPARTMENT]="Sales"

Engineering

[BC_DEPARTMENT]="Engineering"

Important: 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