# 手机号脱敏
UPDATE CUSTOMER
SET PHONE = SUBSTR(PHONE, 1, 3) || ‘****’ || SUBSTR(PHONE, 8, 4);

# 身份证号脱敏
UPDATE CUSTOMER
SET ID_CARD = SUBSTR(ID_CARD, 1, 6) || ‘********’ || SUBSTR(ID_CARD, 15, 4);

# 邮箱脱敏
UPDATE CUSTOMER
SET EMAIL = SUBSTR(EMAIL, 1, 2) || ‘***@’ || SUBSTR(EMAIL, LOCATE(‘@’, EMAIL) + 1);

# 姓名脱敏
UPDATE CUSTOMER
SET CUSTOMER_NAME = SUBSTR(CUSTOMER_NAME, 1, 1) || ‘**’;

# 银行卡号脱敏
UPDATE CUSTOMER
SET BANK_CARD = ‘**** **** **** ‘ || SUBSTR(BANK_CARD, 13, 4);

# 地址脱敏
UPDATE CUSTOMER
SET ADDRESS = SUBSTR(ADDRESS, 1, 6) || ‘***’;

# 创建脱敏视图
CREATE VIEW V_CUSTOMER_MASKED AS
SELECT
CUSTOMER_ID,
CUSTOMER_NAME,
SUBSTR(PHONE, 1, 3) || ‘****’ || SUBSTR(PHONE, 8, 4) AS PHONE,
SUBSTR(ID_CARD, 1, 6) || ‘********’ || SUBSTR(ID_CARD, 15, 4) AS ID_CARD,
SUBSTR(EMAIL, 1, 2) || ‘***@’ || SUBSTR(EMAIL, LOCATE(‘@’, EMAIL) + 1) AS EMAIL
FROM CUSTOMER;

# 授权普通用户访问脱敏视图
GRANT SELECT ON V_CUSTOMER_MASKED TO app_user;

# 不授权基表访问权限
# REVOKE SELECT ON CUSTOMER FROM app_user;

# 创建脱敏函数
CREATE OR REPLACE FUNCTION FN_MASK_PHONE(p_phone VARCHAR(20))
RETURNS VARCHAR(20)
LANGUAGE SQL
RETURN
CASE
WHEN LENGTH(p_phone) = 11 THEN SUBSTR(p_phone, 1, 3) || ‘****’ || SUBSTR(p_phone, 8, 4)
ELSE p_phone
END;

# 使用脱敏函数
SELECT
CUSTOMER_ID,
CUSTOMER_NAME,
FN_MASK_PHONE(PHONE) AS PHONE
FROM CUSTOMER;

# 基于角色的脱敏
CREATE ROLE APP_READONLY;
CREATE ROLE APP_SENSITIVE;

GRANT SELECT ON V_CUSTOMER_MASKED TO APP_READONLY;
GRANT SELECT ON CUSTOMER TO APP_SENSITIVE;

GRANT APP_READONLY TO app_user;
GRANT APP_SENSITIVE TO admin_user;

# 创建加密密钥
CREATE SECRET ‘my_secret_key’ FOR DATA ENCRYPTION;

# 创建加密列
ALTER TABLE CUSTOMER
ADD COLUMN ID_CARD_ENCRYPTED VARBINARY(256);

# 加密数据
UPDATE CUSTOMER
SET ID_CARD_ENCRYPTED = ENCRYPT(ID_CARD, ‘my_secret_key’);

# 解密数据
SELECT
CUSTOMER_ID,
DECRYPT_CHAR(ID_CARD_ENCRYPTED, ‘my_secret_key’) AS ID_CARD
FROM CUSTOMER;

# 创建加密函数
CREATE OR REPLACE FUNCTION FN_ENCRYPT_ID_CARD(p_id_card VARCHAR(18))
RETURNS VARBINARY(256)
LANGUAGE SQL
RETURN ENCRYPT(p_id_card, ‘my_secret_key’);

CREATE OR REPLACE FUNCTION FN_DECRYPT_ID_CARD(p_encrypted VARBINARY(256))
RETURNS VARCHAR(18)
LANGUAGE SQL
RETURN DECRYPT_CHAR(p_encrypted, ‘my_secret_key’);

# 启用数据库加密
# 1. 创建加密密钥库
# 2. 配置密钥库路径
# 3. 重启数据库

# 配置加密参数
UPDATE DATABASE CONFIGURATION USING
KEYSTORE_LOCATION ‘/db2keystore’
KEYSTORE_TYPE PKCS12;

# 加密表空间
CREATE TABLESPACE ENCRYPTED_TS
PAGESIZE 8K
MANAGED BY DATABASE
USING (FILE ‘/db2data/encrypted_ts.dms’ 1G)
ENCRYPT;

# 在加密表空间创建表
CREATE TABLE SENSITIVE_DATA (
ID INTEGER NOT NULL,
SENSITIVE_INFO VARCHAR(500),
CONSTRAINT PK_SENSITIVE_DATA PRIMARY KEY (ID)
) IN ENCRYPTED_TS;

# 查看加密表空间
SELECT
TBSP_NAME,
TBSP_ENCRYPTION
FROM SYSIBMADM.TBSP_UTILIZATION
WHERE TBSP_ENCRYPTION = ‘Y’;

# 启用审计
db2audit configure scope all status both;

# 启动审计
db2audit start;

# 查看审计配置
db2audit describe;

# 查看审计日志
db2audit archive database FGEDB to /db2audit;

# 分析审计日志
db2audit extract delasc to /db2audit/extract from /db2audit/audit.db2.node0000.catn0000;

# 查询审计数据
SELECT
TIMESTAMP,
CATEGORY,
EVENT,
USERID,
APPNAME
FROM AUDIT_LOGS
WHERE CATEGORY = ‘SECURITY’
ORDER BY TIMESTAMP DESC;

# 停止审计
db2audit stop;

# 最小权限原则
# 只授予必要的权限

# 创建角色
CREATE ROLE DATA_OPERATOR;
CREATE ROLE DATA_VIEWER;

# 授予权限
GRANT SELECT ON CUSTOMER TO DATA_VIEWER;
GRANT SELECT, INSERT, UPDATE ON ORDERS TO DATA_OPERATOR;

# 授予角色
GRANT DATA_VIEWER TO app_user;
GRANT DATA_OPERATOR TO operator_user;

# 查看用户权限
SELECT
GRANTEE,
GRANTEETYPE,
TABSCHEMA,
TABNAME,
CONTROLAUTH,
ALTERAUTH,
DELETEAUTH,
INSERTAUTH,
SELECTAUTH,
UPDATEAUTH
FROM SYSCAT.TABAUTH
WHERE GRANTEE = ‘APP_USER’;

# 定期审查权限
# 每季度审查一次用户权限
# 及时回收不必要的权限
REVOKE SELECT ON CUSTOMER FROM app_user;