@TejeshS You can alternatively use, mask function instead of hardcoding with value 'Masked'.
CREATE OR REPLACE VIEW masked_employees AS
SELECT
Name,
Department,
CASE
WHEN current_user() IN ('ab***@gmail.com', 'xy***@gmail.com') THEN Salary
ELSE mask(cast(Salary as string))
END AS Salary
FROM employees;
Else create custom functions like code, which is more dynamic for maintenance purposes.
-- First create a group for users who can see unmasked data
CREATE GROUP IF NOT EXISTS sensitive_data_viewers;
-- Create masking functions with role-based logic
CREATE OR REPLACE FUNCTION mask_functions.conditional_mask_email(email STRING)
RETURNS STRING
RETURN CASE
WHEN IS_MEMBER('sensitive_data_viewers') THEN email
WHEN email IS NULL THEN NULL
ELSE CONCAT(
LEFT(SPLIT(email, '@')[0], 2),
REPEAT('*', LENGTH(SPLIT(email, '@')[0]) - 2),
'@',
SPLIT(email, '@')[1]
)
END;
-- Create view with conditional masking
CREATE OR REPLACE VIEW customer_data_masked AS
SELECT
customer_id,
full_name,
mask_functions.conditional_mask_email(email) as email,
phone_number,
-- Non-sensitive fields shown to everyone
city,
country,
registration_date
FROM customer_table;
-- Grant access to the view for all users
GRANT SELECT ON VIEW customer_data_masked TO role_analyst;
GRANT SELECT ON VIEW customer_data_masked TO role_developer;
-- Add specific users to the sensitive data viewers group
ALTER GROUP sensitive_data_viewers ADD USER user1@company.com;
ALTER GROUP sensitive_data_viewers ADD USER user2@company.com;