daanalytics

-- Create a Dynamic Masking Policy
CREATE OR REPLACE MASKING POLICY email_mask AS (val string) returns string ->
  CASE
    WHEN current_role() IN ('TASTY_ADMIN') 
        THEN VAL
    WHEN current_role() IN ('TASTY_TEST_ROLE') 
        THEN regexp_replace(val,'.+\@','*****@') -- leave email domain visible
    ELSE '*********'
  END
  ;

daanalytics

ALTER TABLE frostbyte_tasty_bytes.raw_customer.customer_loyalty
    ADD ROW ACCESS POLICY frostbyte_tasty_bytes.data_governance.customer_loyalty_city_row_policy ON (city);

daanalytics

CREATE OR REPLACE ROW ACCESS POLICY  customer_loyalty_city_row_policy 
as (city VARCHAR) RETURNS BOOLEAN ->
       CURRENT_ROLE() IN (<ROLE_NAME1>,<ROLE_NAME2>) -- e.g. 'TASTY_ADMIN','TASTY_DEV' 
        OR EXISTS 
            ( SELECT rp.role_name 
              FROM   frostbyte_tasty_bytes.data_governance.customer_loyalty_city_policy_mapping rp
              WHERE  rp.role_name = CURRENT_ROLE()
                AND  rp.city_value = city
            );

daanalytics

-- INSERT INTO customer_loyalty_city_policy_mapping 
-- VALUES ('<CITY_NAME>', '<ROLE_NAME>')
--;
--

INSERT INTO customer_loyalty_city_policy_mapping 
VALUES ('Cape Town', 'TASTY_TEST_ROLE')
;

INSERT INTO customer_loyalty_city_policy_mapping 

daanalytics

CREATE TABLE customer_loyalty_city_policy_mapping 
(city_value STRING, role_name STRING)
;

daanalytics

SELECT *
FROM   snowflake.account_usage.object_dependencies
WHERE  referencing_object_name = 'CUSTOMER_LOYALTY_V' 
AND    referenced_object_domain = 'TABLE'
;

daanalytics

SELECT 
    tag_database,
    tag_schema,
    tag_name,
    column_name,
    tag_value 
FROM TABLE(frostbyte_tasty_bytes.information_schema.tag_references_all_columns
    ('frostbyte_tasty_bytes.raw_customer.customer_loyalty','table'));

daanalytics

- Apply Tags
ALTER TABLE frostbyte_tasty_bytes.raw_customer.customer_loyalty 
    MODIFY COLUMN first_name 
        SET TAG frostbyte_tasty_bytes.raw_customer.pii_name_tag = 'First Name'
;

daanalytics

-- verify allowed values

SELECT system$get_tag_allowed_values('frostbyte_tasty_bytes.raw_customer.pii_name_tag') as tag_allowed_values
;

SELECT GET_DDL('tag', 'frostbyte_tasty_bytes.raw_customer.pii_name_tag') as tag_ddl
;

daanalytics

-- pii_name_tag
CREATE OR REPLACE TAG frostbyte_tasty_bytes.raw_customer.pii_name_tag
    ALLOWED_VALUES 'First Name', 'Last Name'
    COMMENT = 'PII Tag for Name Columns'
;