Page Comparison

= 'Tags columns with restricted race data to have column-level security (CLS) applied';

CREATE OR REPLACE ROLE restricted ''GLOBAL_SECURITY_POLICY cls_restricted_dob
   GRANT CONNECT,DESCRIPTION METADATA,= EXECUTE'Masks ONall test;columns CREATEwith 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_columnsthe 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)
    DESCRIPTION)
= 'For non-RLS views, masksELEMENTS all(
columns with the confidential tag for non-admins without theALL confidentialVIEWS
role'    )
ENABLED = TRUE     AUDIENCERESTRICTION (
        NOT_INFILTER ROLES ("grp_ds_denodo-admin", restricted)= ''
    )    MASKING ELEMENTSANY (cls_restricted_dob) WITH (HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, VIEWStexts NOTWITH TAGGED  (rls_dept)DEFAULT) 
    );

CREATE OR REPLACE RESTRICTION (GLOBAL_SECURITY_POLICY cls_restricted_gender
        FILTERDESCRIPTION = ''Masks all columns with the cls_restricted_gender tag for  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'''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,cls_restricted_dob');