Content Comparison

CREATE OR REPLACE ROLE cls_restricted ''
GRANT CONNECT, METADATA, EXECUTE ON test;

CREATE OR REPLACE ROLE cls_restricted_dob ''
GRANT CONNECT, METADATA, EXECUTE ON test;

CREATE OR REPLACE ROLE cls_restricted_gender ''
GRANT CONNECT, METADATA, EXECUTE ON test;

CREATE OR REPLACE ROLE cls_restricted_race ''
GRANT CONNECT, METADATA, EXECUTE ON test;

CREATE OR REPLACE TAG cls
    DESCRIPTION = 'Tags views and columns that have CLS (column 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 maskcls_confidential_columns
    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 = ''
        MASKING ANY (cls_restricted_dob) WITH (HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, texts WITH DEFAULT) 
    );

CREATE OR REPLACE GLOBAL_SECURITY_POLICY cls_restricted_gender
    DESCRIPTION = 'Masks all columns with the confidentialcls_restricted_gender tag for non-admins without the confidentialcls_restricted or cls_restricted_gender role'
    ENABLED = TRUE
    AUDIENCE (
        NOT_IN ROLES (confidential, "grp_ds_denodo-admin"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 (
        FILTER = ''
        MASKING ANY (confidentialcls_restricted_race) WITH (HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, texts WITH DEFAULT) 
    );

CREATE OR REPLACE ROLE rls_data_strategy ''
  GRANT CONNECT, METADATA, EXECUTE ON test;
  
CREATE OR REPLACE ROLE rls_iam ''
  GRANT CONNECT, METADATA, EXECUTE ON test;
  
CREATE OR REPLACE ROLE general ''
  GRANT CONNECT, METADATA, EXECUTE ON test;
  
CREATE OR REPLACE TAG rls
    DESCRIPTION = 'Used to tag views and columns with RLS applied';
    
CREATE OR REPLACE TAG rls_dept
    DESCRIPTION = 'Used to tag columns with department values for application of row-level security (RLS)';

CREATE OR REPLACE TAG rls_assigned_roles
    DESCRIPTION = 'Used to tag column containing a users assigned roles';

CREATE OR REPLACE GLOBAL_SECURITY_POLICY rls_dept
    DESCRIPTION = 'Limits rows based on departments associated with users assigned roles'
    ENABLED = TRUE
    AUDIENCE (
        ALL
    )
    ELEMENTS (
        COLUMNS TAGGED ANY (rls_dept)
    )
    RESTRICTION (
        FILTER = '((strings_have_intersection(''rls_iam,grp_ds_denodo-ir-read'', rls_assigned_roles, '','') AND rls_dept = ''IAM'') OR (strings_have_intersection(''rls_data_strategy,grp_ds_denodo-ir-read'', rls_assigned_roles, '','') AND rls_dept = ''DS''))'
        REJECT 
    )

CREATE OR REPLACE VIEW security_test FOLDER = '/02-transformation'
    TAGS (cls, rls) (
    dept TAGS (rls, rls_dept),
    hire_dt TAGS (cls, cls_confidential),
    termination_dt TAGS (cls, cls_confidential),
    annual_rt TAGS (cls,  termination_dt
   cls_confidential),
    date_of_birth TAGS (cls, cls_restricted, cls_restricted_dob),
    gender TAGS(confidential (cls, cls_restricted, cls_restricted_gender),
    race TAGS (cls,  annual_rt
cls_restricted, cls_restricted_race),
    assigned_roles TAGS (rls_assigned_roles)
 )
 AS
    with st_union as
TAGS(confidential),    (
      SELECT '0001' AS emplid,
             'John Doe' AS name,
             'DS' AS dept,
             cast('2024-11-01' AS TIMESTAMP) AS date_of_birthhire_dt,
             TAGS(restricted)cast('2024-11-02' AS TIMESTAMP) AS termination_dt,
        gender     100000.0 AS annual_rt,
             TAGS(restricted)cast('1990-01-01' AS DATE) AS date_of_birth,
             'Male' AS gender,
             'Unknown' AS race
      FROM  TAGSdual(restricted)
    ) AS  UNION ALL
      SELECT '00010002' 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 st_union.*, roles.assigned_roles from st_union left join admin.get_user_roles_string roles on 1=1 CONTEXT ('formatted' = 'yes');

select 
    * 
from test.security_test 
context('impersonate_roles'='grp_ds_denodo-ir-read,cls_confidential')

select 
    * 
from test.security_test 
context('impersonate_roles'='rls_data_strategy,cls_restricted_dob');

select 
    * 
from test.security_test 
context('impersonate_roles' = ''rls_data_strategy,rls_iam,cls_restricted');

CONTAINS([users], USERNAME())

 ISMEMBEROF([group])