Generate anonymised database with MasKING - 2023.09.21 Euruko Unconference Talk

Generate anonymised database with MasKING - 2023.09.21 Euruko Unconference Talk

video (EuRuKo 2023): https://youtu.be/oml7dcDo_jo
video (Ruby Unconf 2024) :https://www.youtube.com/watch?v=qgHm6EFm-Eg

repo: https://github.com/kibitan/masking
presentation slide: https://docs.google.com/presentation/d/106kyWEeJ1G2reMKXx2ytKbq6BlvdXV1HZnIrQW442fA

Transcript

1. Generate anonymised database with MasKING Chikahiro Tokoro Euruko 2023 at Vilnius Ruby Unconf 2024 at Hamburg
2. 1. What is the anonymised database? 2. How it was build? • Use cases • Anonymising Strategies • Usage and Demo: MasKING 🥡 You can build your anonymised database • Design concepts • Implementation detail • The journey for production 🥡 You can make your library in quality Agenda:
3. Why do we want anonymised database?
4. One day, during the release….
5. One day, during the release…. the main table was locked during database migration😱
6. staging (QA) production local (development) —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- 0.2 sec 3 sec 1 hour the postmortem: check the delivery process
7. If with similar dataset with production... staging (QA) production local (development) —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- 1 hour
8. But, we should not use the production dataset Let’s use anonymised dataset!
9. Use cases: • simulate database migration local (development) —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- 1 hour
10. Use cases: • SQL performance optimization accurate execution plan # Time: 2023-09-13T15:45:12.987654Z # User@Host: web_app[web_db] @ 10.0.0.2 # Query_time: 25.987654 Lock_time: 2.345678 Rows_sent: 50 Rows_examined: 5000 SET timestamp=1234567890; SELECT users.id, users.username, COUNT(posts.id) AS post_count, SUM(posts.likes) AS total_likes FROM users LEFT JOIN posts ON users.id = posts.user_id WHERE users.created_at >= '2023-01-01' GROUP BY users.id HAVING post_count >= 5 ORDER BY total_likes DESC LIMIT 10; slow query log
11. Use cases: • make ease to reproduce and fix bugs unexpected values missing related record duplicated records
12. Use cases: • better feature preview • stress test • analyse / Business Intelligence data source And… your idea here!
13. https://www.snaplet.dev/ Founded on 2020
14. https://www.snaplet.dev/ Founded on 2020
15. How to implement anonymisation process? 1. copy database and “UPDATE” records production production (copy) UPDATE users SET name = 'User1', email = 'user1@test.com', .... (most common way)
16. How to implement anonymisation process? 2. setup replica and database trigger production production (replica) CREATE TRIGGER after_user_insert AFTER INSERT ON users FOR EACH ROW BEGIN UPDATE users SET username = CONCAT('User', NEW.user_id) WHERE user_id = NEW.user_id; ...
17. How to implement anonymisation process? 3. proxy connection and interrupt SQL on the fly production INSERT INTO users (username, email, ...) VALUES ('JohnDoe', 'johndoe@domain.com' , ...); production (copy) Connection Proxy INSERT INTO users (username, email, ...) VALUES ('JohnDoe', 'johndoe@domain.com' , ...); INSERT INTO users (username, email, ...) VALUES ('User1', 'user1@test.com ', ...);
18. How to implement anonymisation process? 4. read database and create anonymised database production dump anonymised dump production (anonymised) database client (second common way)
19. How to implement anonymisation process? 4. data leak risk Medium Low Low effort Medium High Medium Low config High Medium Low Low time gap realtime realtime Medium Long performance regression No Yes No No 2. DB trigger 3. DB proxy read & create 1. copy & UPDATE High
20. How to implement anonymisation process? 4. data leak risk Medium Low Low effort Medium High Medium Low config High Medium Low Low time gap realtime realtime Medium Long performance regression No Yes No No 2. DB trigger 3. DB proxy read & create 1. copy & UPDATE High
21. https://github.com/kibitan/masking
22. Daily Download ranked in Top 4% 🚀 Total Download ranked in Top 20% 🚀 (2024.07)
23. Usage anonymized+1@example.com anonymized+2@example.com anonymized+3@example.com … users: email: anonymized+%{n}@example.com
24. Usage anonymized+1@example.com anonymized+2@example.com anonymized+3@example.com … anonymized+1@example.com NULL anonymized+3@example.com … users: email: anonymized+%{n}@example.com users: email?: anonymized+%{n}@example.com
25. Usage $ mysql --complete-insert DB_NAME | masking | mysql ANONYMIZED_DB_NAME
26. Generate anonymised database with MasKING - Demo - YouTube Demo
27. Generate anonymised database with MasKING - Demo - YouTube Demo masking.yml
28. Generate anonymised database with MasKING - Demo - YouTube Demo
29. Generate anonymised database with MasKING - Demo - YouTube Demo
30. Design Concepts
31. Design Concept: KISS ~ Keep it simple, stupid! ~ “Do one thing, and do it well” Unix philosophy ❌ Database connection ❌ File handling ✅ Standard input/output
32. Design Concept: No external dependency No gems dependencies, use only standard libraries
33. Design Concept: Quality
34. Design Concept: Quality - metrics
35. Design Concept: Quality - Acceptance test setup test assertion docker-compose.yml acceptance/run_test.sh
36. Design Concept: Quality - Acceptance test on CI
37. How to implement?
38. users: name: Anonymized Name email: anonymized+%{n}@example.com masking.yml dump.sql anonymised_dump.sql masking
39. Domains: uncertainties Mapping configuration ? Parse/Rebuild SQL
40. Domains: uncertainties Mapping configuration Parse/Rebuild SQL Introduction to Test-Driven Development (TDD) with Classic TDD Example | Khalil Stemmler
41. ① Outside-in Mapping configuration stdin ① Outside-in ② Inside-out Parse/Rebuild SQL ③ Inside-out Domains: finding architecture/models Output Line Input Line stdout Processor ⑤ Outside-in ⑤ Outside-in ④ Inside-out
42. How to parse dump?
43. sample_dump.sql setup database define table schema insert data
44. https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
45. setup database define table schema insert data sample_dump_with_complete-insert_option.sql
46. setup database define table schema insert data sample_dump_with_complete-insert_option.sql
47. How to parse SQL?
49. https://github.com/kibitan/masking/blob/main/lib/masking/insert_statement.rb dynamically make regular expression object ?<coulmns_section> ?<table> ?<values_section> VALUE_REGEXP x 3
50. https://github.com/kibitan/masking/blob/main/lib/masking/insert_statement.rb dynamically make regular expression object ?<coulmns_section> ?<table> ?<values_section> VALUE_REGEXP x 3
51. $ mysqldump --complete-insert production_db | masking > anonymised_dump.sql /Users/kibitan/repo/github.com/kibitan/masking/lib/masking/insert_statement.rb:15:in `block in initialize': Masking::Error::InsertStatementParseError (Masking::Error::InsertStatementParseError) from <internal:kernel>:90:in `tap' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking/insert_statement.rb:14:in `initialize' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking/data_mask_processor.rb:12:in `new' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking/data_mask_processor.rb:12:in `initialize' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking/sql_dump_line.rb:13:in `new' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking/sql_dump_line.rb:13:in `mask' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking.rb:23:in `block in run' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking.rb:22:in `each_line' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking.rb:22:in `run' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking.rb:10:in `run' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking/cli.rb:17:in `run' from exe/masking:10:in `<main>' let’s try with production data!
52. $ mysqldump --complete-insert production_db | masking > anonymised_dump.sql /Users/kibitan/repo/github.com/kibitan/masking/lib/masking/insert_statement.rb:15:in `block in initialize': Masking::Error::InsertStatementParseError (Masking::Error::InsertStatementParseError) from <internal:kernel>:90:in `tap' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking/insert_statement.rb:14:in `initialize' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking/data_mask_processor.rb:12:in `new' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking/data_mask_processor.rb:12:in `initialize' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking/sql_dump_line.rb:13:in `new' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking/sql_dump_line.rb:13:in `mask' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking.rb:23:in `block in run' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking.rb:22:in `each_line' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking.rb:22:in `run' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking.rb:10:in `run' from /Users/kibitan/repo/github.com/kibitan/masking/lib/masking/cli.rb:17:in `run' from exe/masking:10:in `<main>' let’s try with production data!
53. the journey to ready for production data https://github.com/kibitan/masking/commit/792e73b7d316fac1fa6694d74b6adcfee19dccc4
54. https://github.com/kibitan/masking/commit/8ca7fdad2c52d3af5e6c56faadc08e8768500108 the journey to ready for production data
55. https://github.com/kibitan/masking/commit/04515ec0f9c8a5457c10b8224b488b90c90c6aa2 the journey to ready for production data
56. https://github.com/kibitan/masking/pull/3 the journey to ready for production data
57. $ mysqldump --complete-insert production_db | masking > anonymised_dump.sql $ mysql anonymised_db < anonymised_dump.sql ERROR 1064 (42000) at line 547659: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'smelati’),' at line 547659 the journey to ready for production data
58. (‘ab’,1),(’cd’,2),(’ef’,3); the journey to ready for production data
59. (‘ab’,1),(’cd’,2),(’ef’,3); ‘ab’,1,’cd’,2,’ef’,3; same with CSV!!! the journey to ready for production data
60. https://www.clear-code.com/blog/2018/12/25.html the journey to ready for production data
61. 2.even? 3.even? 4.even? concat with next 4.even? the journey to ready for production data
62. https://github.com/kibitan/masking/pull/5 the journey to ready for production data
63. https://github.com/kibitan/masking/pull/5 the journey to ready for production data
65. what’s next? • Supporting hashed anonymizing • Parse table schema • Strict mode ◦ type validation for config ◦ working without “--complete-insert” option • Performance optimization ◦ Custom command
66. what’s next?: for PostgreSQL https://github.com/kibitan/pgmasking
67. Thank you! Any feedback is welcome! https://github.com/kibitan/masking Chikahiro x/twitter: @chikahirotokoro https://chikahirotokoro.com