Page Comparison

CREATE OR REPLACE ROLE restricted ''
  GRANT CONNECT, METADATA, EXECUTE ON test;= '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 TAG restrictedGLOBAL_SECURITY_POLICY cls_restricted_dob
    DESCRIPTION = 'UsedMasks toall tagcolumns viewswith 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 the cls_restricted_dob tag for non-admins without the cls_restricted or cls_restricted_dob role'
    ENABLED = TRUE
    AUDIENCE (
        NOT_IN ROLES ("grp_ds_denodo-admin"cls_restricted, cls_restricted_dob)
    )
    ELEMENTS (
        ALL VIEWS
NOT TAGGED  (rls_dept)
    )
    RESTRICTION (
        FILTER = ''
        MASKING ANY (cls_restricted_dob) 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'''

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  REJECT VIEWS
    );
 CREATE OR REPLACE GLOBAL_SECURITY_POLICY rls_dept_iamRESTRICTION (
    DESCRIPTION = 'RLS department policyFILTER for= data strategy''
    ENABLED = TRUE  MASKING ANY  AUDIENCE(cls_restricted_gender) WITH (HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, texts WITH ANYDEFAULT) ROLES
(iam)
    );

CREATE OR REPLACE ELEMENTS (GLOBAL_SECURITY_POLICY cls_restricted_race
    DESCRIPTION = 'Masks all COLUMNScolumns TAGGEDwith ANY (rls_dept)
    )
    RESTRICTION (
   the cls_restricted_race tag for non-admins without the cls_restricted or cls_restricted_race role'
    FILTERENABLED = 'rls_deptTRUE
= ''IAM'''   AUDIENCE (
     REJECT   NOT_IN ROLES (cls_restricted, cls_restricted_race);

CREATE OR REPLACE GLOBAL_SECURITY_POLICY rls_dept_mask_confidential )
    ELEMENTS DESCRIPTION(
= 'Applies confidential CLS to department-based RLS views' ALL VIEWS
  ENABLED = TRUE)
    AUDIENCERESTRICTION (
        FILTER = NOT_IN''
ROLES (data_strategy, iam)     ) MASKING ANY (cls_restricted_race) WITH ELEMENTS(HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, texts WITH DEFAULT) 
VIEWS TAGGED ANY  (rls_dept);

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,
           cast('2024-11-02' AS TIMESTAMP) AS termination_dt,
  );

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

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

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

select * from test.rls_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,rls_iam,restricted');