基于d18n的数据分类分级实践

$ ./d18n.linux-amd64 --help
Usage:
  d18n.linux-amd64

Application Options:
  -v, --verbose                     verbose mode
      --help                        Show this help message
      --server=                     server type, support: mysql, postgres, sqlite, oracle, sqlserver, clickhouse (default: mysql)
      --dsn=                        formatted data source name
  -u, --user=                       database user
      --password=                   database password
  -p                                input password interactively
      --defaults-extra-file=        like mysql --defaults-extra-file for hidden password
  -h, --host=                       database host (default: 127.0.0.1)
  -P, --port=                       database port (default: 3306)
  -S, --socket=                     unix socket file
  -d, --database=                   database name
      --table=                      table name
      --charset=                    connection charset (default: utf8mb4)
      --limit=                      query result lines limit
  -e, --query=                      query read from file or command line
  -q                                input query interactively
  -f, --file=                       input/output file
      --schema=                     schema config file. support: sql, txt
      --mask=                       data masking config file. support: csv, psv, tsv format
      --cipher=                     cipher config file. support: yaml
      --sensitive=                  sensitive detection config file. support: yaml
      --print-cipher                print or auto-generate cipher
      --print-config                print config
      --preview=                    preview result file, print first N lines (default: 0)
      --lint                        lint file
      --import                      import file into database
      --detect                      detect sensitive info from data
      --watermark=                  watermark in export file. support: html, xlsx
      --check-empty                 check query result, if empty raise error
      --replace                     generate sql use replace into syntax, only support MySQL and SQLite
      --update=                     update primary key, separate by comma, case insensitive
      --complete-insert             complete insert with columns name
      --hex-blob=                   need hex encoding columns, separate by comma, case insensitive
      --ignore-columns=             import file ignore columns, separated by comma
      --extended-insert=            use multiple-row INSERT syntax that include several values list (default: 1)
      --ansi-quotes                 enable ANSI_QUOTES
      --disable-foreign-key-checks  disable foreign key checks
      --bom                         csv file with UTF8 BOM
      --excel-max-file-size=        excel max file size, limit by memory
      --lint-level=                 file lint level (default: error)
      --ignore-blank                ignore blank lines or columns when import file
      --comma=                      csv comma char (default: ,)
      --no-header                   no header line, only data lines
      --comments=                   support comment characters, multiple comment split by comma (default: #,--)
      --skip-lines=                 skip first N lines (default: 0)
      --rand-seed=                  random seed, default: current unix nano timestamp
      --max-buffer-size=            bufio MaxScanTokenSize
      --null-string=                NULL string write into file. e.g., NULL, nil, None, "" (default: NULL)

[sbtest]> select * from t1 limit 1\G
*************************** 1. row ***************************
         id: 1
          k: 49929
user_mobile: 13141516496
    id_card: 320826198702343445
    remark1: 安徽省封样小岗村343号
    remark2: 男
    remark3: 鄂D71D44
    remark4: xiaowang@126.com
1 row in set (0.00 sec)

./d18n.linux-amd64 --host 192.168.31.181 --port 3306 --database  sbtest --table t1 --user dts -p --detect --limit 10

输入密码，结果如下：
{
  "id": null,
  "id_card": [
    "uscc"
  ],
  "k": null,
  "remark1": [
    "address"
  ],
  "remark2": [
    "sex"
  ],
  "remark3": [
    "license-plate",
    "name"
  ],
  "remark4": [
    "email"
  ],
  "user_mobile": [
    "phone"
  ]
}

或者直接把密码在命令行传参【一般用于自动化脚本】
./d18n.linux-amd64 --host 192.168.31.181 --port 3306 --database  sbtest --table sbtest1 --user dts --password=dts --detect --limit 10 

[sbtest]> select * from sbtest2 limit 10,2 \G
*************************** 1. row ***************************
 id: 11
  k: 49987
  c: 82569929242-25785498716-70808946545-84908489946-74130244375-57057662210-83614392683-94191069368-76325060710-71897369074
pad: 28771936399-16374273979-88586374255-81481902335-12195498397
 dd: NULL
*************************** 2. row ***************************
 id: 12
  k: 50356
  c: 45496740906-71473774916-89017906302-67252181211-47670897466-43302252251-98125271194-76081915757-16086570359-70881428983
pad: 60996970211-19152844336-02470868185-86009077503-04256992965
 dd: NULL
 
 
d18n的结果如下，它把c和pad都当成了手机号
{
  "c": [
    "phone"
  ],
  "dd": null,
  "id": null,
  "k": null,
  "pad": [
    "phone"
  ]
}

$ ./d18n.linux-amd64 --host 192.168.31.181 --port 3306 --database  sbtest --table t1 --user dts -p --file aaa.sql -
-verbose
Password:
Get rows: 1000 Query cost: 2.390962ms

1、定义脱敏的规则文件 （脱敏规则 https://github.com/LianjiaTech/d18n/blob/main/doc/mask.md） 

cat mask.csv 
phone_no,SmokeInner,3,4,"x"
id_num,SmokeInner,4,4,"x"

上述规则的含义：
- 对phone_no脱敏，前面保留3位，后面保留4位，其余位置用x替代
- 对id_num脱敏，前面保留4位，后面保留4位，其余位置用x替代


2、方式1 将结果输出到控制台
# ./d18n.linux-amd64 --host 192.168.31.181 --port 3306 --database db1 --user dts --password='dts' --query "select * from tb1" --limit 10 --mask mask.csv 
+--------------------------------------+---------------------+---------------------+
|                  ID                  |      PHONE NO       |       ID NUM        |
+--------------------------------------+---------------------+---------------------+
| 12345678-aaaa-4910-abcd-490hgrewe64f | 789xxxxxxxxxxxx6601 |                     |
| 12345678-aaaa-439e-abcd-385hgrewe0f5 | 789xxxxxxxxxxxx6600 | 1234xxxxxxxxxxx1111 |
| 12345678-aaaa-4b9a-abcd-029hgrewee90 | 789xxxxxxxxxxxx6603 |                     |
| 12345678-aaaa-4812-abcd-7a0hgrewee68 | 789xxxxxxxxxxxx6608 |                     |
| 12345678-aaaa-4bbe-abcd-241hgrewef64 | 789xxxxxxxxxxxx6613 |                     |
| 12345678-aaaa-4913-abcd-431hgrewea88 | 789xxxxxxxxxxxx6613 |                     |
| 12345678-aaaa-491f-abcd-715hgrewecb7 | 789xxxxxxxxxxxx6616 |                     |
| 12345678-aaaa-420b-abcd-100hgreweba0 | 189xxxx1111         |                     |
| 12345678-aaaa-48c4-abcd-ab0hgrewe2f0 | 789xxxxxxxxxxxx7233 |                     |
| 12345678-aaaa-4f39-abcd-739hgrewe22a | 789xxxxxxxxxxxx9733 |                     |
+--------------------------------------+---------------------+---------------------+


3、方式2 结果输出为sql文件【例如：将生产数据脱敏后导入到线下或UAT】
# ./d18n.linux-amd64 --host 192.168.31.181 --port 3306 --database db1 --user dts --password='dts' --query "select * from tb1" --limit 10 --mask mask.csv --file a.sql

4、方式3 结果输出为excel文件【例如：对外交付】
# ./d18n.linux-amd64 --host 192.168.31.181 --port 3306 --database db1 --user dts --password='dts' --query "select * from tb1" --limit 10 --mask mask.csv --file a.xlsx

编辑 detect/sensitive.yaml 文件即可

例如，下面是根据自己公司的表情况修改后的规则（出于数据的敏感性这里只列出了部分）：
$ cat sensitive.yaml

name:
  key:
    - \bname\b
    - last[_]*name
    - family[_]*name
    - middle[_]*name
    - first[_]*name
  value:

birthday:
  key:
    - birthday
    - birth
  value:

sex:
  key:
    - sex
    - gender
  value:
    - ^男$|^女$|^male$|^female$|^man$|^men$|^woman$|^women$

address:
  key:
    - address
    - location
    - city
    - country
    - gps
  value: # address use NLP not regexp

email:
  key:
    - \b[e]*mail
  value:
    - (?i)([A-Za-z0-9!#$%&'*+\/=?^_{|.}~-]+@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?)
    - ([a-zA-Z0-9_\\-\\.]+)@((\\[[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.)|(([a-zA-Z0-9\\-]+\\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\\]?)

phone:
  key:
    - phone
    - _phone
    - telephone
    - kinsman_mobile
    - phone[_]*num
  value:
    - ^1(3[0-9]|4[01456879]|5[0-35-9]|6[2567]|7[0-8]|8[0-9]|9[0-35-9])\d{8}$ # 中国手机号
    - \b(0\d{2,3}-\d{7,8}|\(?0\d{2,3}[)-]?\d{7,8}|\(?0\d{2,3}[)-]*\d{7,8})\b # 中国座机号

patient_info:
  key:
    - patient_id_num
    - patient_mobile
    - patient_name

cd d18n
make release
会在 release 目录下，生成3个文件

1、从数据库管理平台拉取mysql清单，遍历每个集群的任一从节点，获取最近2天新增的库表清单
2、使用d18n对step1的库表清单进行检测，并将分析后的结果存到库里（数据分类）
3、在d18n检测后，还需要在平台的代码里做些判断，例如发现是用户身份相关的信息则标记为P1，位置信息标记为P2。
具体的分级规则根据各自公司内部的规定来即可。
4、最好能再采集几条真实数据也存到库里，便于后续的人工对之前d18n打标后的结果进行人工辅助判断。
（后台还需要有个定时任务会自动将前N天的d18n采集到真实数据给清空掉，防止数据泄露）