Versions Compared

Version Old Version 5 New Version 6
Changes made by

Matthew Girard

Matthew Girard

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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.

image-20241123-005217.pngimage-20241123-005250.png

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

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

Generally Available

Confidential

Restricted

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)

•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 and policies have been added for Confidential and Restricted in the dev environment for testing

image-20241123-003757.png

Confidential policy VQL

Code Block
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) 
    );
image-20241123-003811.png

image-20241123-010732.png

Restricted policy VQL

Code Block
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.

Code Block
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();
image-20241123-004244.png

Each of these three queries demonstrates a different level of masking

General

Code Block
select * from test.security_test context('impersonate_roles' = 'general');
image-20241123-004618.png

Confidential

Code Block
select * from test.security_test context('impersonate_roles' = 'confidential');
image-20241123-004939.png

Restricted

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

Code Block
select * from test.security_test context('impersonate_roles' = 'restricted');
image-20241123-005006.png