This article gives some examples on how to assign privileges to roles or users automatically, based on some property of the views to be accessed.
Denodo privilege system acts at the database or element level. This means that there is no concept of folder privileges. However, through the usage of the predefined stored procedures that query the Virtual DataPort metadata combined with Scheduler, we can automate the privileges assignment even at the folder level.
The most typical example, that surfaces often is about applying the same set of privileges on all the views placed in the same folder (Case 1). In the other scenarios treated in this article we will see how to apply the same set of privileges to all interface views, the ones that are typically exposed to the data consumers (Case 2). Then we will explain how to apply a masking rule on all the views that contain a given field (Case 3). Finally, we will see how to apply a row restriction when the view description contains a given tag (Case 4).
Throughout the read of this article, and while generalizing these examples to your needs, you may find it useful to keep an eye on the privileges granting statements syntax as a reference.
Let’s say that you want to give read access to all users assigned to role finalusers to all the views in the folder /published in database mydb. This is a typical scenario in which all the views in this folder must be public for final consumers.
Keep in mind the following considerations:
database in which the folder lives.
ALTER ROLE <role name> GRANT <Privilege1,Privilege2,...,PrivilegeN> ON <view name>
Then, the high-level procedure to automatically assign privileges would consist in defining a Scheduler job that executes, with an appropriate frequency, a series of dynamically generated ALTER ROLE statements obtained by querying the metadata catalog, via the get_views() and catalog_permissions() stored procedures.
This is the step-by-step procedure in the case where the role is called finalusers, the database is called mydb and the folder is /published.
SELECT 'ALTER ROLE finalusers GRANT Execute ON ' || database_name || '.' || name ||';' AS alter_role_vql
With this query you are forming a valid ALTER ROLE VQL statement per view in folder /published. Each of these statements will be executed when the scheduler job is run, meaning that the statement does not have any effect on those views with the privileges already assigned. If you have a very large number of views in the folder you may want to avoid running ALTER ROLE statements on those views. This can be achieved by joining the views resulting from the stored procedures GET_VIEWS and CATALOG_PERMISSIONS:
SELECT 'ALTER ROLE finalusers GRANT EXECUTE ON ' || database_name || '.' || gv.name ||';' AS alter_role_vql
The query specified at step 3 in Case 1 can be modified according to your needs: we can change the privileges to be applied (METADATA, EXECUTE …) as well as the objects which we want to apply the privileges on.
In this case, we would like to again apply the METADATA and EXECUTE privileges on all the views of type interface.
SELECT 'ALTER ROLE finalusers GRANT EXECUTE ON ' || database_name || '.' || gv.name ||';'
The only difference with the query at step 4 in Case 1 is an additional filtering condition that outputs just the interface views (view_type=2).
Let us now study the scenario where we want to apply the masking of the column ssn on all the derived views in database mydb that contain that field for users assigned to role finalusers.
For each view that satisfies those conditions we need to run two ALTER ROLE statements; the first one is the same as in the cases above, while the second one applies the masking rule.
The syntax of the masking rule statement is as follows:
ALTER ROLE <role name> GRANT EXECUTE ANY(<field to be masked>) THEN <masking condition> MASKING ON <view to be masked>;
In our specific scenario, the whole query would be the following:
SELECT 'ALTER ROLE finalusers GRANT EXECUTE ON VIEW ' || database_name || '.' || name || '; ALTER ROLE finalusers GRANT EXECUTE WHEN ANY(ssn) THEN ' || '''1<>1''' || ' MASKING ON ' || database_name || '.' || name ||';' AS alter_role_vql
WHERE name in (SELECT distinct view_name FROM GET_VIEW_COLUMNS() WHERE column_name = 'ssn' AND input_database = 'mydb')
Once again, this query can be automatized with the technique described in Case 1.
In this case, we are in the scenario where we would like to restrict the records that users assigned to a given role can see on all the views that include a given text in the view description.
Specifically, in this example we would like to show only the rows with cc_company=1 to users assigned to role salesanalysts_cc1 for all views that contain the tag #DataCorp in their description. This assumes a scenario where DataCorp code is 1 (cc_company=1) and the development convention dictates to add this tag to all views that should be executable by analysts belonging to that organization.
Again, we just need to change the query in step 3 described in Case 1 with the following:
AND description LIKE '%#DataCorp%';
In this article we have seen a privileges assignment technique that leverages metadata-querying predefined stored procedures (GET_VIEWS, GET_VIEW_COLUMNS) along with parameterized queries in Denodo Scheduler to apply your privileges policy in an automated manner. The idea is to dynamically construct the proper ALTER ROLE statement on the views that match some user-defined criteria. This pattern can be customized to match your needs.