Page Comparison

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 (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 NOT_IN ROLES ("grp_ds_denodo-admin", restricted)= ''
      )  MASKING ANY  ELEMENTS(cls_restricted_dob) WITH (HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, texts WITH VIEWSDEFAULT) NOT
TAGGED  (rls_dept)  );

CREATE )OR REPLACE GLOBAL_SECURITY_POLICY cls_restricted_gender
 RESTRICTION (  DESCRIPTION = 'Masks all columns with the FILTER = ''
        MASKING ANY (restricted) WITH (HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, texts WITH DEFAULT) 
    );

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
    AUDIENCEcls_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 (
        NOT_INFILTER ROLES (data_strategy, iam)= ''
     )   MASKING  ELEMENTSANY (cls_restricted_race) WITH (HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, VIEWS TAGGED ANY  (rls_depttexts WITH DEFAULT) 
    );

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,;

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

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

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

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

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.

rls_data_strategy,cls_restricted_dob');