Column Privileges¶

When you grant the EXECUTE privilege to a user/role over a view, this user or the users with this role can:

• Execute a SELECT statement to query this view

• If the user/role also has the INSERT/UPDATE/DELETE privileges over a view, they can execute INSERT/UPDATE/DELETE statements over this view.

In some scenarios, you may want to limit the access to certain columns of a view to users. The columns of a view that cannot be queried or modified through INSERT/UPDATE/DELETE statements are called protected columns.

For example, let us say you have a view “employee”. You may want to forbid users with the role “developer” to see the column “salary”. That is, you want “salary” to be a protected column for the users of the role “developer”. To do this, follow these steps:

1. Click the menu Administration > Role Management. In the tables with roles, select the role “developer”.

2. Click Assign privileges and on the row of the database of the view “employee”, click edit.

3. Select, at least, the privilege “Execute” over this view.

4. Select the row of the view “employee” and click Assign column privileges.

5. In this dialog, clear the check box next to the field “salary”.

With this change, the user will be able to run this query:

SELECT ename
FROM employee

However, this one will fail:

SELECT ename, salary
FROM employee

If instead of making the query fail, you want to mask the values of a column, you can specify a row restriction with column masking (see section Row Restrictions below).

When assigning column privileges, consider this:

• If you assign column privileges to a user over a view, the statement executed by this user will fail if the query references a protected column of this view in any of the clauses of the query (SELECT, WHERE, GROUP BY, etc.). This is to ensure that users cannot guess the value of protected columns by for example, using them in the WHERE clause. Find an example of this in the next section.

• Column privileges do not affect global administrators or administrators of the database to which the view belongs. They can reference protected columns in their statements.

• The Execution Engine takes into account the column privileges, row restrictions and custom policies granted over a view to a user/role, regardless of if the view with the column privilege is directly referenced in the query or not.

The following table lists the behavior of column privileges for each DML statement:

Row Restrictions¶

When a user/role has the EXECUTE/UPDATE/DELETE privilege over a view, you can define a restriction over that view to restrict the rows returned to that user/role or affected by the UPDATE/DELETE statements. That way, when that user executes a DML operation over the view, they will only obtain/update/delete the rows that match a certain condition and/or some of the fields of some rows will be masked.

For example, let us say you want to restrict the access to the view “employee” to users with role “developer”. To assign a row restriction to a user/role: edit that user/role, select the view you want to restrict (“employee” in our example), click Assign restrictions and then click New restriction.

In this dialog, you first need to specify a condition. The user will be able to see the rows that do meet the condition you enter (e.g. position <> 'manager'). For the rows that do not meet the condition, you have to select one of the following actions:

1. Reject row. In our example, the user will only see/update/delete the employees who are not managers.

2. Reject row if any or all sensitive fields are used (you can select which ones). For example, you could allow the user to see all the employees’ data if the statement does not reference use the column “salary”. If the select/update/delete operation references the column “salary” then the statement will only affect the employees that are not managers.

3. Mask sensitive fields if any or all of them are used (you can select which ones). For example, you could allow the user to see all the employees’ data, but for the ones who are managers, the values of the field salary will be replaced by a masking expression.

The following table shows the masking expressions to replace sensitive values.

The following table shows the REDACT default expressions per type.

You can develop what is called a custom policy that takes into account any information you want to decide if the result of the query is filtered or not. The section Custom Policies of the Developer Guide explains what they are and how to develop them.

When assigning row restrictions, consider this:

• The row restrictions do not affect global administrators or administrators of the database to which the view belongs. I.e. no row will be rejected or masked regardless for them.

• The Execution Engine takes into account the column privileges, row restrictions and custom policies granted over a view to a user/role, regardless of if the view with the column privilege is directly referenced in the query or not.

• The option Reject row if any/all sensitive fields are used and the masking are applied when the query references a sensitive field of this view from any of the clauses of the query (SELECT, WHERE, GROUP BY, etc.). This is to avoid users guessing the value of a sensitive field by using it from the WHERE clause. For example, if the sensitive field salary was only masked on the SELECT clause, the user could try to guess the values using queries like:

SELECT ename FROM employee WHERE salary > 50000 and salary < 100000;

or

UPDATE employee SET ename = ename || '_100000' WHERE salary > 100000

The following table shows the behavior of “Reject Row” restrictions, for each type of DML statement:

For example, let us say you assign a row restriction over the view “employee” to the role “sales_manager”. This row restriction rejects rows that do not verify the condition department = 'sales'.

If a user with role “sales_manager” executes:

SELECT * FROM employee

SELECT * FROM employee WHERE department = 'sales'

If the user has the UPDATE privilege over employee and executes:

UPDATE employee SET manager_id = 1 WHERE manager_id = 2

UPDATE employee SET manager_id = 1 WHERE manager_id = 2 AND department = 'sales'

The following table shows the behavior of restrictions “Reject Row if sensitive fields are used”, for each type of DML statement:

For example, let us say you assign a row restriction over the view “employee” to the role “developer”. This row restriction rejects rows that do not verify the condition position <> 'manager', but only if the statement uses the sensitive column salary.

If a user with role “developer” executes:

SELECT ename FROM employee

SELECT ename FROM employee WHERE salary > 50000

SELECT ename FROM employee WHERE salary > 50000 and position <> 'manager'

The same way, if the same user executes:

CREATE MATERIALIZED TABLE employee_salary AS SELECT ename, salary FROM employee

SELECT ename, salary FROM employee WHERE position <> 'manager'

The following table show the behavior of restrictions “Mask sensitive fields if ANY/ALL of them are used”, for each type of statement:

Using the previous example, if a user with role “developer” executes:

SELECT ename, salary FROM employee

It will return all rows, but for those rows corresponding to managers, the values of salary will be masked.

The same way, if the same user executes the query:

SELECT ename FROM employee WHERE salary > 50000

The user will see the result of the following query:

SELECT ename FROM employee WHERE CASE WHEN (position <> 'manager') THEN salary ELSE null END > 50000

This is, the names for the employees whose position is not manager.

If the same user has the privilege DELETE over employee and executes:

DELETE FROM employee

It will delete all employees. However, if the user executes:

DELETE FROM employee WHERE salary > 50000

It will only delete the rows WHERE salary > 50000 and position is not ‘manager’

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

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.

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.

Limit Indirect Access to Views Shared with Other Development Teams¶

In multi-layered virtual models with several development teams, defining restrictions at intermediate layers may be unavoidable: different layers may be managed by different teams with different security concerns and the owners of the views in a given layer may need to ensure that the data of their views is not inadequately exposed by views in the higher layers.

In this scenario, it is easy to lose control of which data is accessible from the final users. So you could be interested in changing the behavior of the security model to avoid that giving access on a view is also transferring the ability to grant privileges to other users over that data. There are two alternative ways to do this:

1. Using Global Security Policies.

2. Or using the Indirect Access privilege (see the section below).

Limit Indirect Access to Shared Views Using the Indirect Access Privilege¶

Starting with Denodo 8.0u20230914, Denodo includes an alternative way to limit this access that does not require using Global Security Policies. It includes a new INDIRECT_ACCESS privilege that can be granted to roles on specific views.

Let’s illustrate this feature using the example from the first section.

You could grant INDIRECT_ACCESS privilege on EMPLOYEE view to HR role, so:

• Users from HR could execute queries using the EMPLOYEE view.

• Users from HR could create new views using the employee information.

• Users from HT could grant EXECUTE privilege on a derived view(which uses the employee information) to the SALES department.

• Users from SALES could execute the derived view but all data from EMPLOYEE view will be restricted to them until INDIRECT_ACCESS is granted to SALES.

Note

Keep the following considerations in mind when implementing this security model:

• This feature is disabled by default at server level (previous behavior is the default one).

• Once the feature is enabled at server level, it must be enabled for a specific view.

• Once the feature is enabled for a specific view, the INDIRECT_ACCESS privilege can be granted to roles in order to define which users will retrieve the data of the specific view executing other views which query data from this view somehow.

Let’s see how to implement this step by step:

1. First, this feature must be enabled at server level in Administration > Server Configuration > Privileges and check the option Allow Indirect access by default.

2. Roles that include these privileges could be configured for a specific view or procedure clicking the tab privileges in the detail pane of view/procedure and check the option Restrict indirect access. You could select the roles that will have this privilege.

Another option to configure the privilege is going to the Administration > Role Management option, select a role and click on the button Assign Privileges. Click on the edit button of advanced privileges column for the database and then click on the button of the column indirect access for the element that is going to be changed. This column show a check for the elements that have configured the grant indirect_access.