vvgsrk’s gists

vvgsrk / create_masking_policies_for_customer.sql

Last active July 7, 2024 09:29

	USE ROLE MASKING_ADMIN;

	CREATE OR REPLACE MASKING POLICY dp_governance.masking_policies.string_mask_customer AS (val STRING) RETURNS STRING ->
	CASE
	WHEN EXISTS (SELECT 1 FROM TABLE(SPLIT_TO_TABLE(SYSTEM$GET_TAG('dp_governance.tags.customer_sensitive', dp_governance.tags.get_current_user(), 'user'), ',')) WHERE value = 'customer-survey') and SYSTEM$GET_TAG_ON_CURRENT_COLUMN('dp_governance.tags.customer_sensitive') = 'customer-survey' THEN val
	WHEN EXISTS (SELECT 1 FROM TABLE(SPLIT_TO_TABLE(SYSTEM$GET_TAG('dp_governance.tags.customer_sensitive', dp_governance.tags.get_current_user(), 'user'), ',')) WHERE value = 'customer-loyalty') and SYSTEM$GET_TAG_ON_CURRENT_COLUMN('dp_governance.tags.customer_sensitive') = 'customer-loyalty' THEN val
	WHEN EXISTS (SELECT 1 FROM TABLE(SPLIT_TO_TABLE(SYSTEM$GET_TAG('dp_governance.tags.technical_user', dp_governance.tags.get_current_user(), 'user'), ',')) WHERE value = 'ServiceAccount') THEN val
	ELSE '*MASKED*'
	END
	COMMENT = 'String masking policy for c

vvgsrk / create_masking_policies_for_employee.sql

Last active July 7, 2024 09:31

	USE ROLE MASKING_ADMIN;

	CREATE OR REPLACE MASKING POLICY dp_governance.masking_policies.string_mask_employee AS (val STRING) RETURNS STRING ->
	CASE
	WHEN EXISTS (SELECT 1 FROM TABLE(SPLIT_TO_TABLE(SYSTEM$GET_TAG('dp_governance.tags.employee_sensitive', dp_governance.tags.get_current_user(), 'user'), ',')) WHERE value = 'compensation') and SYSTEM$GET_TAG_ON_CURRENT_COLUMN('dp_governance.tags.employee_sensitive') = 'compensation' THEN val
	WHEN EXISTS (SELECT 1 FROM TABLE(SPLIT_TO_TABLE(SYSTEM$GET_TAG('dp_governance.tags.employee_sensitive', dp_governance.tags.get_current_user(), 'user'), ',')) WHERE value = 'payroll') and SYSTEM$GET_TAG_ON_CURRENT_COLUMN('dp_governance.tags.employee_sensitive') = 'payroll' THEN val
	WHEN EXISTS (SELECT 1 FROM TABLE(SPLIT_TO_TABLE(SYSTEM$GET_TAG('dp_governance.tags.technical_user', dp_governance.tags.get_current_user(), 'user'), ',')) WHERE value = 'ServiceAccount') THEN val
	ELSE '*MASKED*'
	END
	COMMENT = 'String masking policy for employee experience sensi

vvgsrk / attach_masking_policies_to_tag_customer.sql

Last active July 7, 2024 09:37

	USE ROLE TAG_ADMIN;

	ALTER TAG dp_governance.tags.customer_sensitive
	SET MASKING POLICY dp_governance.masking_policies.string_mask_customer,
	MASKING POLICY dp_governance.masking_policies.timestamp_mask_customer,
	MASKING POLICY dp_governance.masking_policies.date_mask_customer
	;

vvgsrk / attach_masking_policie_to_tag_employee.sql

Last active July 7, 2024 09:35

	USE ROLE TAG_ADMIN;

	ALTER TAG dp_governance.tags.employee_sensitive
	SET MASKING POLICY dp_governance.masking_policies.string_mask_employee,
	MASKING POLICY dp_governance.masking_policies.float_mask_employee,
	MASKING POLICY dp_governance.masking_policies.timestamp_mask_employee,
	MASKING POLICY dp_governance.masking_policies.date_mask_employee,
	MASKING POLICY dp_governance.masking_policies.number_mask_employee
	;

vvgsrk / unattach_masking_policie_from_tag_customer.sql

Last active July 7, 2024 09:39

	USE ROLE tag_admin;

	ALTER TAG dp_governance.tags.customer_sensitive
	UNSET MASKING POLICY dp_governance.masking_policies.string_mask_customer,
	MASKING POLICY dp_governance.masking_policies.timestamp_mask_customer,
	MASKING POLICY dp_governance.masking_policies.date_mask_customer
	;

vvgsrk / unattach_masking_policie_from_tag_employee.sql

Last active July 7, 2024 09:40

	USE ROLE TAG_ADMIN;

	ALTER TAG dp_governance.tags.employee_sensitive
	UNSET MASKING POLICY dp_governance.masking_policies.string_mask_employee,
	MASKING POLICY dp_governance.masking_policies.float_mask_employee,
	MASKING POLICY dp_governance.masking_policies.timestamp_mask_employee,
	MASKING POLICY dp_governance.masking_policies.date_mask_employee,
	MASKING POLICY dp_governance.masking_policies.number_mask_employee
	;

vvgsrk / dp_governance.sql

Last active July 7, 2024 08:56

	USE ROLE SYSADMIN;

	CREATE DTABASE dp_governance;

	CREATE SCHEMA dp_governance.tags;
	CREATE SCHEMA dp_governance.masking_policies;

vvgsrk / asign_tags_to_users_customer.sql

Created July 7, 2024 09:53

	USE ROLE USERADMIN;

	-- Robert
	ALTER USER robert SET TAG dp_governance.tags.customer_sensitive = 'customer-survey';

	-- Jasper
	ALTER USER jasper SET TAG dp_governance.tags.customer_sensitive = 'customer-loyalty';

	-- Rowan
	ALTER USER rowan SET TAG dp_governance.tags.customer_sensitive = 'customer-survey,customer-loyalty';

vvgsrk / asign_tags_to_users_employee.sql

Last active July 7, 2024 09:58

	USE ROLE USERADMIN;

	-- Jamie
	ALTER USER jamie SET TAG dp_governance.tags.employee_sensitive = 'compensation';

	-- Avery
	ALTER USER avery SET TAG dp_governance.tags.employee_sensitive = 'payroll';

	-- Blake
	ALTER USER blake SET TAG dp_governance.tags.employee_sensitive = 'compensation,payroll';

vvgsrk / un_assign_tags_from_users_customer.sql

Created July 10, 2024 17:28

	USE ROLE USERADMIN;

	-- Robert
	ALTER USER robert UNSET TAG dp_governance.tags.customer_sensitive;

	-- Jasper
	ALTER USER jasper UNSET TAG dp_governance.tags.customer_sensitive;

	-- Rowan
	ALTER USER rowan UNSET TAG dp_governance.tags.customer_sensitive;

Venkata Gowri Sai Rakesh Kumar Varanasi vvgsrk