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.

Restricted

VQL

CREATE OR REPLACE ROLE cls_restricted ''
GRANT CONNECT, METADATA, EXECUTE ON test;

CREATE OR REPLACE ROLE cls_restricted_dob ''
GRANT CONNECT, METADATA, EXECUTE ON test;

CREATE OR REPLACE ROLE cls_restricted_gender ''
GRANT CONNECT, METADATA, EXECUTE ON test;

CREATE OR REPLACE ROLE cls_restricted_race ''
GRANT CONNECT, METADATA, EXECUTE ON test;

CREATE OR REPLACE TAG cls
    DESCRIPTION = 'Tags views and columns that have CLS (column level security) applied';

CREATE OR REPLACE TAG cls_restricted
    DESCRIPTION = 'Tags columns with restricted data that have column-level security (CLS) applied.';

CREATE OR REPLACE TAG cls_restricted_dob
    DESCRIPTION = 'Tags columns with restricted data of birth (DOB) data to have column-level security (CLS) applied';

CREATE OR REPLACE TAG cls_restricted_gender
    DESCRIPTION = 'Tags columns with restricted gender data to have column-level security (CLS) applied';
    
CREATE OR REPLACE TAG cls_restricted_race
    DESCRIPTION = 'Tags columns with restricted race data to have column-level security (CLS) applied';

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

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

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

Confidential

VQL

CREATE OR REPLACE ROLE cls_confidential ''
  GRANT CONNECT, METADATA, EXECUTE ON test;
  
CREATE OR REPLACE TAG cls_confidential
    DESCRIPTION = 'Used to tag columns that fall under the Confidential data definition to apply column-level security (CLS)';
    
CREATE OR REPLACE GLOBAL_SECURITY_POLICY cls_confidential
    DESCRIPTION = 'Masks all columns with the cls_confidential tag for non-admins without the cls_confidential role'
    ENABLED = TRUE
    AUDIENCE (
        NOT_IN ROLES (cls_confidential, cls_restricted)
    )
    ELEMENTS (
        ALL VIEWS
    )
    RESTRICTION (
        FILTER = ''
        MASKING ANY (cls_confidential) WITH (HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, texts WITH DEFAULT) 
    );

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 a policy and roles for hiding rows based on department.

VQL

CREATE OR REPLACE ROLE rls_data_strategy ''
  GRANT CONNECT, METADATA, EXECUTE ON test;
  
CREATE OR REPLACE ROLE rls_iam ''
  GRANT CONNECT, METADATA, EXECUTE ON test;
  
CREATE OR REPLACE ROLE general ''
  GRANT CONNECT, METADATA, EXECUTE ON test;
  
CREATE OR REPLACE TAG rls
    DESCRIPTION = 'Used to tag views and columns with RLS applied';
    
CREATE OR REPLACE TAG rls_dept
    DESCRIPTION = 'Used to tag columns with department values for application of row-level security (RLS)';

CREATE OR REPLACE TAG rls_assigned_roles
    DESCRIPTION = 'Used to tag column containing a users assigned roles';

CREATE OR REPLACE GLOBAL_SECURITY_POLICY rls_dept
    DESCRIPTION = 'Limits rows based on departments associated with users assigned roles'
    ENABLED = TRUE
    AUDIENCE (
        ALL
    )
    ELEMENTS (
        COLUMNS TAGGED ANY (rls_dept)
    )
    RESTRICTION (
        FILTER = '((strings_have_intersection(''rls_iam,grp_ds_denodo-ir-read'', rls_assigned_roles, '','') AND rls_dept = ''IAM'') OR (strings_have_intersection(''rls_data_strategy,grp_ds_denodo-ir-read'', rls_assigned_roles, '','') AND rls_dept = ''DS''))'
        REJECT 
    )

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 security_test FOLDER = '/02-transformation'
    TAGS (cls, rls) (
    dept TAGS (rls, rls_dept),
    hire_dt TAGS (cls, cls_confidential),
    termination_dt TAGS (cls, cls_confidential),
    annual_rt TAGS (cls, cls_confidential),
    date_of_birth TAGS (cls, cls_restricted, cls_restricted_dob),
    gender TAGS (cls, cls_restricted, cls_restricted_gender),
    race TAGS (cls, cls_restricted, cls_restricted_race),
    assigned_roles TAGS (rls_assigned_roles)
 )
 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 st_union.*, roles.assigned_roles from st_union left join admin.get_user_roles_string roles on 1=1 CONTEXT ('formatted' = 'yes');

The queries below demonstrate combinations of CLS and RLS

General

A user without any CLS or RLS roles

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

Admin

Admin users and roles bypass global security polices so are able to see all rows and columns

select 
    * 
from test.security_test 
context('impersonate_roles'='grp_ds_denodo-admin');

Read all, confidential

A role that has “read all” access to rows, coupled with the confidential role

select 
    * 
from test.security_test 
context('impersonate_roles'='grp_ds_denodo-ir-read,cls_confidential')

Department role, restricted DOB

A department-specific role, with access to only restricted the DOB column

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

Multiple department roles, restricted and implied confidential access

select 
    * 
from test.security_test 
context('impersonate_roles'='rls_data_strategy,rls_iam,cls_restricted');

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)

Restricted

Confidential

Row-level Security (RLS)

Demo

General

Caching

Tableau

Using Denodo

Using Tableau User Functions