Modeling Fine-Grained Role Restrictions on Multiple Roles¶
When a user has multiple roles with different fine-grained privileges for each one there are some extra considerations to take into account. This is because the complexity of these role combinations can lead to undesired behaviors and therefore is important to understand how these restrictions work and the best practices to follow.
There are a few key things to keep in mind when modeling fine-grained role restrictions on multiple roles:
Adding a role expands the user privileges, it does not restrict them
If roles R1 and R2 define fine-grained restrictions P1 and P2 on different views each, the data available to a user with roles R1+R2 might not always be the same as the data available to R1 + the data available to R2
In order to execute a view in Denodo, the privileges that are taken into account are the ones on those roles that have execution privilege on the view.
Privileges are Cumulative, not Exclusionary¶
First thing to understand when managing multiple roles in Denodo, as in many role-based systems, is that adding a role expands the user privileges, not restricts them. One common misconception about how Denodo evaluates restrictions when a user has multiple roles is that if a role R1 has a restriction P1 and another role R2 has the restriction P2, a user with R1 and R2 will have restrictions P1 AND P2 and that is NOT true.
For example, let’s say there is a role ‘customer_service’ that allows a user to see all information about customers; And a role ‘sales’ allows a user to see all information about sales. If the user Eric has both roles ‘sales’ and ‘customer_service’, he will be able to see all information about customers and sales. The same way, let’s imagine we have the role ‘sales_us’ with a row restriction over CUSTOMER so the role can only see the US customers. We have another role ‘sales_de’ with the equivalent restrictions for German customers. If user Mary has both the role ‘sales_us’ and the role ‘sales_de’, Mary will be able to see the sales information for customers from the US and from Germany.
Data Available to a User with Roles R1,R2 is Equals to the Data Available to R1 + the Data Available to R2 in General but NOT Always¶
If a role has privileges over a certain view and another role has privileges over a different view, a user with the two roles assigned will be able to access both views. This was the case of Eric in the previous example. The same happens if a role has a fine-grained restriction over a view and a different role has a different fine-grained restriction over the same view. This was the case of Mary in the previous example. In this case, the restrictions for roles ‘sales_us’ and ‘sales_de’ were both defined over the same view CUSTOMER, so Denodo can compute the union of the privileges as follows:
However, this is not possible when two roles have privilege to execute a view, but: one role defines a fine-grained restriction over one of the subviews in the view hierarchy The other role defined a fine-grained restriction over a different subview of the same hierarchy. This can lead to unexpected results and therefore it is considered an anti-pattern. Instead, roles that can be assigned to the same users must define the fine-grained restrictions on the same views.
Note
Roles that can be assigned to the same user must always define the fine-grained restrictions on the same views. Assigning fine-grained restrictions on different views from the same hierarchy can lead to undesired behaviors and makes the security management more complex.
The execution engine only considers the privileges in roles with execution privilege on the query views¶
In order to execute a view in Denodo, the privileges that are taken into account are the ones on those roles that have execution privilege on the view: Those are the active roles for the query. This includes fine grained privileges like row restrictions and masking; And also indirect access restrictions.
Following the same example from previous section, if only sales_de has execution privilege on view SALES_BY_AGE, a user with roles ‘sales_us’ and ‘sales_de’ will see the results associated to customers from Germany only. If on the other hand both roles have execution privileges on view SALES_BY_AGE, the user will see the results from Germany and also the US.
Restricting the scope of the active roles to the ones with execute privilege on the top view allows to have more control and certainty over the data set each user will see when they execute the view. It also simplifies security management and auditing.
Difference in Behavior since Denodo 8.0u20220126¶
Update 20220126 changes the way Denodo evaluates restrictions on the following scenario:
A user has multiple roles that allow the execution of a view which is composed of other subviews.
For some of those subviews there exists a role R1 that imposes a fine-grained restriction while another role R2 of the same user has execute privilege on the view but it does not specify neither execute nor fine-grained restrictions on the subview.
In this scenario, in order to compute the final privilege of the user on that subview considering all roles:
Until update 20220126, Denodo would apply the restriction defined on R1 over that subview as the absence of explicit privilege or restriction on R2 was considered as no privilege at all. As a consequence, a user with two roles in this situation would see less data than the same user with just one of them. This is considered against the principle that establishes adding a role to a user should give you more privileges, never less.
Since the update 20220126, Denodo will not apply the restriction defined on R1 over that subview as the fact that role R2 has execution privilege on the view on top implies that role can see the data from that subview as long as it is used to compute the final view.
Let’s illustrate this using the example from the previous section:
Role ‘customer_service’ has:
Execute privilege over SALES_BY_AGE
Masking restriction over SALES, so the role cannot see the price information.
Role ‘vip_cust_service’ has:
Execute privilege over SALES_BY_AGE
Row restriction over CUSTOMER_US and CUSTOMER_DE to access only the VIP customer’s data.
User Sunita has both roles ‘customer_service’ and ‘vip_cust_service’:
Denodo evaluates the fine-grained restrictions on the view they were defined, so for each view it needs to consider the privileges granted from each role in order to compute the final combined privileges on that element.
In the example, the role ‘customer_service’ has a masking restriction over SALES and the role ‘vip_cust_service’ has no execution privilege or restrictions defined on that view. The same way, on CUSTOMER_US, as role ‘vip_cust_service’ had a row restriction and ‘customer_service’ did not have execute or a specific restriction.
With 8.0u20210715 or earlier: Denodo applies the masking over SALES and the row restriction. As a consequence, the user with the two roles would see only the VIP customer information and the sales price masked, which means seeing less data than the same user with just one of them.
Starting with update 8.0u20220126: Denodo will not apply neither the masking nor the row restriction. This is because the fact that the role has execute privilege on the view on top means the expected behavior for that role is to see the data coming from that view with no restrictions. As a consequence, the user with the two roles in this situation can see more data than the strict union or privileges, which as we have detailed in the previous section is not always possible.
In certain scenarios when executing views in Denodo, it may be beneficial to consider all roles assigned to a user rather than just those with execute privileges. Starting from release 8.0u20250514, Denodo supports taking into account all active roles for executing queries. This includes indirect access as well as fine-grained privileges such as row restrictions and masking.
For more information about how to configure it, go to section Privileges.