Page Comparison

...

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

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
    AUDIENCE (
        NOT_IN ROLES (data_strategy, iam)
    )
    ELEMENTS (
        VIEWS TAGGED ANY  (rls_dept)
    )
    RESTRICTION (
        FILTER = ''
        MASKING ANY (confidential) WITH (HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, texts WITH DEFAULT) 
    );

CREATE OR REPLACE GLOBAL_SECURITY_POLICY rls_dept_mask_restricted
    DESCRIPTION = 'Applies confidential CLS to department-based RLS views'
    ENABLED = TRUE
    AUDIENCE (
        NOT_IN ROLES (iam)
    )
    ELEMENTS (
        VIEWS TAGGED ANY  (rls_dept)
    )
    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

Demo

...

Code Block

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

...

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

...

Department RLS, read_test (

...

all rows, no confidential or restricted)

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

...