DB - 数据库加密应用

1. 商超环境数据库加密方式列表（支持查询条件）

1.1 核心需求适配说明

商超场景需兼顾加密安全性和业务可查询性，尤其是手机号（会员查询）、金额（交易统计）、库存数量（补货提醒）等字段需作为查询条件，因此优先选择确定性加密算法（相同明文加密后结果一致，支持等值查询）。

数据库加密类型推荐算法查询支持安全级别性能影响商超场景适用字段
MySQL 8.0+ 双向确定性加密 AES-256-ECB（兼容查询） 支持等值查询 中高 低 会员手机号、商品编码、库存数量
  单向哈希 + 盐值 SHA-256 + 固定盐值 支持精确匹配 高 极低 支付密码、卡密验证
Oracle 12c+ 双向确定性加密 AES-256（CBC 模式 + 固定 IV） 支持等值查询 高 低 会员 ID、交易金额、促销门槛金额
  透明数据加密 TDE AES-256（表空间级） 完全支持查询 极高 极低 全表敏感字段（如会员详情、支付记录）
PostgreSQL 12+ 双向确定性加密 AES-256（pgcrypto 固定 IV） 支持等值查询 高 低 商品库存、会员等级、折扣比例
  单向哈希加密 bcrypt（固定盐值模式） 支持精确匹配 高 中 员工登录密码、API 密钥
SQL Server 2016+ 始终加密（确定性） AES-256（AEAD 模式） 支持等值查询 极高 中 银行卡后 4 位、会员积分、订单金额
  对称密钥加密 AES-256（固定 IV） 支持等值查询 高 低 门店编号、供应商编码

2. 商超场景加密详细示例（含查询关联）

2.1. MySQL 示例（会员手机号查询场景）

-- 场景说明：会员系统需加密存储手机号，同时支持通过手机号查询会员信息
-- 表结构设计：存储加密手机号+其他会员信息
CREATE TABLE商超会员表(
  会员ID INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增主键',
  会员姓名VARCHAR(50) NOT NULL COMMENT '明文存储',
  加密手机号VARBINARY(256) NOT NULL COMMENT 'AES加密后的手机号（二进制存储）',
  会员等级TINYINT NOT NULL COMMENT '1-普通，2-银卡，3-金卡',
  累计积分INT DEFAULT 0 COMMENT '明文存储',
  注册时间TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) COMMENT '商超会员信息表';

-- 加密密钥管理：生产环境需存储在MySQL密钥环（如keyring_file）
SET @加密密钥 = '商超会员加密密钥_32位长度哦1234'; -- 32字节=256位AES密钥

-- 1. 新增会员（加密存储手机号）
-- 关键点：使用ECB模式（确定性加密，相同手机号加密结果一致，支持查询）
INSERT INTO商超会员表(会员姓名, 加密手机号, 会员等级)
VALUES (
  '张三',
  AES_ENCRYPT('13800138000', @加密密钥), -- 省略IV参数默认使用ECB模式
  2
);

-- 2. 会员查询（通过手机号查询，加密字段作为条件）
-- 关键点：查询条件也需要加密，与存储的密文比对
SELECT 会员ID, 会员姓名, 会员等级, 累计积分
FROM商超会员表
-- 用相同密钥加密查询条件，与存储的加密手机号比对
WHERE 加密手机号 = AES_ENCRYPT('13800138000', @加密密钥);

-- 3. 业务关联：查询该会员的消费记录（关联订单表）
-- 订单表结构（简化）：CREATE TABLE 商超订单表(订单ID, 会员ID, 消费金额, 订单时间)
SELECT o.订单ID, o.消费金额, o.订单时间
FROM商超订单表o
JOIN商超会员表u ON o.会员ID = u.会员ID
WHERE u.加密手机号 = AES_ENCRYPT('13800138000', @加密密钥)
ORDER BY o.订单时间 DESC;
 

2.2. Oracle 示例（商品库存查询场景）

-- 场景说明：库存系统需加密存储商品剩余数量，支持低库存预警查询
-- 表结构设计：存储加密库存数量+商品信息
CREATE TABLE 商超商品库存表 (
  商品ID NUMBER(10) PRIMARY KEY,
  商品名称 VARCHAR2(100) NOT NULL, -- 明文
  加密库存数量 RAW(128) NOT NULL, -- 加密后的库存数量
  安全库存阈值 NUMBER(5) NOT NULL, -- 明文（触发补货的阈值）
  供应商ID NUMBER(8) NOT NULL -- 明文，关联供应商表
) COMMENT '商超商品库存信息';

-- 加密配置：使用固定IV实现确定性加密（支持查询）
CREATE OR REPLACE PACKAGE 库存加密工具包 IS
  -- 密钥存储建议：生产环境使用Oracle Wallet管理
  加密密钥 RAW(32) := UTL_RAW.CAST_TO_RAW('商超库存加密密钥32位长度1234');
  固定IV RAW(16) := UTL_RAW.CAST_TO_RAW('固定初始化向量16'); -- 固定IV确保相同值加密结果一致
  
  -- 加密函数：将数字转为RAW后加密
  FUNCTION 加密库存(库存数量 IN NUMBER) RETURN RAW;
  
  -- 解密函数：解密后转为数字
  FUNCTION 解密库存(加密库存 IN RAW) RETURN NUMBER;
END 库存加密工具包;
/

CREATE OR REPLACE PACKAGE BODY 库存加密工具包 IS
  FUNCTION 加密库存(库存数量 IN NUMBER) RETURN RAW IS
  BEGIN
    -- 使用AES-256-CBC模式+固定IV，确保相同库存数量加密结果一致
    RETURN DBMS_CRYPTO.ENCRYPT(
      src => UTL_RAW.CAST_TO_RAW(TO_CHAR(库存数量)),
      typ => DBMS_CRYPTO.AES_CBC_PKCS5,
      key => 加密密钥,
      iv => 固定IV
    );
  END;
  
  FUNCTION 解密库存(加密库存 IN RAW) RETURN NUMBER IS
  BEGIN
    RETURN TO_NUMBER(UTL_RAW.CAST_TO_VARCHAR2(
      DBMS_CRYPTO.DECRYPT(
        src => 加密库存,
        typ => DBMS_CRYPTO.AES_CBC_PKCS5,
        key => 加密密钥,
        iv => 固定IV
      )
    ));
  END;
END 库存加密工具包;
/

-- 1. 初始化库存数据（加密存储）
INSERT INTO 商超商品库存表 (商品ID, 商品名称, 加密库存数量, 安全库存阈值, 供应商ID)
VALUES (
  1001, '可口可乐330ml',
  库存加密工具包.加密库存(50), -- 加密存储50瓶
  20, -- 库存低于20触发补货
  5001
);

-- 2. 低库存查询（加密字段作为条件）
-- 场景：查询库存低于安全阈值的商品，用于生成补货单
SELECT 商品ID, 商品名称,
       库存加密工具包.解密库存(加密库存数量) AS 当前库存,
       安全库存阈值, 供应商ID
FROM 商超商品库存表
-- 关键点：解密后与安全阈值比对
WHERE 库存加密工具包.解密库存(加密库存数量) < 安全库存阈值;

-- 3. 业务关联：生成补货单（关联供应商表）
-- 供应商表结构（简化）：CREATE TABLE 供应商表(供应商ID, 名称, 联系方式)
INSERT INTO 补货单表 (补货单ID, 商品ID, 需补货数量, 供应商ID, 生成时间)
SELECT 
  10001, -- 补货单ID
  t.商品ID,
  t.安全库存阈值 - 库存加密工具包.解密库存(t.加密库存数量), -- 计算需补货数量
  t.供应商ID,
  SYSDATE
FROM 商超商品库存表 t
WHERE 库存加密工具包.解密库存(t.加密库存数量) < t.安全库存阈值;
 

2.3. PostgreSQL 示例（支付金额查询场景）

-- 场景说明：支付系统需加密存储交易金额，支持按金额区间查询（如满减活动）
-- 步骤1：启用加密扩展
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 步骤2：创建订单表（含加密金额）
CREATE TABLE 商超订单表 (
  订单ID SERIAL PRIMARY KEY,
  会员ID INT NOT NULL, -- 关联会员表
  加密金额 BYTEA NOT NULL, -- 加密存储的交易金额
  支付方式 VARCHAR(20) NOT NULL, -- 明文：微信/支付宝/现金
  订单状态 VARCHAR(10) NOT NULL, -- 明文：已支付/未支付
  下单时间 TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
) COMMENT '商超交易订单表';

-- 步骤3：定义加密/解密函数（封装逻辑，便于复用）
-- 关键点：使用固定IV实现确定性加密，支持金额查询
CREATE OR REPLACE FUNCTION 加密金额(金额 NUMERIC, 密钥 TEXT) 
RETURNS BYTEA AS $$
DECLARE
  固定IV BYTEA := '固定向量16字节长度'::BYTEA; -- 16字节固定IV
BEGIN
  -- 使用AES-256-CBC模式，将金额转为文本后加密
  RETURN pgp_sym_encrypt(
    amount::TEXT, 
    密钥,
    format('cipher-algo=aes256, iv=%L, compress-algo=0', 固定IV)
  );
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION 解密金额(加密金额 BYTEA, 密钥 TEXT) 
RETURNS NUMERIC AS $$
DECLARE
  固定IV BYTEA := '固定向量16字节长度'::BYTEA;
BEGIN
  RETURN pgp_sym_decrypt(
    加密金额, 
    密钥,
    format('cipher-algo=aes256, iv=%L, compress-algo=0', 固定IV)
  )::NUMERIC;
END;
$$ LANGUAGE plpgsql;

-- 1. 生成订单（加密存储金额）
-- 密钥管理：生产环境建议通过环境变量注入
SET @支付密钥 = '商超支付加密密钥_32位长度123456';

INSERT INTO 商超订单表 (会员ID, 加密金额, 支付方式, 订单状态)
VALUES (
  1001, -- 关联会员ID
  加密金额(199.99, @支付密钥), -- 加密存储199.99元
  '微信支付',
  '已支付'
);

-- 2. 金额区间查询（满200减30活动筛选）
-- 场景：查询消费满200元的订单，用于发放优惠券
SELECT 订单ID, 会员ID,
       解密金额(加密金额, @支付密钥) AS 实际金额,
       支付方式
FROM 商超订单表
-- 关键点：解密后进行区间判断
WHERE 解密金额(加密金额, @支付密钥) >= 200
  AND 订单状态 = '已支付'
  AND 下单时间 >= CURRENT_DATE - INTERVAL '7 days'; -- 近7天

-- 3. 业务关联：统计促销效果（关联促销活动表）
-- 促销活动表：CREATE TABLE 促销活动表(活动ID, 活动名称, 满减门槛, 活动时间)
SELECT 
  p.活动名称,
  COUNT(o.订单ID) AS 符合条件订单数,
  SUM(解密金额(o.加密金额, @支付密钥)) AS 总交易金额
FROM 促销活动表 p
LEFT JOIN 商超订单表 o ON 
  解密金额(o.加密金额, @支付密钥) >= p.满减门槛
  AND o.下单时间 BETWEEN p.活动开始时间 AND p.活动结束时间
WHERE p.活动ID = 501 -- 满200减30活动
GROUP BY p.活动名称;
 

2.4. SQL Server 示例（会员积分查询场景）

-- 场景说明：会员积分需加密存储，支持按积分区间查询（如积分兑换）
-- 步骤1：创建密钥层次结构（安全存储密钥）
-- 1.1 创建数据库主密钥（加密证书）
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '商超主密钥_StrongPwd2025';

-- 1.2 创建证书（加密对称密钥）
CREATE CERTIFICATE 积分加密证书 
WITH SUBJECT = '会员积分加密证书',
EXPIRY_DATE = '2030-12-31';

-- 1.3 创建对称密钥（AES-256，用于实际加密）
CREATE SYMMETRIC KEY 积分加密密钥
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE 积分加密证书;

-- 步骤2：创建会员积分表
CREATE TABLE 商超会员积分表 (
  记录ID INT PRIMARY KEY IDENTITY(1,1),
  会员ID INT NOT NULL, -- 关联会员表
  加密积分 VARBINARY(128) NOT NULL, -- 加密存储的积分
  积分来源 VARCHAR(50) NOT NULL, -- 明文：消费/活动/兑换
  变动时间 DATETIME DEFAULT GETDATE()
) COMMENT '会员积分变动记录';

-- 1. 新增积分记录（加密存储）
-- 打开对称密钥（使用证书解密）
OPEN SYMMETRIC KEY 积分加密密钥
DECRYPTION BY CERTIFICATE 积分加密证书;

-- 插入积分记录（消费199元获得199积分）
INSERT INTO 商超会员积分表 (会员ID, 加密积分, 积分来源)
VALUES (
  1001,
  -- 使用固定IV实现确定性加密（支持查询）
  EncryptByKey(Key_GUID('积分加密密钥'), '199', 1, '固定IV_16字节长度'), -- 最后参数为固定IV
  '消费'
);

-- 关闭密钥（安全最佳实践）
CLOSE SYMMETRIC KEY 积分加密密钥;

-- 2. 积分查询（筛选积分>=1000的会员，用于兑换礼品）
OPEN SYMMETRIC KEY 积分加密密钥
DECRYPTION BY CERTIFICATE 积分加密证书;

-- 先计算每个会员的总积分（解密后聚合）
WITH 会员总积分 AS (
  SELECT 
    会员ID,
    SUM(CONVERT(INT, DecryptByKey(加密积分, 1, '固定IV_16字节长度'))) AS 总积分
  FROM 商超会员积分表
  GROUP BY 会员ID
)
-- 关联会员表查询符合条件的会员
SELECT 
  m.会员ID,
  m.会员姓名,
  t.总积分
FROM 会员总积分 t
JOIN 商超会员表 m ON t.会员ID = m.会员ID
WHERE t.总积分 >= 1000 -- 积分满1000可兑换
ORDER BY t.总积分 DESC;

CLOSE SYMMETRIC KEY 积分加密密钥;

-- 3. 业务关联：积分兑换操作
-- 兑换记录表：CREATE TABLE 积分兑换表(兑换ID, 会员ID, 礼品ID, 消耗积分, 兑换时间)
OPEN SYMMETRIC KEY 积分加密密钥
DECRYPTION BY CERTIFICATE 积分加密证书;

-- 插入兑换记录并扣减积分
BEGIN TRANSACTION;

-- 1) 记录兑换
INSERT INTO 积分兑换表 (会员ID, 礼品ID, 消耗积分, 兑换时间)
VALUES (1001, 3001, 1000, GETDATE());

-- 2) 扣减积分（新增负积分记录）
INSERT INTO 商超会员积分表 (会员ID, 加密积分, 积分来源)
VALUES (
  1001,
  EncryptByKey(Key_GUID('积分加密密钥'), '-1000', 1, '固定IV_16字节长度'),
  '兑换礼品'
);

COMMIT TRANSACTION;

CLOSE SYMMETRIC KEY 积分加密密钥;
 

3. 商超场景加密关键关联说明

  1. 密钥与业务的关联：
    • 不同业务模块应使用独立密钥（如会员密钥、支付密钥、库存密钥），避免一钥泄露全库风险
    • 密钥需定期轮换（如每季度），轮换时需批量解密旧数据再用新密钥加密
  2. 加密字段与查询的关联：
    • 确定性加密虽支持查询，但相同明文会产生相同密文，建议对高频值（如金额 0、库存 100）进行微小扰动（如 + 0.01）
    • 加密字段索引：可创建基于加密值的索引（如CREATE INDEX idx_加密手机号 ON 商超会员表(加密手机号)）提升查询性能
  3. 业务流程关联：
    • 会员模块（加密手机号）→ 订单模块（加密金额）→ 积分模块（加密积分）通过会员 ID 关联，形成完整业务链
    • 加密操作应封装为函数 / 存储过程，避免在应用层重复实现，确保加密逻辑一致
  4. 性能与安全平衡：
    • 高频查询字段（如商品库存）建议使用硬件加速的 AES 算法
    • 非查询敏感字段（如身份证号）可使用随机加密（更高安全性）
    • 批量操作（如日结统计）建议在非高峰时段执行，减少对业务的影响
 posted on 2025-08-11 19:47  xibuhaohao  阅读(29)  评论(0)    收藏  举报