Add role model-level security constraints

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. This is only valid at the business model-level.

In the Pentaho Metadata Editor, select the model to which you want to add row-level security, right-click the model, and select Edit.

Any data constraints defined below the model level, such as in a business table or business column, is ignored and not used. In the Business Model Properties dialog box, select the General > Data 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 use the OR function 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.

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.