Practical Data Masking in Percona Server for MySQL 8.4

Byneoclova

percona

Data masking 은 Email, 신용카드 번호, 직책 등과 같은 민감한 필드를 숨기면서도 보고서 작성, 지원, 테스트 등에 사용할 수 있도록 데이터를 현실적으로 유지할 수 있게 해줍니다. 이는 외부 기관과 협업하면서 개발 목적으로 데이터를 공유해야 할 때 특히 유용합니다. 또한 데이터 보호와 고객의 개인정보 안전을 보장해야 합니다. 마지막으로, 데이터 마스킹은 GDPR, HIPAA, CCPA 와 같은 개인정보 보호 규정을 준수하기 위해 반드시 필요합니다.

Percona Server for MySQL 8.4 에는 SQL 에서 직접 사용할 수 있는 data masking 및 synthetic-data(합성 데이터) 생성 기능이 포함된 data masking 컴포넌트가 내장되어 있습니다. 아래에서는 간단하고 실용적인 워크플로를 보여줍니다. 컴포넌트 설치 및 활성화하고, 샘플 테이블을 생성한 다음, 제한된 사용자가 masking 되어있는 데이터를 볼 수 있도록 View 또는 stored procedure 를 만들며, 마지막으로 masking 되어있는 데이터를 dump/export 과정을 설명합니다.

테스트에는 Percona Server for MySQL 8.4 를 사용했습니다.

예제에서는 ‘mask_inner()’, ‘mask_pan()’, ‘gen_rnd_email()’ 같은 Percona masking function 와 dictionary helper 를 사용합니다. 이들은 모두 Percona 의 data masking 기능에 포함된 구성 요소입니다.

 

Install the data masking component

다음은 해당 컴포넌트를 설치하기 위한 최소 설치 순서입니다. 먼저 내부 테이블인 ‘masking_dictionaries’ 를 생성한 후 컴포넌트를 설치합니다. 또한 일부 dictionary function 은 ‘MASKING_DICTIONARIES_ADMIN’ 권한이 필요하다는 점에 유의하세요. 예시는 다음과 같습니다:

 

-- create internal dictionary table (run as a privileged admin)
CREATE TABLE IF NOT EXISTS mysql.masking_dictionaries (
Dictionary VARCHAR(256) NOT NULL,
Term VARCHAR(256) NOT NULL,
UNIQUE INDEX dictionary_term_idx (Dictionary, Term)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- install the component
INSTALL COMPONENT 'file://component_masking_functions';

-- grant dictionary-admin if you plan to add/remove dictionary terms
GRANT MASKING_DICTIONARIES_ADMIN ON *.* TO 'root'@'localhost';

 

MySQL 의 모범 사례에 따라 많은 관리자는 ‘root’ 사용자를 비활성화합니다. 과거에는 이로 인해 해당 function 들이 작동하지 않는 문제가 발생했습니다. 현재는 서버가 dictionary 쿼리를 실행하기 위해 내장 사용자 ‘mysql.session’ 을 사용하도록 변경되었습니다.

이 기능이 정상적으로 동작하려면 ‘mysql.session’ 사용자에게 ‘masking_dictionaries’ 테이블에 대한 ‘SELECT’, ‘INSERT’, ‘UPDATE’, ‘DELETE’ 권한을 부여해야 합니다.

 

-- grant dictionary-admin if you plan to add/remove dictionary terms
GRANT SELECT, INSERT, UPDATE, DELETE ON mysql.masking_dictionaries TO 'mysql.session'@'localhost';

 

 

Example data: Create a table and insert some rows
 

CREATE DATABASE example;
USE example;

CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
full_name VARCHAR(200),
email VARCHAR(255),
credit_card VARCHAR(32),
job_title VARCHAR(100)
);

INSERT INTO customers (full_name, email, credit_card, job_title) VALUES
('Alice Smith', 'alice@mydomain1.com', '4111111111111111', 'Senior Engineer'),
('Bob Rossi', 'bob_rossi@mydomain2.com', '5500000000001234', 'Sales Manager'),
('Caroline Yu', 'caroline@mydomain3.com', '3400000000009876', 'Product Designer');

 

 

Simple masking examples

이제 일반적인 필드를 masking 하는 방법을 살펴보겠습니다.

Percona Server for MySQL 은 다양한 function 을 제공합니다. 그중 유용한 몇 가지는 다음과 같습니다:

  • mask_inner(str, margin_left, margin_right [, mask_char]) — 문자열의 가운데 부분을 masking 합니다.
  • mask_outer(str, left_mask, right_mask [, mask_char]) — 문자열의 양쪽 끝을 masking 합니다.
  • mask_pan(str) / mask_pan_relaxed(str) — 주요 계좌번호(신용카드 번호 등)를 masking 합니다.
  • gen_rnd_email() — 무작위 Email 주소를 생성합니다. (synthetic 내보내기에 유용)
  • dictionary functions + masking_dictionary_term_add() — 생성된 용어를 위한 dictionary 을 직접 제공할 수 있습니다.

이 외에도 다양한 function 이 있습니다. 전체 목록은 아래 문서에서 확인할 수 있습니다:

https://docs.percona.com/percona-server/8.4/data-masking-function-list.html

 

 

Example: Mask email but keep the domain

‘a****@example.com’ 처럼 보이도록 만들고 싶다면, ‘substring’ 함수와 ‘mask_inner()’ function 을 함께 사용하면 됩니다.

 

-- View expression to keep domain and mask the local part except first/last char

SELECT
  id,
  full_name,
  CONCAT(
   mask_inner(SUBSTRING_INDEX(email, '@', 1), 1, 1, 'X'),
    '@',
    SUBSTRING_INDEX(email, '@', -1)
  ) AS email_masked
FROM customers;
+----+-------------+-------------------------+
| id | full_name   | email_masked            |
+----+-------------+-------------------------+
|  1 | Alice Smith | aXXXe@mydomain1.com     |
|  2 | Bob Rossi   | bXXXXXXXi@mydomain2.com |
|  3 | Caroline Yu | cXXXXXXe@mydomain3.com  |
+----+-------------+-------------------------+

 

도메인을 유지할 필요가 없다면, 완전히 무작위 Email 주소를 생성하는 다음 function 을 사용할 수 있습니다: ‘gen_rnd_email([name_size, surname_size, domain]) ‘

 

-- Generate a random email address

SELECT
  id,
  full_name,
  gen_rnd_email(4, 5, 'mydomain.edu') AS email_masked
FROM customers;
+----+-------------+-------------------------+
| id | full_name   | email_masked            |
+----+-------------+-------------------------+
|  1 | Alice Smith | yqgy.cflrn@mydomain.edu |
|  2 | Bob Rossi   | betf.cetqp@mydomain.edu |
|  3 | Caroline Yu | qefm.jrgrw@mydomain.edu |
+----+-------------+-------------------------+

 

 

Example: Mask credit card (PAN), leaving the last four digits

Use mask_pan():

 

-- produces XXXXXXXXXXXX1111 style output (if input length valid)

SELECT id, mask_pan(credit_card) AS cc_masked FROM customers;
+----+------------------+
| id | cc_masked
+----+------------------+
|  1 | XXXXXXXXXXXX1111 |
|  2 | XXXXXXXXXXXX1234 |
|  3 | XXXXXXXXXXXX9876 |
+----+------------------+

 

 

Example: Mask job title with synthetic dictionary terms

Jobs 이라는 이름의 dictionary 를 생성한 후, gen_dictionary(‘jobs’) function 을 사용하여 데이터를 생성할 수 있습니다.

 

-- add a few dictionary terms (requires MASKING_DICTIONARIES_ADMIN grant)

SELECT masking_dictionary_term_add('jobs', 'Engineer');
SELECT masking_dictionary_term_add('jobs', 'Designer');
SELECT masking_dictionary_term_add('jobs', 'Account Manager');
SELECT masking_dictionary_term_add('jobs', 'Support Specialist');

-- let’s take a look what’s inside the dictionaries table

mysql> select * from mysql.masking_dictionaries;
+------------+--------------------+
| Dictionary | Term               |
+------------+--------------------+
| jobs       | Account Manager    |
| jobs       | Designer           |
| jobs       | Engineer           |
| jobs       | Support Specialist |
+------------+--------------------+

-- use gen_dictionary to produce random job titles from that dictionary
SELECT id, gen_dictionary('jobs') AS job_masked FROM customers;

 

같은 방식으로, 예를 들어 여러 종류의 berries 를 포함한 dictionary 을 정의할 수도 있습니다.

 

SELECT masking_dictionary_term_add('berries', 'Strawberry');

SELECT masking_dictionary_term_add('berries', 'Blueberry');

SELECT masking_dictionary_term_add('berries', 'Raspberry');

SELECT masking_dictionary_term_add('berries', 'Blackberry');

SELECT masking_dictionary_term_add('berries', 'Cranberry');

SELECT masking_dictionary_term_add('berries', 'Lingonberry');

 

원하는 dictionary 는 자유롭게 생성할 수 있습니다. 유일한 제한은 당신의 창의력입니다.

Percona Server for MySQL 은 dictionary 지원과 helper function 을 제공합니다.
버전 8.4.4 부터는 dictionary 테이블을 계속 조회하지 않도록 메모리 캐시가 추가되었습니다.
따라서 dictionary 테이블을 직접 수정한 경우, 캐시를 다시 동기화하려면 다음 명령을 실행해야 합니다.

 

SELECT masking_dictionaries_flush();

 

 

Create a view that shows masked data

사용자가 데이터베이스에 접근할 수 있지만, 기본 테이블에 대한 직접 접근 권한은 없는 경우가 흔합니다. 이럴 때는 View 를 생성하고, 사용자에게 View 에 대한 접근 권한만 부여할 수 있습니다.

View 는 낮은 권한의 사용자가 실제 테이블을 보호하면서 masking 된 결과를 조회할 수 있도록 하는 간단하고 안정적인 방법입니다.

 

USE example;

-- create a read-only user who should not see real data
CREATE USER 'masked_user'@'%' IDENTIFIED BY 'ChangeMe123!';

-- create a view that applies masking functions:
CREATE OR REPLACE VIEW v_customers_masked AS
SELECT
  id,
  full_name,
  -- mask the local part of email, keep domain
  CONCAT(
    mask_inner(SUBSTRING_INDEX(email, '@', 1), 1, 1, 'X'),
    '@',
    SUBSTRING_INDEX(email, '@', -1)
  ) AS email_masked,
  -- mask PAN (payment card number)
  mask_pan(credit_card) AS credit_card_masked,
  -- generate a dictionary-based fake job title (stable-ish per query)
  gen_dictionary('jobs') AS job_masked
FROM customers;

-- grant only SELECT on the view (no access to real table)
GRANT SELECT ON example.v_customers_masked TO 'masked_user'@'%';

-- (do NOT grant SELECT on example.customers !)

 

새로 생성한 masked_user 계정으로 데이터베이스에 다시 연결합니다.

 

# mysql -u masked_user -p'ChangeMe123!'

 

이제 생성한 View 와 기본 테이블을 대상으로 쿼리를 실행해 보겠습니다. 사용자는 masking 되거나 합성된 값만 볼 수 있습니다. 이 패턴을 사용하면 실제 테이블을 보호하면서도 하위 사용자가 쿼리와 보고서를 실행할 수 있습니다.

 

mysql> select * from example.v_customers_masked;

+----+-------------+-------------------------+--------------------+------------+
| id | full_name   | email_masked            | credit_card_masked | job_masked |
+----+-------------+-------------------------+--------------------+------------+
|  1 | Alice Smith | aXXXe@mydomain1.com     | XXXXXXXXXXXX1111   | Engineer   |
|  2 | Bob Rossi   | bXXXXXXXi@mydomain2.com | XXXXXXXXXXXX0004   | Designer   |
|  3 | Caroline Yu | cXXXXXXe@mydomain3.com  | XXXXXXXXXXX0009    | Designer   |
+----+-------------+-------------------------+--------------------+------------+
3 rows in set (0.01 sec)

mysql> select * from example.customers;
ERROR 1142 (42000): SELECT command denied to user 'masked_user'@'localhost' for table 'customers'

 

 

Can we use stored procedure for masking data?

물론 가능합니다. View 와 마찬가지로, 데이터를 masking 하기 위해 stored procedure 를 사용할 수도 있습니다. 이전과 동일한 masking 을 구현하고, 권한이 제한된 사용자에게는 stored procedure 에 대한 EXECUTE 권한만 부여하면 됩니다.

 

DELIMITER $$

CREATE PROCEDURE get_masked_customers()
BEGIN
    SELECT
    id,
    full_name,
    CONCAT(mask_inner(SUBSTRING_INDEX(email, '@', 1), 1, 1, 'X'),'@',SUBSTRING_INDEX(email, '@', -1)) AS email_masked,
    mask_pan(credit_card) AS credit_card_masked,
    gen_dictionary('jobs') AS job_masked

    FROM customers;

END$$

DELIMITER ;

-- provide EXECUTE grant to masked_user

GRANT EXECUTE ON PROCEDURE example.get_masked_customers TO 'masked_user'@'%';

 

masked_user 계정으로 데이터베이스에 다시 연결한 후, 해당 procedure 를 호출합니다.

 

# mysql -u masked_user -p'ChangeMe123!'

mysql> CALL example.get_masked_customers;

+----+-------------+-------------------------+--------------------+--------------------+
| id | full_name   | email_masked            | credit_card_masked | job_masked         |
+----+-------------+-------------------------+--------------------+--------------------+
|  1 | Alice Smith | aXXXe@mydomain1.com     | XXXXXXXXXXXX1111   | Support Specialist |
|  2 | Bob Rossi   | bXXXXXXXi@mydomain2.com | XXXXXXXXXXXX1234   | Designer           |
|  3 | Caroline Yu | cXXXXXXe@mydomain3.com  | XXXXXXXXXXXX9876   | Designer           |
+----+-------------+-------------------------+--------------------+--------------------+

 

View 를 사용할지 stored procedure 를 사용할지는 목표에 따라 다릅니다.

View 를 사용하면 사용자가 view 위에서 SELECT 문을 실행하며 필터 조건을 추가하거나 다른 테이블과 JOIN 할 수 있습니다. 반면 stored procedure 를 사용하면 사용자는 항상 고정된 결과, 전체 masking 테이블, 혹은 stored procedure 코드가 제공하는 결과만 받을 수 있습니다. 더 많은 유연성을 제공하려면, stored procedure 에 입력 매개변수를 추가하여 filter conditions, sorting, limiting the result 등을 구현할 수도 있습니다. Procedure 의 로직에 복잡한 처리를 넣는 것도 가능하지만, 결국 view 가 더 많은 유연성을 제공한다고 볼 수 있습니다.

 

 

Dump/export masked data

View 의 값을 dump 할 때 mysqldump 를 직접 사용할 수는 없습니다. 실제로 mysqldump 는 View 정의(CREATE VIEW 문)만 dump 할 수 있으며, View 의 데이터는 dump 되지 않습니다.

이 경우 올바른 방법은 View 에서 SELECT … INTO OUTFILE 을 사용하는 것입니다.

CSV 나 다른 파일 형식을 선호하는 경우:

 

-- run as masked_user (or a user that has FILE privilege and access)


SELECT * FROM example.v_customers_masked
INTO OUTFILE '/var/lib/mysql-files/customers_masked.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n';

 

secure_file_priv 설정과 파일 시스템 권한이 해당 경로에 쓰기를 허용하는지 확인하고, masked_user 에게 FILE 권한이 있는지 확인하세요. 권한이 없다면, 사용자에게 권한을 추가한 후 다시 시도합니다:

 

GRANT FILE ON *.* TO  'masked_user'@'%';

mysql> SHOW GLOBAL VARIABLES LIKE 'secure_file_priv';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

Check now the content of the file /var/lib/mysql-files/customers_masked.csv and you should see all data properly masked.

# cat /var/lib/mysql-files/customers_masked.csv
1,"Alice Smith","aXXXe@mydomain1.com","XXXXXXXXXXXX1111","Account Manager"
2,"Bob Rossi","bXXXXXXXi@mydomain2.com","XXXXXXXXXXXX0004","Account Manager"
3,"Caroline Yu","<a href="mailto:cXXXXXXe@mydomain3.com">cXXXXXXe@mydomain3.com</a>","XXXXXXXXXXX0009","Designer"

 

 

Use mysqldump with proxysql

솔직히 말하면, masking 된 데이터를 dump 할 때 mysqldump 를 사용하는 방법이 있습니다. MySQL 서버에서 직접 dump 하는 대신, mysqldump 를 ProxySQL 로 연결하면 ProxySQL 이 실시간으로 데이터 masking 을 수행할 수 있습니다.

ProxySQL 은 들어오는 쿼리를 매칭하여 수정되거나 다른 SQL 코드로 대체하도록 설정할 수 있습니다. 예를 들어, mysqldump 가 실행하는 일반 쿼리인 SELECT /*!40001 SQL_NO_CACHE */ * FROM tablename 을 우리가 필요한 masking function 를 적용한 커스텀 쿼리로 교체할 수 있습니다.

이 글의 목적은 ProxySQL 을 설명하는 것이 아니며, ProxySQL 의 작동 방식과 구성, 사용법을 이미 알고 있다고 가정합니다. 또한 시스템에 ProxySQL 이 이미 설치되어 있다고 가정합니다.

ProxySQL 에 대한 자세한 내용은 공식 문서를 참고하세요: http://www.proxysql.com

ProxySQL 설정에서는 간단히 customers 테이블의 mysqldump 쿼리를 실시간으로 재작성하는 쿼리 규칙을 생성할 수 있습니다.

 

-- create the query rule in the ProxySQL configuration to match and replace the mysqldump query
INSERT INTO mysql_query_rules (rule_id,active,schemaname,match_pattern,replace_pattern)
VALUES (1,1,'example',
'^SELECT /*!40001 SQL_NO_CACHE */ * FROM `customers`',
'SELECT id, full_name, CONCAT(mask_inner(SUBSTRING_INDEX(email, "@", 1), 1, 1, "X"),"@",SUBSTRING_INDEX(email, "@", -1)) AS email, mask_pan(credit_card) AS credit_card, gen_dictionary("jobs") AS job_title FROM customers'
);

LOAD QUERY RULES TO RUNTIME;
SAVE QUERY RULES TO DISK;

 

그런 다음, proxy 를 통해 연결하여 mysqldump 를 다음과 같이 실행할 수 있습니다:

 

mysqldump -uroot -pmyrootpw -h 127.0.0.1 -P 6033 example customers --skip-extended-insert > customers.sql

 

이제 dump 파일을 열어보면, INSERT 문이 masking 된 상태로 생성된 것을 확인할 수 있습니다.

 

--
-- Dumping data for table `customers`
--

LOCK TABLES `customers` WRITE;
/*!40000 ALTER TABLE `customers` DISABLE KEYS */;
INSERT INTO `customers` VALUES (1,'Alice Smith','<a href="mailto:aXXXe@mydomain1.com">aXXXe@mydomain1.com</a>','XXXXXXXXXXXX1111','Designer');
INSERT INTO `customers` VALUES (2,'Bob Rossi','bXXXXXXXi@mydomain2.com','XXXXXXXXXXXX1234','Support Specialist');
INSERT INTO `customers` VALUES (3,'Caroline Yu','cXXXXXXe@mydomain3.com','XXXXXXXXXXXX9876','Account Manager');

/*!40000 ALTER TABLE `customers` ENABLE KEYS */;
UNLOCK TABLES;

 

이 경우, 테이블에서 반환된 필드 이름을 변경하지 않았기 때문에 dump 도 완전히 투명합니다.

 

 

Additional tips & caveats
  • Privilege model: 필요한 권한만 부여하세요. masked_user 에게 기본 테이블 접근 권한을 부여하면, View 를 통한 masking 은 의미가 없어집니다. dictionary administration functions 는 MASKING_DICTIONARIES_ADMIN 권한이 필요합니다.
  • Determinism: gen_rnd_email() 이나 gen_dictionary() 와 같은 function 은 쿼리마다 다른 결과를 생성할 수 있습니다. 재현 가능한 masked exports 가 필요할 경우 이를 고려하여 설계해야 합니다.
  • Caching: Percona Server 8.4 에서는 dictionary 조회 속도를 높이기 위해 dictionary term cache 가 도입되었습니다. 제공된 function 을 사용하지 않고 dictionary 테이블을 직접 변경하면 cache 가 동기화되지 않을 수 있습니다. 이 경우 제공된 masking_dictionaries_flush() 를 사용하거나 cache flush 설정을 구성하세요. 시스템 변수 dictionaries_flush_interval_seconds 를 사용하면 내부 dictionary cache 를 업데이트하는 간격(초)을 조정하여 dictionary 테이블 변경 사항과 맞출 수 있습니다.
  • Static vs dynamic masking: 위 접근 방식은 dynamic masking 입니다(실제 row 는 변경되지 않으며, SELECT 할때 masking). 테스트 환경에서는 static masking 을 선호할 수 있습니다. 이 경우, 모든 테이블을 masking 하여 재생성해야 하므로 SELECT … INTO OUTFILE 을 사용해 데이터를 내보내야 합니다. 필요 시 ProxySQL 과 함께 mysqldump 를 사용하는 방법도 고려할 수 있습니다.
  • Replication: Replication 환경에서는 실제 데이터가 평소대로 복제되며, Replica 에서는 masking 되지 않습니다. 데이터 masking 은 오직 SELECT 문에만 적용되며, 실제 데이터는 그대로 유지됩니다.
  • Challenges: masking 해야 할 테이블이 많다면, 각 테이블마다 masking View 나 stored procedure 를 정의해야 합니다. Dump 의 경우, ProxySQL 기반 솔루션을 사용하지 않는 한 테이블별로 내보내기를 관리해야 합니다. 또 다른 잠재적 도전 과제는 JOIN 조건에 사용되는 필드를 masking 해야 하는 경우입니다. 이런 경우, JOIN 되는 테이블의 해당 필드도 동일하게 masking 되어야 합니다. 불행히도 현재 function 들은 이를 지원하지 않으므로, 이를 처리하기 위해 커스텀 코드를 구현해야 합니다. 다만, 이런 경우는 자주 발생하지 않을 것으로 예상됩니다.

 

Conclusion

데이터 masking 은 오늘날 데이터 보안과 개인정보 보호 규정 준수를 위해 중요한 작업입니다. Percona Server for MySQL 8.4 는 최소한의 노력으로 data masking 을 구현할 수 있는 전용 컴포넌트와 함수를 제공합니다. 이를 통해 외부 파트너와 데이터를 안전하게 공유하며 비즈니스를 개발하고, 고객의 개인정보를 보호할 수 있습니다.

자세한 내용은 문서를 참고하세요:

 

 

블로그 원문 : https://www.percona.com/blog/data-masking-in-percona-server-for-mysql-8-4/

 

 

자유롭게 댓글을 달아주세요! 언제나 환영합니다.
기타 문의:  info@neoclova.co.kr
네오클로바 기술블로그 홈 바로가기: https://neoclova.net
네오클로바 홈페이지: http://neoclova.co.kr

 

Post Views: 35

Similar Posts

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다