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/deployed, 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 (CLS)

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

Data Class →

Generally Available (Green)

Confidential (Yellow)

Restricted (Red)

Audience

All data access roles (not public)

Dean/Division Leader Role
(Identified data for own unit)

Reporting and Compliance Roles

(University-wide access as needed)

Others must provide justification for use

Identified data

Reporting and Compliance Roles

(University-wide access)

Types of Data

•Name

•Title (fac/staff) / Level (students)

•HR Title (fac/staff)

•Department / Program

•Supervisor

•Campus

•Pronouns (user provided)

•Email address

•Affiliations (faculty/staff/student/...)

•Classification (temp, post doc, RA, staff/grade, faculty)

•Compensation

•Course registrations

•Age range (..., 25-35, 36-45, …)

•Financial transaction data

•Leave/return dates

•Service dates

•Space assignments

•Grant proposals and awards

•Enrollment status

•Home address

•Local address

•Alum/donor name/contact info

•Citizenship

•Race/ethnicity

•Gender identity

•Religion

•PHI

•Marital status

•Date of birth

•Benefit selections

•Admissions decisions (before release)

•Grades

•Alum/donor gift history

•Social security number

•Driver’s license number

•Passport number

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

VQL

CREATE OR REPLACE ROLE confidential ''
  GRANT CONNECT, METADATA, EXECUTE ON test
  
CREATE OR REPLACE TAG confidential
    DESCRIPTION = 'Used to tag views or columns that fall under the Confidential data definition';
    
CREATE OR REPLACE GLOBAL_SECURITY_POLICY mask_confidential_columns
    DESCRIPTION = 'For non-RLS views, 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", restricted)
    )
    ELEMENTS (
        VIEWS NOT TAGGED  (rls_dept)
    )
    RESTRICTION (
        FILTER = ''
        MASKING ANY (confidential) WITH (HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, texts WITH DEFAULT) 
    );

Restricted

VQL

CREATE OR REPLACE ROLE restricted ''
  GRANT CONNECT, METADATA, EXECUTE ON test;
CREATE OR REPLACE TAG restricted
    DESCRIPTION = 'Used to tag views or columns whose data fall under the Restricted definition'
CREATE OR REPLACE GLOBAL_SECURITY_POLICY mask_restricted_columns
    DESCRIPTION = 'For non-RLS views, masks all columns with the confidential tag for non-admins without the confidential role'
    ENABLED = TRUE
    AUDIENCE (
        NOT_IN ROLES ("grp_ds_denodo-admin", restricted)
    )
    ELEMENTS (
        VIEWS NOT TAGGED  (rls_dept)
    )
    RESTRICTION (
        FILTER = ''
        MASKING ANY (restricted) WITH (HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, texts WITH DEFAULT) 
    );

This can be expanded upon by leveraging user attributes from LDAP/SAML: https://community.denodo.com/kb/en/view/document/How%20to%20implement%20ABAC%20Capabilities%20with%20Global%20Security%20Policies

Row-level Security (RLS)

In addition to column-level security, you can employ row-level security and limit the rows users can see. The VQL below will create policies to hide all rows for all users except ones with the read_test, data_strategy or iam roles.

VQL

CREATE OR REPLACE ROLE data_strategy ''
  GRANT CONNECT, METADATA, EXECUTE ON test;
  
CREATE OR REPLACE ROLE iam ''
  GRANT CONNECT, METADATA, EXECUTE ON test;
  
CREATE OR REPLACE ROLE read_test ''
  GRANT CONNECT, METADATA, EXECUTE ON test;
  
CREATE OR REPLACE ROLE general ''
  GRANT CONNECT, METADATA, EXECUTE ON test;
  
CREATE OR REPLACE TAG rls_dept
    DESCRIPTION = 'Used to tag views that should have RLS applied based on department';

CREATE OR REPLACE GLOBAL_SECURITY_POLICY rls_hide_all
    DESCRIPTION = 'Hides all rows for RLS-enabled views'
    ENABLED = TRUE
    AUDIENCE (
        NOT_IN ROLES (data_strategy, iam, read_test)
    )
    ELEMENTS (
        COLUMNS TAGGED ANY (rls_dept)
    )
    RESTRICTION (
        FILTER = '0=1'
        REJECT 
    );


CREATE OR REPLACE GLOBAL_SECURITY_POLICY rls_dept_data_strategy
    DESCRIPTION = 'RLS department policy for data strategy'
    ENABLED = TRUE
    AUDIENCE (
        ANY ROLES (data_strategy)
    )
    ELEMENTS (
        COLUMNS TAGGED ANY (rls_dept)
    )
    RESTRICTION (
        FILTER = 'rls_dept = ''DS'''
        REJECT 
    );

CREATE OR REPLACE GLOBAL_SECURITY_POLICY rls_dept_iam
    DESCRIPTION = 'RLS department policy for data strategy'
    ENABLED = TRUE
    AUDIENCE (
        ANY ROLES (iam)
    )
    ELEMENTS (
        COLUMNS TAGGED ANY (rls_dept)
    )
    RESTRICTION (
        FILTER = 'rls_dept = ''IAM'''
        REJECT 
    );

CREATE OR REPLACE GLOBAL_SECURITY_POLICY rls_dept_mask_confidential
    DESCRIPTION = 'Applies confidential CLS to department-based RLS views'
    ENABLED = TRUE
    AUDIENCE (
        NOT_IN ROLES (data_strategy, iam)
    )
    ELEMENTS (
        VIEWS TAGGED ANY  (rls_dept)
    )
    RESTRICTION (
        FILTER = ''
        MASKING ANY (confidential) WITH (HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, texts WITH DEFAULT) 
    );

CREATE OR REPLACE GLOBAL_SECURITY_POLICY rls_dept_mask_restricted
    DESCRIPTION = 'Applies confidential CLS to department-based RLS views'
    ENABLED = TRUE
    AUDIENCE (
        NOT_IN ROLES (iam)
    )
    ELEMENTS (
        VIEWS TAGGED ANY  (rls_dept)
    )
    RESTRICTION (
        FILTER = ''
        MASKING ANY (restricted) WITH (HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, texts WITH DEFAULT) 
    );

This can be expanded upon by leveraging user attributes from LDAP/SAML: https://community.denodo.com/kb/en/view/document/How%20to%20implement%20ABAC%20Capabilities%20with%20Global%20Security%20Policies

Demo

CREATE OR REPLACE VIEW cls_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();

CREATE OR REPLACE VIEW rls_test FOLDER = '/02-transformation'
tags (rls_dept)
(
    dept tags (rls_dept)
    hire_dt TAGS (confidential),
    termination_dt TAGS (confidential),
    annual_rt TAGS (confidential),
    date_of_birth TAGS (restricted),
    gender TAGS (restricted),
    race TAGS (restricted)
 )
 AS
    with st_union as
    (
      SELECT '0001' AS emplid,
             'John Doe' AS name,
             'DS' AS dept,
             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()
      UNION ALL
      SELECT '0002' AS emplid,
             'John Doe' AS name,
             'IAM' AS dept,
             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()
    )
    select * from st_union CONTEXT ('formatted' = 'yes');

The queries below demonstrate combinations of CLS and RLS

General

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

Confidential

select * from test.cls_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.cls_test context('impersonate_roles' = 'restricted');

Department RLS, DS, Confidential

select * from test.rls_test context('impersonate_roles'='data_strategy');

Department RLS, IAM, Restricted

select * from test.rls_test context('impersonate_roles'='iam');

Department RLS, Stacked

select * from test.rls_test context('impersonate_roles'='data_strategy,iam');

Department RLS, read_test (all rows, no confidential or restricted)

select * from test.rls_test context('impersonate_roles'='read_test');

Department RLS, general user

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

Combining role-based policies for both CLS and RLS together is tricky in Denodo unless the RLS limiting role is also included in an appropriate CLS policy. To prevent role clashing, and simplify implementation, it’s best to implement RLS using session attributes, and CLS using roles.

If a user has a role that isn’t included in the list of included roles of a policy, and that role has execute access to a view, they can bypass that policy for that view. Also, in order for a role in a list of excluded roles in a policy to be effective, it must have the execute privilege on the views in the policy.

Caching

Denodo applies policies on top of cached data. Since policies do not apply to Administrator user types, users with the serveradmin role, or admins of individual VDB’s, it’s recommended that an admin user be used for caching jobs since this will guarantee that no policies are applied during caching.

Tableau

Data security in Tableau be implemented either using Denodo, or by using Tableau user functions.

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. Note that Tableau hashes credentials for a data source based on the server name NOT the data source name. Because of this, if you have multiple data sources based on the same server, Tableau will use the last set of credentials you enter for that server.

The above approach does require that data sources be Live. Because of this, all views should be optimized and cached to ensure adequate performance.

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 against a list of multiple groups.

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

Tagging and Security

Tagging

Security

Column-level Security (CLS)

Confidential

Restricted

Row-level Security (RLS)

Demo

General

Confidential

Restricted

Department RLS, DS, Confidential

Department RLS, IAM, Restricted

Department RLS, Stacked

Department RLS, read_test (all rows, no confidential or restricted)

Department RLS, general user

Caching

Tableau

Using Denodo

Using Tableau User Functions