echo “=== 数据脱敏验证测试 ===”
echo “测试时间: $(date)”
echo “”

# 源端数据
echo “1. 源端原始数据:”
sqlplus -s fgedu/fgedu@fgedudb < 0 THEN ‘身份证已脱敏’
ELSE ‘身份证未脱敏’
END AS id_card_status
FROM fgedu02.fgedu_customers
WHERE ROWNUM = 1;
EOF

# 验证手机号脱敏
sqlplus -s fgedu/fgedu@fgedudb < 0 THEN ‘手机号已脱敏’
ELSE ‘手机号未脱敏’
END AS phone_status
FROM fgedu02.fgedu_customers
WHERE ROWNUM = 1;
EOF

echo “”
echo “=== 验证完成 ===”

# 步骤2：执行验证
$ ./verify_masking.sh

=== 数据脱敏验证测试 ===
测试时间: 2026-04-10 10:00:00

1. 源端原始数据:
CUSTOMER_ID CUSTOMER_NAME ID_CARD PHONE EMAIL
———– ————- ——————– ————- ——————–
1 张三 110101199001011234 13812345678 zhangsan@example.com
2 李四 310101198501011234 13912345678 lisi@example.com

2. 目标端脱敏数据:
CUSTOMER_ID CUSTOMER_NAME ID_CARD PHONE EMAIL
———– ————- ——————– ————- ——————–
1 张* 110101********1234 138****5678 zha***@example.com
2 李* 310101********1234 139****5678 lis***@example.com

3. 脱敏规则验证:
ID_CARD_STATUS
——————–
身份证已脱敏

PHONE_STATUS
——————–
手机号已脱敏

=== 验证完成 ===
from GoldenGate视频:www.itpux.com

# 敏感数据清单
| 表名 | 敏感字段 | 脱敏规则 |
|——————|——————|——————|
| fgedu_customers | id_card | 保留前6后4 |
| fgedu_customers | phone | 保留前3后4 |
| fgedu_accounts | account_pwd | 替换为固定值 |
| fgedu_cards | card_no | 保留前6后4 |
| fgedu_cards | cvv2 | 替换为*** |
| fgedu_trans | trans_amount | 数值扰动±10% |

# 脱敏配置
GGSCI (fgedu.net.cn)> EDIT PARAMS R_BANK_MASK

REPLICAT R_BANK_MASK
USERIDALIAS fgedu_alias
ASSUMETARGETDEFS

— 客户信息脱敏
MAP fgedu_core.fgedu_customers, TARGET fgedu_test.fgedu_customers,
COLMAP (
customer_id = customer_id,
customer_name = @STREXT(customer_name, 1, 1) || ‘**’,
id_card = @STREXT(id_card, 1, 6) || ‘********’ || @STREXT(id_card, 15, 18),
phone = @STREXT(phone, 1, 3) || ‘****’ || @STREXT(phone, 8, 11),
email = ‘test@test.com’,
address = ‘测试地址’
);

— 账户信息脱敏
MAP fgedu_core.fgedu_accounts, TARGET fgedu_test.fgedu_accounts,
COLMAP (
account_id = account_id,
customer_id = customer_id,
account_pwd = ‘******’,
account_status = account_status
);

— 银行卡信息脱敏
MAP fgedu_core.fgedu_cards, TARGET fgedu_test.fgedu_cards,
COLMAP (
card_id = card_id,
account_id = account_id,
card_no = @STREXT(card_no, 1, 6) || ‘**********’ || @STREXT(card_no, 16, 19),
cvv2 = ‘***’,
expiry_date = expiry_date
);

— 交易信息脱敏
MAP fgedu_core.fgedu_trans, TARGET fgedu_test.fgedu_trans,
COLMAP (
trans_id = trans_id,
account_id = account_id,
trans_amount = @ROUND(trans_amount * (1 + (@MOD(trans_id, 20) – 10) / 100.0), 2),
trans_date = trans_date,
trans_type = trans_type
);

# 脱敏效果
源端数据：
CUSTOMER_ID CUSTOMER_NAME ID_CARD PHONE ACCOUNT_PWD
———– ————- ——————– ————- ———–
1001 张三 110101199001011234 13812345678 abc123

目标端数据：
CUSTOMER_ID CUSTOMER_NAME ID_CARD PHONE ACCOUNT_PWD
———– ————- ——————– ————- ———–
1001 张** 110101********1234 138****5678 ******

# 原因分析
函数参数错误或数据类型不匹配

# 解决方案
# 1. 检查函数语法
COLMAP (phone = @STREXT(phone, 1, 3) || ‘****’ || @STREXT(phone, 8, 11))

# 2. 添加错误处理
COLMAP (phone = @IF(@STRLEN(phone)=11, @STREXT(phone, 1, 3) || ‘****’ || @STREXT(phone, 8, 11), phone))

# 问题2：脱敏后数据长度不匹配
# 错误信息
ERROR OGG-00868 Column value too long for column.

# 原因分析
脱敏后数据长度超过字段定义长度

# 解决方案
# 1. 修改目标表字段长度
SQL> ALTER TABLE fgedu02.fgedu_customers MODIFY (id_card VARCHAR2(20));

# 2. 或调整脱敏规则
COLMAP (id_card = @STREXT(id_card, 1, 6) || ‘****’ || @STREXT(id_card, 15, 18))

# 问题3：NULL值处理
# 现象
源端NULL值脱敏后变成字符串’NULL’

# 解决方案
COLMAP (phone = @IF(@COLTEST(phone, NULL), phone, @STREXT(phone, 1, 3) || ‘****’ || @STREXT(phone, 8, 11)))

# 问题4：性能问题
# 现象
脱敏后同步性能下降明显

# 解决方案
# 1. 简化脱敏函数
# 2. 减少条件判断
# 3. 使用批量处理
BATCHSQL BATCHESPERQUEUE 200, OPSPERBATCH 200

RULE_FILE=/GoldenGate/app/dirdef/masking_rules.txt

# 添加脱敏规则
add_rule() {
local table=$1
local column=$2
local rule=$3

echo “${table}.${column}=${rule}” >> $RULE_FILE
echo “规则已添加: ${table}.${column}”
}

# 查询脱敏规则
query_rule() {
local table=$1
local column=$2

grep “^${table}.${column}=” $RULE_FILE
}

# 列出所有规则
list_rules() {
echo “=== 当前脱敏规则 ===”
cat $RULE_FILE
}

case “$1” in
add)
add_rule $2 $3 $4
;;
query)
query_rule $2 $3
;;
list)
list_rules
;;
*)
echo “用法: $0 {add|query|list} [table] [column] [rule]”
;;
esac

# 使用示例
$ ./masking_rule_manager.sh add fgedu_customers id_card “保留前6后4”
规则已添加: fgedu_customers.id_card

$ ./masking_rule_manager.sh list
=== 当前脱敏规则 ===
fgedu_customers.id_card=保留前6后4
fgedu_customers.phone=保留前3后4
fgedu_customers.email=保留前3字符

# 解决方案2：脱敏效果审计脚本
#!/bin/bash
# masking_audit.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

LOG_FILE=/GoldenGate/app/logs/masking_audit.log

audit_masking() {
echo “=== 脱敏审计报告 ===” >> $LOG_FILE
echo “审计时间: $(date)” >> $LOG_FILE

# 检查脱敏覆盖率
sqlplus -s fgedu/fgedu@fgedudb <> $LOG_FILE
SELECT
‘身份证脱敏率’ AS metric,
ROUND(COUNT(CASE WHEN INSTR(id_card, ‘*’) > 0 THEN 1 END) * 100.0 / COUNT(*), 2) AS rate
FROM fgedu02.fgedu_customers
UNION ALL
SELECT
‘手机号脱敏率’,
ROUND(COUNT(CASE WHEN INSTR(phone, ‘*’) > 0 THEN 1 END) * 100.0 / COUNT(*), 2)
FROM fgedu02.fgedu_customers;
EOF

echo “” >> $LOG_FILE
}

audit_masking