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" |