I have multiple tables like EMPLOYEE, EMPLOYEEMail and more tables. For reference purpose I'm putting some sample data and 2 tables
CREATE TABLE EMPLOYEE (
empId INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL,
phone bigint
);
INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'Sales',9001234567);
INSERT INTO EMPLOYEE VALUES (0002, 'Dave', 'Accounting',9000123456);
INSERT INTO EMPLOYEE VALUES (0003, 'Ava', 'Sales',9000012345);
CREATE TABLE EMPLOYEEMail (
empId INTEGER PRIMARY KEY,
MailID TEXT NOT NULL,
AlternateMail TEXT NOT NULL
);
INSERT INTO EMPLOYEEMail VALUES (0001, 'hello123@gmail.com','hi123@gmail.com');
INSERT INTO EMPLOYEEMail VALUES (0002, 'good123@gmail.com','bye123@gmail.com');
INSERT INTO EMPLOYEEMail VALUES (0003, 'super123@gmail.com','fast123@gmail.com');
Query :
SELECT CONCAT(SUBSTR(phone, 1, 6), REPEAT('*', CHAR_LENGTH(phone) - 6)) AS masked_phone
FROM `EMPLOYEE`;
select
CONCAT(LEFT(UUID(), 8), '@', SUBSTRING_INDEX(`MailID`, '@', -1)) as phone,
CONCAT(LEFT(UUID(), 8), '@', SUBSTRING_INDEX(`AlternateMail`, '@', -1)) as AlternateMail
from EMPLOYEEMail;
Output:
masked_phone
900123****
900012****
900001****
phone AlternateMail
d8883d67@gmail.com d8883d92@gmail.com
d8883dd0@gmail.com d8883dde@gmail.com
d8883df3@gmail.com d8883dff@gmail.com
It is working fine up to select query But I need to mask multiple columns all will come in Comma separated values like this
(MailID ,AlternateMail,Phone,SSN,BankAccount,Addrress)
I'm looking for a loop statement where I will compare these values with Information_schema.columns table which ever the column matches the with given values .
I'm trying to loop those into Dynamic select statement with @column and @tablename and load the masked values into temp table. Please suggest me on this.
I think substring is prettier than masking email addresses and this way sensitive data will remain secret.
SELECT CONCAT("****", SUBSTRING(`EMPLOYEEMail`.`MailID`, LOCATE('@', `EMPLOYEEMail`.`MailID`) - 4 )) AS MailID,
CONCAT("****", SUBSTRING(`EMPLOYEEMail`.`AlternateMail`, LOCATE('@', `EMPLOYEEMail`.`AlternateMail`) - 4 )) AS AlternateMail,
INSERT(`EMPLOYEE`.`phone`,7,10,"****") as masked_phone
FROM EMPLOYEE
JOIN EMPLOYEEMail ON EMPLOYEE.empId=EMPLOYEEMail.empId;
With your solution (fully masked the username from the email addresses) the query becomes meaningless because nothing refers to the original data
Why don't you use the concept of View to achieve this?. One of the benefits of Views is to hide specific information from the original table or visualize it in another way, without touching the original data.
In your case, you just need to create a view with the query above to hide the specific part of the phone number, email...etc.
CREATE VIEW employee_info_encrypted AS
SELECT CONCAT(SUBSTR(phone, 1, 6), REPEAT('*', CHAR_LENGTH(phone) - 6)) AS masked_phone,
CONCAT(LEFT(UUID(), 8), '@', SUBSTRING_INDEX(`MailID`, '@', -1)) as phone,
CONCAT(LEFT(UUID(), 8), '@', SUBSTRING_INDEX(`AlternateMail`, '@', -1)) as AlternateMail
FROM `EMPLOYEE`
inner join EMPLOYEEMail on EMPLOYEEMail.empId = EMPLOYEE.empId;
Then, by running the below query you will get the masked info:
select * from employee_info_encrypted;
Whenever you want to add a new column for example to the response, you just change the View code, and it will change the response based on the new conditions.