Page Comparison

...

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.

...

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

Code Block

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 = '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

...

Image RemovedRestricted policy VQL

Code Block

CREATE OR REPLACE ROLE restricted '' GRANT ROLE confidential 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 = '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) );

...

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.

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_all ''
  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_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 
    );

...

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

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

CREATE OR REPLACE VIEW security_test
    FOLDER = '/02-transformation'
(
    dept tags (rls_dept)
    hire_dt TAGS (confidential),
    termination_dt TAGS (confidential),
    annual_rt TAGS (confidential),
    date_of_birth TAGS (restricted),
 termination_dt   gender TAGS (restricted),
    race TAGS (confidential),restricted)
 )
 AS
    with st_union as
    (
      SELECT '0001' AS emplid,
             'John Doe' AS name,
             'DS' AS dept,
             cast('2024-11-01' AS TIMESTAMP) AS annual_rthire_dt,
             TAGS(confidential),cast('2024-11-02' AS TIMESTAMP) AS termination_dt,
             100000.0 AS date_of_birthannual_rt,
             TAGS(restricted),cast('1990-01-01' AS DATE) AS date_of_birth,
         gender
     'Male' AS gender,
      TAGS(restricted),       'Unknown' AS race
      FROM  TAGSdual(restricted)
    ) AS  UNION ALL
      SELECT '00010002' 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.security_test context('impersonate_roles' = 'generalread_all');

...

Confidential

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

...

Restricted

...

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

...

...

Department RLS

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

...

Department RLS Unmasked

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.

...