Applies to:
Denodo 8.0
Last modified on: 15 Jul 2022
Tags:
Administration
Column restrictions
Global Security Policies
Row restrictions
Security
The Denodo Platform supports user and role-based authentication and authorization mechanisms with both schema-wide permissions (e.g., to access Denodo databases and views) and data-specific permissions. For the particular case of views, Denodo allows defining privileges at different levels of granularity:
This document is aimed at people that are already familiar with the Denodo security system and the different kinds of fine-grained view privileges described above.
The purpose of this document is to provide best practices on how to model these fine-grained privileges in complex environments where the virtual model in Denodo follows a multi-layer architecture with different development teams and users who can have multiple roles.
This document is structured on the following sections:
Denodo access control using roles is based on the NIST Role Based access control (RBAC) standard. In this regard, there are some considerations to take into account to understand how privileges are managed in Denodo:
As a general rule, fine-grained privileges should be defined on the final views that are exposed to data consumers, to decide what data is exposed to them. Defining fine-grained privileges in intermediate levels of the view hierarchies can lead to management complexities (some of them described later in this document) and, therefore, it is discouraged when not strictly needed. If the final view does not contain all the necessary columns required to evaluate the privilege, choose the highest level view with that information available (or create an intermediate auxiliary view with the required information if necessary).
Nevertheless, 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. The next sections of this document describe this situation in detail and explain the best practices that can be followed to avoid this while minimizing management complexity.
Virtual models in Denodo are usually designed following a layered architecture in order to separate the different responsibilities, increase reusability and help the security management and maintenance. The chosen layers may vary but in order to illustrate the best practices we will use the following layered structure as an example:
Data stewards must decide which views will be available to the other data consumers and what restrictions should apply to each one: different data services, business power users, data scientists, etc.
In general, data consumers can access subsets of the views in the applications and modeling layer (or even in the semantic layer) depending on their privileges.
In big deployments, there may exist several development teams. In this example, we will separate the developers in two groups (there may be more in real cases, but this is enough for our discussion):
The data steward responsible for defining and supervising the security rules for those view assets at the semantic level is commonly a different person from the data steward responsible for the data assets created inside a business unit like marketing, operations, etc. This scenario with different teams and different people in charge of assigning privileges on each one requires taking into account some security considerations that are described in the next section.
One key point to bear in mind is that the privileges of a derived view are independent of the privileges assigned to its underlying views. This is not specific to Denodo, it is standard behavior in all commercial databases.
A sometimes underappreciated consequence of this is that when we allow a user to create new views and decide who can execute them, this user also gains the ability to decide who can indirectly see the data of the original views used to create the new one. Let’s see an example to illustrate this point.
Example 1: let’s say a company has the following two virtual databases in Denodo:
The steward of the ‘core_db’ virtual database (Ron) has decided to give the EXECUTE privilege to the ‘hr_employee’ role, but it has not given that privilege to the ‘sales_employee’ role to protect the employee salaries data from being disclosed.
The data steward for the HR database (Amanda) has the ‘assign privileges’ role in the HR database and, therefore, she can assign the EXECUTE privilege on MANAGER_SALARIES to any role she wishes, including the ‘sales_employee’ role. But the MANAGER_SALARIES view allows seeing salaries data, so now the users with ‘sales_employee’ role have indirect visibility over the data in the EMPLOYEE view. They cannot see the salaries by executing the EMPLOYEE view directly, but they can get whatever data from EMPLOYEE that is exposed through the MANAGER_SALARIES view.
This may not be the desired outcome. Ron may want to limit who can see the data coming from the ‘core_db’ views, even for indirect access through views in higher layers. Fortunately, Ron can use fine-grained privileges for this. Let’s see an example of this.
Example 2: A first way for Ron to address the problem is creating a new row restriction in the EMPLOYEE view masking the salary column for the ‘sales_employee’ role. This way, even if Amanda gives to the users with this role the privilege to execute queries on MANAGER_SALARIES, the salary data will be masked for them.
Key Takeaway: Giving execute access over a view to a user with privilege to create views and assign privileges, is also indirectly transferring the ability to grant privileges to third users over that data. Fine-grained privileges allow us to set limits on this privilege, ensuring that certain security rules are always applied. |
This is enough when Ron knows in advance all roles that can be used to query the derived views built on top of the ‘core_db’ views. If that is the case, Ron can specify the desired restrictions in the exposed views for each role (there are some caveats to this that will be discussed in the next section). Nevertheless, when Ron is not aware of all roles used to query the views at higher layers, salaries data could still be disclosed. Let’s see a new example to illustrate how this can happen.
Example 3: Suppose that Ron is not aware that users with the role ‘marketing_employee’ are allowed to query views at ‘hr_db’. Therefore, he does not define any restriction for this role on the EMPLOYEE view. If Amanda then grants the privilege to execute the MANAGER_SALARIES view to the ‘marketing_employee’ role, the salaries data is disclosed for the users having this role.
To avoid this, Ron can either make sure that he sets suitable restrictions for all the roles that can be used at higher layers, or he can also adopt a more conservative best practice: he can make use of the Global security policies (only available with Denodo Enterprise Plus) to deny indirect visibility of the EMPLOYEE view to any role not included in a explicit list of exceptions. Let’s see an example of how to do this.
Example 4: Ron could start by defining a global policy to deny the access to view EMPLOYEE data to any role different from HR.
Now, even if Amanda grants execution privileges on MANAGER_SALARIES to the ‘sales_employee’ and ‘marketing_employee’ roles, the execution of these queries will fail because they are not allowed by the policy defined above for the EMPLOYEE view.
Now, upon Amanda’s request, Ron can decide to grant limited access to these roles, for instance by defining a restriction masking the salary for both roles and adding them to the list of roles not affected by the ‘Deny’ policy.
This best practice has the advantage of not requiring a thorough preventive analysis of the necessary limitations for each possible role. Instead, if some user or group of users need wider access, the data steward can study each case as it is requested and create the adequate policies for it.
Key Takeaway: To have tighter control of how their data is exposed in views of higher layers, stewards may consider using global policies to deny indirect visibility to the data on their views, and define specific policies for the desired exceptions. This is especially useful when the roles used at higher layers can change and/or are not known by the stewards of the lower layers. |
In the previous examples, the derived views created at the higher layers used only one view of the lower layer. The situation can get more complicated when a higher view combines several views of the lower layer, each one with different restrictions for different roles. Consider the following example to illustrate the point.
Example 5: Now, the ‘core_db’ layer exposes to higher layers the EMPLOYEE view which includes among other things the employees’ usernames, salaries and department ids; and the DEPARTMENT view, which contains information about the company departments including their id, name and geographical location. The steward of the ‘core_db’ department has defined the following restrictions on these views:
Suppose now Sally has both the ‘employee’ and the ‘manager_europe’ roles, and she executes the TOP_SALARIES view (we assume she also has privileges to do so). We will review step by step how Denodo evaluates the restrictions that are applied to this query. The key point is that Denodo will execute the query by going bottom-up in the view hierarchy and computing the applicable restrictions at each individual view in the hierarchy. More precisely:
This is probably not the desired outcome in this scenario. As in Example 3 in the previous section, the root of the problem is that the steward of the ‘core_db’ virtual database has not specified restrictions covering all the roles for the views. In this case, no restriction has been defined neither for the ‘employee’ role in the DEPARTMENT view nor for the ‘manager_europe’ role in the EMPLOYEE view.
But in this case, the problem cannot be solved by simply adding new row restrictions to EMPLOYEE and DEPARTMENT, because the desired policies require information that is not available in the views: for instance, we cannot specify a policy in the EMPLOYEE view stating that the ‘manager_europe’ role can only see data of employees from departments located in Europe, because there is not a ‘location’ column in the EMPLOYEE view.
There are two possible ways of solving this:
These alternatives result in different effective privileges for Sally in our example, so let’s analyze each of them in detail.
Example 6: To implement the first approach we could specify the following policies:
Let’s see what happens when Sally executes the TOP_SALARIES view in the new situation:
This adequately protects sensitive data, but it may seem too restrictive given Sally’s roles. So, let’s consider now the second alternative of exposing to the higher layers a combined view containing all columns required to implement the desired policies.
Example 7: Exposing to the higher layers a single view combining EMPLOYEE and DEPARTMENT and define the restrictions in it:
Let’s see what happens now when Sally executes TOP_SALARIES:
Key Takeaway: When several views of a lower layer can be combined to create views at a higher layer, make sure to define relevant restrictions for each role in all the views of the lower layer. When this is not possible (e.g. because some restrictions depend on columns that are not available in all views), consider either creating new views with all the needed columns, or using global policies to deny indirect visibility and further restrict access. |
As a side note, when creating this type of combined views, make sure to define the relevant referential constraints between them so Denodo can apply the ‘join pruning’ optimization when needed. See document “Best Practices to Maximize Performance II: Configuring the Query Optimizer” of our Knowledge Base for details.