[toc]

适用范围

oracle 通过在线重定义将表中字段的数据进行加密。

不采用 tde的方式，oracle 超大表，业务尽可能无感知的将表中的某个字段变成加密存储，这里采用在线重定义的方式，对业务尽可能少的影响，通常少于5秒钟。

方案概述

oracle 通过在线重定义将表中字段的数据进行加密。

实施步骤

创建示例表

1
2
3
4
5
CREATE TABLE t (
    id          NUMBER PRIMARY KEY,
    name        VARCHAR2(100),
    create_date DATE DEFAULT SYSDATE
);

插入测试数据

1
2
3
4
5
6
7
BEGIN
    FOR i IN 1..100 LOOP
        INSERT INTO t (id, name) VALUES (i, 'Name_' || i);
    END LOOP;
    COMMIT;
END;
/

创建加密函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE OR REPLACE FUNCTION encrypt_name(p_name IN VARCHAR2)
RETURN RAW DETERMINISTIC
IS
  v_max_length CONSTANT NUMBER := 2000;  -- 中间表RAW字段长度限制
  v_key RAW(32) := UTL_I18N.STRING_TO_RAW('32ByteAES256KeyForStrongSecurity', 'AL32UTF8');
  v_src RAW(32767);
BEGIN
  IF p_name IS NULL THEN
    RETURN NULL;
  END IF;

  -- AES-CBC模式加密时填充规则：加密后数据长度为 (原长度/16 + 1)*16
  IF UTL_RAW.LENGTH(UTL_I18N.STRING_TO_RAW(p_name, 'AL32UTF8')) > (v_max_length - 16) / 1.0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Name 值过长，加密后长度超过 ' || v_max_length || ' 字节');
  END IF;

  v_src := UTL_I18N.STRING_TO_RAW(p_name, 'AL32UTF8');

  RETURN DBMS_CRYPTO.ENCRYPT(  
    src => v_src,
    typ => DBMS_CRYPTO.AES_CBC_PKCS5, 
    key => v_key
  );
END;
/

创建解密函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE OR REPLACE FUNCTION decrypt_name(p_encrypted_raw IN RAW) 
RETURN VARCHAR2
DETERMINISTIC  -- 保持与加密函数一致的确定性声明
IS
  v_key RAW(32) := UTL_I18N.STRING_TO_RAW('32ByteAES256KeyForStrongSecurity', 'AL32UTF8');
  v_decrypted_raw RAW(32767);
BEGIN
  IF p_encrypted_raw IS NULL THEN
    RETURN NULL;
  END IF;

  -- 执行解密（保持与加密完全相同的参数配置）
  v_decrypted_raw := DBMS_CRYPTO.DECRYPT(
    src => p_encrypted_raw,
    typ => DBMS_CRYPTO.AES_CBC_PKCS5,  -- 必须与加密算法及模式严格一致
    key => v_key
  );

  -- RAW转字符串（保持与加密相同的字符集）
  RETURN UTL_I18N.RAW_TO_CHAR(v_decrypted_raw, 'AL32UTF8');

EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20002, 
      '解密失败：'||SQLERRM||
      ' [检查密钥一致性及输入数据类型]');
END;
/

创建在线重定义的临时表

1
2
3
4
5
CREATE TABLE t_temp (
    id          NUMBER PRIMARY KEY,
    name        RAW(2000),    -- 根据实际限制设立
    create_date DATE
);

在线重定义

生产环境执行建议

阶段 建议时段 关键监控指标 风险等级
启动重定义 非高峰 CPU、I/O利用率 中
同步数据 允许小并发 同步延迟、锁等待 高
FINISH切换 业务最低谷 锁持有时间 极高

通过分阶段执行并严格监控，可在 不影响在线业务 的前提下安全完成表结构变更。

在线重定义

1
2
3
4
5
6
7
8
9
10
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname        => USER,
    orig_table   => 'T',
    int_table    => 'T_TEMP',
    col_mapping  => 'id id, encrypt_name(name) name, create_date create_date',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK  -- 原表必须存在主键
  );
END;
/

说明

关键参数

  • col_mapping：定义字段映射，在此处调用加密函数。
  • CONS_USE_PK：要求原表必须有主键，不支持无主键表。

重要检查

1
2
-- 验证物化视图日志是否自动创建
SELECT * FROM user_mview_logs WHERE master = 'T';

复制数据、依赖对象（索引、约束）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname           => USER,
    orig_table      => 'T',
    int_table       => 'T_TEMP',
    copy_indexes    => DBMS_REDEFINITION.CONS_ORIG_PARAMS,  -- 按原表参数创建索引
    copy_triggers   => TRUE,     -- 同步触发器
    copy_privileges => TRUE,     -- 复制权限
    ignore_errors   => TRUE,     -- 忽略不影响业务的错误（如重复索引）
    num_errors      => num_errors
  );
  DBMS_OUTPUT.PUT_LINE('依赖项错误数: ' || num_errors);
END;
/

说明

  • ⚠️风险点：索引重建可能占用大量资源（建议在低峰期执行）。

  • 对大表的非必要索引，可在重定义后手动创建：

    1
    copy_indexes => DBMS_REDEFINITION.CONS_NONE  -- 禁用自动索引复制

增量同步数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
    uname      => USER,
    orig_table => 'T',
    int_table  => 'T_TEMP'
  );
  -- 可多次调用以降低单次事务压力（大数据量时推荐）
  /*
  FOR i IN 1..3 LOOP
    DBMS_REDEFINITION.SYNC_INTERIM_TABLE(...);
    COMMIT;
    DBMS_LOCK.SLEEP(60); -- 间隔60秒
  END LOOP;
  */
END;
/

说明

  • 运行机制：通过物化视图日志同步变更，确保中间表数据与原表一致。

  • 进度检查

    1
    2
    SELECT COUNT(*) FROM t; 
    SELECT COUNT(*) FROM t_temp;  -- 数据量应完全一致（若多次SYNC）

最终切换（原子操作）

1
2
3
4
5
6
7
8
BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
    uname      => USER,
    orig_table => 'T',
    int_table  => 'T_TEMP'  -- T和T_TEMP名称在此处交换
  );
END;
/

说明

  • 影响时间：业务短时阻塞（<5秒），需获取排他锁。

    锁监控

    1
    2
    3
    SELECT sid, type, lmode, request 
    FROM v$lock 
    WHERE id1 IN (SELECT object_id FROM dba_objects WHERE object_name = 'T');

  • 回退预案：若此时失败，原表仍可正常访问。

验证与清理

验证操作

  1. 结构验证

    1
    DESC t; -- 确认name字段类型变为RAW

  2. 数据校验

    1
    SELECT id, decrypt_name(name) AS plain_name FROM t WHERE ROWNUM <=10;

  3. 依赖项检查

    1
    SELECT index_name, status FROM user_indexes WHERE table_name = 'T';

清理中间对象

1
2
DROP TABLE t_temp PURGE;              -- 清理中间表
EXEC DBMS_MVIEW.REFRESH('T_LOG', 'C');-- 清除物化视图日志（若未自动清理）

参考文档

https://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11677

原文作者: liups.com

原文链接: http://liups.com/posts/8eeae1ab/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议