How to encrypt Personal data for GDPR in MySQL
Before going towards how to encrypt Personal data for GDPR and knowing how to encrypt and decrypt MySQL data using AES_ENCRYPT() AND AES_DECRYPT() functions. Let me explain whether is it required to encrypt user data for GDPR compliance or not.
GDPR is “General Data Protection Regulation” which states so many set of rules for user data protection. Any website should follow these rules if it uses or stores end user data in any form. This is the reason why most of the websites need to display a cookie notice/policy. In accordance with using website functionality user will have to accept these policies. They also present their cookie and privacy policies on how they using the end user data.
Now talking about encryption of user data. As per my study it may be recommendation to encrypt user data for security and integrity reasons.
I have read many article along with some clauses of GDPR policies and found that “According to GDPR it is recommended to encrypt user’s personal data using a key“. I could not found anywhere that websites must have to encrypt the end user data to store it. So…
Encrypting personal data STRICTLY SPEAKING is not mandatory.
Internet
Well many website would like to follow the recommended guidelines. Each company do not have a database administrator. For those who are very small scale ones, it is very difficult to main such a scenario.
I have found a workaround by which there will be no data loss and encryption will be easily performed along with data retention.
The method I am using to encrypt and decrypt MySQL data is a by MySQL functions AES_ENCRYPT() AND AES_DECRYPT(). Anybody can use this method very efficiently for encryption decryption for GDPR.
What is AES_ENCRYPT() function? Read More
What is AES_DECRYPT() function? Read More
How to create encrypted data from the existing one using AES_ENCRYPT
Now i will discuss the main process of encrypting user data and create new encrypted data from the existing data. There are two options. You can chose any of them as per your comfort and requirement.
We are going to discuss with example in reference with the table ‘students‘.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TABLE `students` ( `student_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `student_name` varchar(255) NOT NULL, `student_email` varchar(100) DEFAULT NULL, `student_dob` date DEFAULT NULL, `student_subject` varchar(255) NOT NULL, `student_subject2` varchar(100) DEFAULT NULL, `student_subject3` varchar(100) DEFAULT NULL ); INSERT INTO `students` (`student_id`, `student_name`, `student_email`, `student_dob`, `student_subject`, `student_subject2`, `student_subject3`) VALUES (1, 'John', 'john@techbriefers.com', '2000-05-01', 'Maths', 'English', 'Commerce'), (2, 'Suzan', 'suzan@techbriefers.com', '2000-02-01', 'English', 'Arts', 'Music'), (3, 'Nikki', 'nikki@techbriefers.com', '1999-12-11', 'Maths', 'Music', 'Commerce'), (4, 'Mathew', 'mathew@techbriefers.com', '2000-03-03', 'Maths', 'English', 'Commerce'), (5, 'Nicolas', 'nic@techbriefers.com', '1999-09-11', 'Maths', 'English', 'Commerce'); |
Before opting any method please do take a backup of your data or at least backup your table.
Method One:
Suitable for the data where table has very few columns that need to be encrypted. If you have many or all columns for encryption it is better to chose the second method. Let see the method One:
- Create columns with a similar name (or other) each for the column to be encrypted. Like I have to encrypt columns student_name, student_email and student_dob as these three column contain user’s personal data.
|
1 |
ALTER TABLE `students` ADD `student_name_e` VARBINARY(100) NOT NULL AFTER `student_dob`, ADD `student_email_e` VARBINARY(100) NOT NULL AFTER `student_name_e`, ADD `student_dob_e` VARBINARY(100) NOT NULL AFTER `student_email_e`; |
Now Copy Original Column data to these new created columns in encrypted form.
|
1 |
UPDATE `students` SET `student_name_e` = AES_ENCRYPT(`student_name`, 'mykey'), `student_email_e` = AES_ENCRYPT(`student_email`, 'mykey'), `student_dob_e` = AES_ENCRYPT(`student_dob`, 'mykey') WHERE 1 |
Now rename the encrypted columns to original columns and before that rename original column to some other name or drop those columns
To rename original columns to some other (say suffixing ‘_old’) and encryption columns renaming to original ones..
|
1 |
ALTER TABLE `students` CHANGE `student_name` `student_name_old` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, CHANGE `student_email` `student_email_old` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `student_dob` `student_dob_old` DATE NULL DEFAULT NULL, CHANGE `student_name_e` `student_name` VARBINARY(100) NOT NULL, CHANGE `student_email_e` `student_email` VARBINARY(100) NOT NULL, CHANGE `student_dob_e` `student_dob` VARBINARY(100) NOT NULL; |
If you want to drop the old columns use :
|
1 2 3 4 |
ALTER TABLE `students` DROP `student_name_old`, DROP `student_email_old`, DROP `student_dob_old`; |
Now see the final table and data will now be like:
Let’s check if the Encryption is done correctly and the data is integrated. The decrypted result of encrypted columns and non encrypted old data is fetched by below query.
|
1 |
SELECT `student_name_old`, `student_email_old`, `student_dob_old`, AES_DECRYPT(`student_name`,'mykey') AS decrypted_name, AES_DECRYPT(`student_email`,'mykey') AS decrypted_email, AES_DECRYPT(`student_dob`,'mykey') AS decrypted_dob FROM `testing`.`students` |
Nice article. You mentioned a second way for more complex tables, but I dont see the Method 2 …?
Im interested to know what route you suggest.
Many Thanks
Nice articel. But could you point me to method 2.
I dont see it here…
That’s good for complying the law but where to save the key and how to refresh the key will need to be considered
What is the second method you mentioned for tables with many columns?