Tagging

Tags can be used to classify views or columns in Denodo to make browsing easier, or for application of security policies.

Here is a guide to Tags in Denodo: https://community.denodo.com/kb/en/view/document/How%20to%20use%20Denodo%20Tags

Note that in order for new tags to appear in the Data Catalog, they must be manually synced once in each Data Catalog environment after they’re created, after which they will update automatically as changes are made and deployed.

Security

Global Security Policies in Denodo allow masking and filtering of columns and rows based on user-assigned roles and tags.

Here is a guide to Global Security Policies in Denodo: https://community.denodo.com/docs/html/browse/9.0/en/vdp/administration/global_security_policies/global_security_policies

Column-level Security

While data classification is still a work in progress at Tufts, below is a table of some draft classifications.

Tags, policies and roles have been added for Confidential and Restricted in the dev environment for testing. Ultimately, roles will exist in Grouper where their membership can be managed.

Confidential

Confidential policy VQL

CREATE OR REPLACE GLOBAL_SECURITY_POLICY mask_confidential_columns
    DESCRIPTION = 'Masks all columns with the confidential tag for non-admins without the confidential role'
    ENABLED = TRUE
    AUDIENCE (
        NOT_IN ROLES (confidential, "grp_ds_denodo-admin")
    )
    ELEMENTS (
        ALL VIEWS
    )
    RESTRICTION (
        FILTER = ''
        MASKING ANY (confidential) WITH (HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, texts WITH DEFAULT) 
    );

Restricted

Restricted policy VQL

CREATE OR REPLACE GLOBAL_SECURITY_POLICY mask_restricted_columns
    DESCRIPTION = 'Masks all columns that have the restricted tag for non-admins without the restricted role.'
    ENABLED = TRUE
    AUDIENCE (
        NOT_IN ROLES ("grp_ds_denodo-admin", restricted)
    )
    ELEMENTS (
        ALL VIEWS
    )
    RESTRICTION (
        FILTER = ''
        MASKING ANY (restricted) WITH (HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, texts WITH DEFAULT) 
    );

Demo

A test view test.security test has been created in the test VDB.

CREATE OR REPLACE VIEW security_test
    FOLDER = '/02-transformation'(
        hire_dt
        TAGS(confidential),
        termination_dt
        TAGS(confidential),
        annual_rt
        TAGS(confidential),
        date_of_birth
        TAGS(restricted),
        gender
        TAGS(restricted),
        race
        TAGS(restricted)
    ) AS
    SELECT '0001' AS emplid,
           'John Doe' AS name,
           cast('2024-11-01' AS TIMESTAMP) AS hire_dt,
           cast('2024-11-02' AS TIMESTAMP) AS termination_dt,
           100000.0 AS annual_rt,
           cast('1990-01-01' AS DATE) AS date_of_birth,
           'Male' AS gender,
           'Unknown' AS race
    FROM dual();

Each of these three queries demonstrates a different level of masking

General

select * from test.security_test context('impersonate_roles' = 'general');

Confidential

select * from test.security_test context('impersonate_roles' = 'confidential');

Restricted

Note that the confidential role has been added to restricted so it can satisfy any confidential policy restriction as well

select * from test.security_test context('impersonate_roles' = 'restricted');

Row-level Security

Work in progress…

Tableau

Using Denodo

Most use cases should be able to use Denodo security by prompting users for credentials to connect to a Denodo data source in Tableau. This can be set when publishing a workbook to the Tableau server.

Now when trying to access that workbook, users will be prompted for their credentials and Denodo will apply RLS based on their privileges

Note that if you have multiple data sources, users will be prompted for credentials for each data source. Thankfully Tableau can remember passwords for each user and data source and it’s recommended that users do this.

Using Tableau User Functions

In the rare case where you need to implement security within Tableau, you can do so by filtering based on usernames, or an individual AD group.

Users

If you have a column named userswith comma separated user names like jdoe01,jdoe02,jdoe03 you check if a user existing in the like by creating a calculated field with the following formula:

CONTAINS([users], USERNAME())

You can then create filter on your data for where this is True.

AD Group

If you have a column named group with a single AD group you can check if a user is a member by creating a calculated field with the formula:

 ISMEMBEROF([group])

You can then create filter on your data for where this is True.

Tableau does not currently have an easy way to check for membership of multiple groups.

Other Tableau User Function can be found here: https://help.tableau.com/current/pro/desktop/en-us/functions_functions_user.htm