How to mask dynamically specific columns from multiple tables in MYSQL

user_943978 0
Viewed 185

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.

mysql sql
2 Answers

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

user_60874220

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.

user_48766390
Related