## 1. 传输加密配置
# 生成SSL证书
$ cd /kingbase/conf
$ openssl req -new -x509 -days 365 -nodes -text -out server.crt -keyout server.key
$ chmod 600 server.key
# 配置SSL
ALTER SYSTEM SET ssl = ‘on’;
ALTER SYSTEM SET ssl_cert_file = ‘server.crt’;
ALTER SYSTEM SET ssl_key_file = ‘server.key’;
ALTER SYSTEM SET ssl_ca_file = ‘root.crt’;
# 应用配置
SELECT sys_reload_conf();
# 验证SSL连接
$ ksql -U fgedu -d fgedudb -h 192.168.1.100 -p 54321 -c “\conninfo”
## 2. 存储加密配置
# 启用透明数据加密
ALTER SYSTEM SET tde_enabled = on;
# 应用配置
SELECT sys_reload_conf();
# 创建加密表空间
CREATE TABLESPACE encrypted_tbs OWNER fgedu LOCATION ‘/kingbase/tablespaces/encrypted’ ENCRYPTION ‘AES256’;
# 创建患者表
CREATE TABLE fgedu_patient (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
id_card VARCHAR(18),
phone VARCHAR(11),
address VARCHAR(200),
medical_record TEXT
) TABLESPACE encrypted_tbs;
# 插入测试数据
INSERT INTO fgedu_patient (name, id_card, phone, address, medical_record) VALUES
(‘张三’, ‘110101199001011234’, ‘13800138000’, ‘北京市海淀区’, ‘高血压，糖尿病’),
(‘李四’, ‘110101199001011235’, ‘13800138001’, ‘北京市朝阳区’, ‘冠心病，高血脂’);
# 查看数据
SELECT * FROM fgedu_patient;
## 3. 列级加密配置
# 创建加密密钥
CREATE ENCRYPTION KEY patient_key WITH ALGORITHM ‘AES256’ IDENTIFIED BY ‘patient_key_password’;
# 创建敏感信息表
CREATE TABLE fgedu_patient_sensitive (
id SERIAL PRIMARY KEY,
patient_id INTEGER REFERENCES fgedu_patient(id),
bank_card VARCHAR(16) ENCRYPTED WITH (ENCRYPTION_TYPE = ‘AES256’, ENCRYPTION_KEY = ‘patient_key’),
social_security VARCHAR(18) ENCRYPTED WITH (ENCRYPTION_TYPE = ‘AES256’, ENCRYPTION_KEY = ‘patient_key’),
family_member VARCHAR(100) ENCRYPTED WITH (ENCRYPTION_TYPE = ‘AES256’, ENCRYPTION_KEY = ‘patient_key’)
) TABLESPACE encrypted_tbs;
# 插入测试数据
INSERT INTO fgedu_patient_sensitive (patient_id, bank_card, social_security, family_member) VALUES
(1, ‘6222021234567890123’, ‘110101199001011234’, ‘父亲：张建国，母亲：李素华’),
(2, ‘6222021234567890124’, ‘110101199001011235’, ‘父亲：李建国，母亲：王素华’);
# 查看加密数据
SELECT * FROM fgedu_patient_sensitive;