Restrict data returned on query based on user’s security level

Row Level Security – Pentaho+ Metadata Model

Business Need
_____________________________________________________________________________________

Consider a situtation where in an organization, workers can access only those data rows that are pertinent to their department, or restricting a customer’s data access to only the data relevant to their company without changing content of business tables, and without affecting application, and regardless of how users access the data.

I use this as an example application : How to can I add security to my existing database that will limit managers to departments they manage and departments that report to those they manage regardless of how users get to the tables and what queries and reports are developed against the database?

Solution
_____________________________________________________________________________________

Pentaho+ Row Level Security allows us to control the results that are returned in a query based on a user’s security level.

We can specify which rows of data each User Role or User ID is allowed to retrieve from the database, based on some column of data, or combination of columns of data.

Steps
_____________________________________________________________________________________

Within the Metadata Editor, select the model to add Row Level Security to, 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 in a Business Table or Business Column, is ignored and not used.

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,

1) Global Constraint
2) 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.

USER() – returns the name of the current user
ROLES() – returns a list of roles the current user has.

Example of Global Constraints

This example defines that if the Role of the logged in User is “Admin” then we can access the Metadata Model, and everyone else no access

IN(“Admin”; ROLES())

Below example defines to show data only for the user that is logged in, which is similar to adding where clause in SQL (where USERNAME = ‘Admin’ )

IN([business_table.business_column]; USER())

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. (The syntax is [business_table.business_column] = value).

Role           | Constraint
——————————————————
Admin        | TRUE()
——————————————————
Sales          | [BT_OFFICE.BC_DEPARTMENT]=”Sales”
——————————————————
Engineering | [BT_OFFICE.BC_DEPARTMENT]=”Engineering”

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 our query, based on the relationships defined in the Business Model. It is recommended that we do not use outer joined business columns for the purposes of security constraints