Page Comparison

...

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 = 'MasksFor 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 ALL VIEWS(rls_dept)
    )
    RESTRICTION (
        FILTER = ''
        MASKING ANY (confidential) WITH (HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, texts WITH DEFAULT) 
    );

...

Restricted

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 = 'MasksFor non-RLS views, masks all columns that havewith the restrictedconfidential tag for non-admins without the restrictedconfidential role.'
    ENABLED = TRUE
    AUDIENCE (
        NOT_IN ROLES ("grp_ds_denodo-admin", restricted)
    )
    ELEMENTS (
        ALL VIEWS NOT TAGGED  (rls_dept)
    )
    RESTRICTION (
        FILTER = ''
        MASKING ANY (restricted) WITH (HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, texts WITH DEFAULT) 
    );

...

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. They will also hide

VQL

Code Block

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_alltest ''
  GRANT CONNECT, METADATA, EXECUTE ON test;
  
CREATE OR REPLACE TAGROLE rls_deptgeneral ''
  GRANT DESCRIPTIONCONNECT, = 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_depthide_data_strategyall
    DESCRIPTION = 'RLSHides departmentall policyrows for dataRLS-enabled strategyviews'
    ENABLED = TRUE
    AUDIENCE (
        ANYNOT_IN ROLES (data_strategy, iam, read_test)
    )
    ELEMENTS (
        COLUMNS TAGGED ANY (rls_dept)
    )
    RESTRICTION (
        FILTER = 'rls_dept = ''DS'''0=1'
        REJECT 
    );


CREATE OR REPLACE GLOBAL_SECURITY_POLICY rls_dept_data_iamstrategy
    DESCRIPTION = 'RLS department policy for data strategy'
    ENABLED = TRUE
    AUDIENCE (
        ANY ROLES (iamdata_strategy)
    )
    ELEMENTS (
        COLUMNS TAGGED ANY (rls_dept)
    )
    RESTRICTION (
        FILTER = 'rls_dept = ''IAMDS'''
        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.

Code Block

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),
    gender TAGS (restricted),
    race TAGS (restricted)
 )
 AS
    with st_union as
    (
      SELECT '0001' AS emplid,

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 
    );
'John
Doe'CREATE ASOR name,
   REPLACE GLOBAL_SECURITY_POLICY rls_dept_mask_restricted
    DESCRIPTION = 'Applies confidential CLS to department-based RLS views'DS'
 AS dept,  ENABLED = TRUE
    AUDIENCE (
   cast('2024-11-01' AS TIMESTAMP) AS hire_dt, ANY ROLES (data_strategy)
    )
    ELEMENTS cast('2024-11-02' AS TIMESTAMP) AS termination_dt,(
        VIEWS TAGGED ANY  (rls_dept)
    )
 100000.0 AS annual_rt,  RESTRICTION (
        FILTER = cast('1990-01-01'
AS DATE) AS date_of_birth,     MASKING ANY (restricted) WITH (HIDE) (numbers WITH DEFAULT, datetimes 'Male' AS genderWITH DEFAULT, texts             'Unknown' AS raceWITH DEFAULT) 
     FROM dual()
      UNION ALL
      SELECT '0002' AS emplid,;

...

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.

Code Block

CREATE OR REPLACE VIEW cls_test
    FOLDER = '/02-transformation'(
        hire_dt
       'John Doe' AS name, TAGS(confidential),
        termination_dt
      'IAM' AS deptTAGS(confidential),
        annual_rt
    cast('2024-11-01' AS TIMESTAMP) AS hire_dt TAGS(confidential),
        date_of_birth
     cast('2024-11-02' AS TIMESTAMP) AS termination_dt,   TAGS(restricted),
        gender
      100000.0 AS annual_rt TAGS(restricted),
        race
    cast('1990-01-01' AS DATE) AS date_of_birth, TAGS(restricted)
    ) AS
    SELECT   'Male0001' AS genderemplid,
           'John  'UnknownDoe' AS racename,
          FROM dual(cast('2024-11-01' AS TIMESTAMP) AS hire_dt,
  )     select * from st_union CONTEXT cast('formatted2024-11-02' =AS 'yes');

...

The queries below demonstrate combinations of CLS and RLS

General

Code Block
select * from test.security_test context('impersonate_roles' = 'read_all');

...

Confidential

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

...

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' = 'read_all,restricted');

...

Department RLS

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

...

Department RLS Unmasked

Code Block

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

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

...

Confidential

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

Code Block
select * from test.cls_test context('impersonate_roles' = 'restricted');

...

Department RLS, DS, Confidential

Code Block
select * from test.cls_test context('impersonate_roles'='data_strategy');

...

Department RLS, IAM, Restricted

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

...

Department RLS, Stacked

Code Block
select * from test.security_test context('impersonate_roles'='data_strategy,iam');

...

Department RLS, read_test (bypass)

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

...

Department RLS, general user

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

...

Info

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.

...

Version	Old Version 11	New Version 12
Changes made by	Matthew Girard	Matthew Girard
Saved on	Nov 27, 2024	Nov 28, 2024

Versions Compared

Key

Restricted

Demo

Demo

General

Confidential

Restricted

Department RLS

Department RLS Unmasked

General

Confidential

Restricted

Department RLS, DS, Confidential

Department RLS, IAM, Restricted

Department RLS, Stacked

Department RLS, general user

Caching

Tableau

Using Denodo