Fine-Grained Privileges at View Level

Fine-grained restrictions in Denodo are a powerful security mechanism that allows a great level of flexibility and control over the data you want to expose to other consumers. Denodo allows different kind of fine-grained access control over a view:

  • Column privileges: Limit the columns a user/role can use in a query.

  • Row privileges: Allow different actions over subsets of rows:

    • Reject rows

    • Reject rows if some sensitive fields are used

    • Mask sensitive fields

In case you scenario has specific requirements that require an Ad hoc implementation, Denodo also provides an API to create your own custom policies.


In order to understand the use of fine-grained privileges it is important to bear in mind that giving access to a role over a specific view is not only giving access to execute that view. If that role has enough privileges to create views, it is also allowing that role to create new views on top of it and decide who can have access to those new views. This means that giving access to a user to execute a view is also transferring the ability to grant privileges to other users over that data.

For example, let’s say you want to allow developers from the HR department to access a canonical view EMPLOYEE containing data regarding employees. At the same time, you do not want other departments like sales to have access to that data as it contains some personal information about the employees. As those developers from the HR department have access to EMPLOYEE, they can not only execute queries using that view and create new views using the employee information. For example, they could decide to allow the sales department access the new views they have created on top of EMPLOYEE and therefore indirectly accessing data from the EMPLOYEE view.

This is when fine-grained restrictions at intermediate levels come into play:

If you specify a fine-grained restriction on a view for a specific role, that restriction is always applied even if the user with that role executes a view built on top of it (unless the user has another role with more privileges).

So, following the same example, you could enforce a restriction over EMPLOYEE so only people from the HR department could access the data. That way, the HR department can still grant access to the sales department to execute their views, but they will not be able to see any of the employee information as the restriction will always apply over the EMPLOYEE view.

Note

Giving execute access over a view to a user with CREATE privilege is also indirectly transferring the ability to grant privileges to third users over that data. Fine-grained restrictions allow us to set limits on this privilege, ensuring certain security rules are always applied.

The restriction enforcement is very versatile as different users can see different versions of the same dataset without needing to replicate the same restrictions on every view. This can make someone think it is a good idea to define these restrictions on intermediate views with the purpose of reusing the same restriction on different views. However, this is not a good practice in general as it can make their management harder. It is a better approach to always define the restrictions on those views acting as facades on the security borders. Additionally, you can make use of tag-based security policies in order to reuse many of the restrictions’ definitions.

Note

As a general rule, a data owner that wants to expose a view to other users should define the fine-grained restrictions on that view that is visible to those users and not on the views under it.

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:

Behavior of column privileges for each type of statement

Statement

Behavior

SELECT

Does not reference protected columns

Query runs

References protected columns

Query fails

CREATE MATERIALIZED TABLE <materialized table> AS SELECT … FROM <view with restricted columns>

Does not reference protected columns

Query runs

References protected columns

Query fails

INSERT

Statement always runs

INSERT INTO <materialized view> SELECT … FROM <view with restricted columns>

Does not reference protected columns

Statement runs

References protected columns

Statement fails

UPDATE

Does not reference protected columns

Statement runs

References protected columns

Statement fails

DELETE

Does not reference protected columns

Statement runs

References protected columns

Statement fails

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.

Masking expressions for sensitive fields

Expression

Description

Hide

A NULL value is returned.

Show first 4 characters

Only first four characters are returned with a fixed number of asterisks.

Show latest 4 characters

Only latest four characters are returned with a fixed number of asterisks.

Only year

Only returns the real year. Other values are reset to default.

Redact

Returns a predefined masking value depending on the field type.

Redact with asterisk

Returns a fixed number of asterisks.

Remove time

Reset the time values to default.

Round

Rounds the number value.

Set to 0

Returns a 0 value.

Set to -1

Returns a -1 value.

Custom

A user defined expression.

Note that the expression must have the same type than the field. A NULL value is returned in other cases.

The following table shows the REDACT default expressions per type.

Redact default expressions.

Type

Expression

Numbers

Returns a 0 value.

Text

Returns a fixed number of asterisks.

Date times

Returns the correct type (localdate, timestamp…) with the values reset to default.

For example, for ‘localdate’ the value of 1970-01-01 and for ‘timestamp’ the value of 1970-01-01 00:00:00

Other types

A NULL value is returned.

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:

Behavior of “Reject Row” restrictions, for each type of statement

Statement

Behavior

SELECT

Virtual DataPort executes the DML command including the condition from the restriction

CREATE MATERIALIZED TABLE <materialized table> AS SELECT … FROM <view with restrictions>

Virtual DataPort executes the DML command including the condition from the restriction

INSERT

No restrictions

INSERT INTO <materialized table> SELECT … FROM <view with restrictions>

Virtual DataPort executes the DML command including the condition from the restriction

UPDATE

Virtual DataPort executes the DML command including the condition from the restriction

DELETE

Virtual DataPort executes the DML command including the condition from the restriction

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
Virtual DataPort will return the results corresponding to the query:
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
Virtual DataPort will perform the update:
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:

Behavior of restrictions “Reject Row if sensitive fields are used”, for each type of statement

Statement

Behavior

SELECT

Not using sensitive fields

No restrictions

Using sensitive fields

Virtual DataPort executes the DML command including the condition from the restriction

CREATE MATERIALIZED TABLE <materialized table> AS SELECT … FROM <restricted view>

Not using sensitive fields

No restrictions

Using sensitive fields in SELECT query

Virtual DataPort executes the DML command including the condition from the restriction

INSERT

No restrictions

INSERT INTO <materialized table> SELECT … FROM <view with restrictions>

Not using sensitive fields

No restrictions

Using sensitive fields in SELECT query

Virtual DataPort executes the DML command including the condition from the restriction

UPDATE

Not using sensitive fields

No restrictions

Using sensitive fields

Virtual DataPort executes the DML command including the condition from the restriction

DELETE

Not using sensitive fields

No restrictions

Using sensitive fields

Virtual DataPort executes the DML command including the condition from the restriction

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
the query will return all rows from employee. However, if the user executes:
SELECT ename FROM employee WHERE salary > 50000
it will return the results corresponding to the query:
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
Virtual DataPort will create a materialized table with the results obtained from the query:
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:

Behavior of restrictions “Mask sensitive fields if ANY/ALL of them are used”, for each type of statement

DML Operation

Behavior

SELECT

Not using sensitive fields

No restrictions

Using sensitive fields

Virtual DataPort masks the value for the sensitive columns

CREATE MATERIALIZED TABLE <materialized table> AS SELECT * FROM <view with restrictions>

Not using sensitive fields

No restrictions

Using sensitive fields in SELECT query

Virtual DataPort masks the value for the sensitive columns

INSERT

No restrictions

INSERT INTO <materialized table> SELECT * FROM <view with restrictions>

Not using sensitive fields

No restrictions

Using sensitive fields in SELECT query

Virtual DataPort masks the value for the sensitive columns

UPDATE

Not using sensitive fields

No restrictions

Using sensitive fields

Virtual DataPort executes the DML command including the condition from the restriction

DELETE

Not using sensitive fields

No restrictions

Using sensitive fields

Virtual DataPort executes the DML command including the condition from the restriction

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.

../../../../_images/fine_grained_privilege_sales_us.png

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:

../../../../_images/fine_grain_restrictions_multiple_roles1.png

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 antipattern. 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 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’:

../../../../_images/fine_grain_restrictions_multiple_roles_update_change.png

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.