Logo
Oracle 存储过程加密

概述 #

在加密场景中支持 Oracle 的存储过程，弥补了原有库内计算对数据加密处理能力的不足。当前功能引入了一个 schema 级别的参数 UDF_ENABLED，这个参数的开启或关闭仅对 SQL 中包含的存储过程的解析逻辑有影响，对 SQL 中不包含的存储过程的情况无影响。Engine 会自动识别 Oracle 数据库中表涉及的存储过程，当 UDF_ENABLED = true 时，Engine 会自动创建加密相关的 UDF（user defined functions 用户） 并将识别到的存储过程进行拷贝和改写，将涉及到加密的 SQL 使用 UDF 进行改写。当原存储过程被访问时，Engine 自动解析 SQL ，改写 SQL 访问改写后的存储过程。通过 DistSQL 可以查询到被改写后的存储过程的原始映射。

使用前提 #

  1. 数据库：Oracle 11g 及以上
  2. Oracle JDK 版本：JDK1.5 及以上
  3. 支持的加密算法： AES、RC4、MD5、SphereEx:CHAR_TRANSFORM_LIKE、SphereEx:SM3

使用 SphereEx:SM3 算法需要提前加载依赖 jar 包，并授予相应的权限

BEGIN
   dbms_java.grant_permission(
      grantee => 'SYSTEM',
      permission_type => 'SYS:java.security.SecurityPermission',
      permission_name => 'insertProvider.BC',
      permission_action => ''
   );
END/

  1. 具备 LOADJAVA 环境

  2. 最小化权限

使用 Oracle 的存储过程加密功能，涉及到注册到逻辑库的存储单元的用户需要额外增加如下权限

CREATE USER sphereex_udf_convert_table identified by sphereex_udf_convert_table;
GRANT ALL PRIVILEGES TO sphereex_udf_convert_table;
ALTER USER sphereex_udf_convert_table QUOTA UNLIMITED ON USERS;

使用说明 #

  • 使用 Oracle 的存储过程加密功能时逻辑列必须与明文列名称相同；
  • SET ENCRYPT UDF_ENABLED = true/false 执行过程中，不能调用涉及到加密表的相关存储过程，无法保障其数据有效性；
  • SET ENCRYPT UDF_ENABLED = true/false 执行前需要保障表中无未完成的事务或定时任务；
  • 加密表发生DDL变更时如果涉及存储过程的变更，Engine 需要通过 SET ENCRYPT UDF_ENABLED = false 和 SET ENCRYPT UDF_ENABLED = true 的方式才能刷新到变更后的存储过程。在 Engine 刷新到变更后的存储过程前，无法保障其数据有效性；
  • UDF_ENABLED = true 后，增加的存储过程涉及加密表时，Engine 需要通过 SET ENCRYPT UDF_ENABLED = false 和 SET ENCRYPT UDF_ENABLED = true 的方式才能刷新到新增的存储过程。在 Engine 刷新到新增的存储过程前，无法保障其数据有效性；
  • 原有的加密语法 CREATE ENCRYPT RULE + SET ENCRYPT UDF_ENABLED 支持单机
  • 新增的 CONVERT TABLE …… BY ENCRYPT RULE 不支持单机
  • UDF_ENABLED 参数的设置只能通过 Proxy 完成
  • 存储过程只能通过 Driver 使用
    • Proxy 中支持 call 命令，但只会透传至 Oracle，不会判断 UDF_ENABLED 走相关逻辑；
    • Driver 端调用存储过程会判断 UDF_ENABLED 走相关逻辑。
  • 不支持多个逻辑库引用同一个存储单元
  • UDF_ENABLED = true 后， Engine 中涉及存储过程的 SQL 解析和改写逻辑不受 query_with_plain 参数的影响。
  • UDF_ENABLED = true 后 ，仅支持一个逻辑库添加一个存储单元，且存储单元的类型为 Oracle
  • UDF_ENABLED = true 后 ，只有如下 ALTER ENCRYPT RULE 的语句可以执行，其他 ALTER ENCRYPT RULE 执行均会报错
ALTER ENCRYPT RULE table_name SET QUERY_WITH_PLAIN = true;
ALTER ENCRYPT RULE table_name(column_name) SET QUERY_WITH_PLAIN = true;
ALTER ENCRYPT RULE table_name SET QUERY_WITH_PLAIN = false;
ALTER ENCRYPT RULE table_name(column_name)SET QUERY_WITH_PLAIN = false;

使用示例 #

环境监测 #

  1. 登录 Oracle 查看数据库版本
  2. 查看JDK版本
  3. 导出 schema 下的所有表，存储过程，包，函数，索引，视图 的 DDL

登录用户执行如下 SQL，filename 是存储执行结果的文件

spool filename
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
FROM USER_OBJECTS u
where U.OBJECT_TYPE IN ('TABLE','PROCEDURE','PACKAGE','PACKAGE_SPEC','PACKAGE_BODY','FUNCTION','INDEX','VIEW');
spool off

初始化 LOADJAVA 的环境 #

  1. 加载三方 jar 包至 Oracle 数据库所在主机

  2. 将二进制包 copy 到 Oracle 的镜像中

docker cp /opt/sphereex-dbplusengine-udf-5.4.1-SNAPSHOT.tar.gz oracle19c:/opt/
  1. 构建好 udf 二进制包 sphereex-dbplusengine-udf-5.4.1-SNAPSHOT

执行 ./start.sh 脚本

cd /opt/sphereex-dbplusengine-udf-5.4.1-SNAPSHOT/bin

./start.sh -usystem -poracle -a101.237.35.53 -P1521 -dORCLCDB

场景一 ：新业务接入，设置 UDF_ENABLED = true 后， 新增一张需要加密的表 #

初始化 DBPlusEngine 的集群环境 #

根据业务情况初始化相关配置的集群环境

  1. 创建逻辑库

create database encrypt_db;

use encrypt_db;

  1. 注册数据源
REGISTER STORAGE UNIT ds_0 (
URL='jdbc:oracle:thin:@//101.237.35.53:1521:1521/ORCLCDB',
USER='encrypt_user',
PASSWORD='oracle',
PROPERTIES('minPoolSize'='1', 'connectionTimeoutMilliseconds'='10000', 'maxLifetimeMilliseconds'='1800000', 'idleTimeoutMilliseconds'='60000', 'maxPoolSize'='500')
);

注：需要给 encrypt_user 赋予最小化权限

  1. 创建规则
CREATE ENCRYPT RULE T_PROC (
COLUMNS(
(NAME=`NAME`, DATA_TYPE='varchar(100)', PLAIN=`NAME`,
CIPHER=NAME_CIPHER, CIPHER_DATA_TYPE='varchar(1000)',
ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))
)
), QUERY_WITH_PLAIN=false);
  1. 创建表
create table T_PROC (ID int, NAME varchar(100));

设置 UDF_ENABLED 开关 #

  1. 开启 UDF_ENABLED
SET ENCRYPT UDF_ENABLED = true;
  1. 查看 Proxy 中的 UDF 函数名
SHOW ENCRYPT TABLE UDF T_PROC;

创建存储过程 #

  1. Oracle 底层创建存储过程
create or replace PROCEDURE t_procedure (var_id IN INT, var_name IN VARCHAR2) IS BEGIN INSERT INTO T_PROC (ID, NAME) VALUES (var_id, var_name); END;
/
  1. Proxy 执行 SET ENCRYPT UDF_ENABLED 进行刷新适配
SET ENCRYPT UDF_ENABLED = false;

SET ENCRYPT UDF_ENABLED = true;
  1. 查看改写后的存储过程与原存储过程的对应关系
show encrypt procs;

删除明文列配置 #

此步骤为可选步骤，如果新业务接入不想存明文，可以在创建加密规则和表的时候就不添加明文列的配置

CONVERT TABLE t_proc_encrypt_to_single DROP PLAIN COLUMNS(`user_name`);

至此操作完成，通知用户其他业务如定时任务等，应根据 show encrypt procs; 的显示结果访问存储过程应使用改写后的存储过程（存储过程的名字以 SPEX 开头）。

场景二 ：存量业务接入，设置 UDF_ENABLED = true 后，将非加密表转为加密表 #

将非加密表转为加密表，涉及表结构修改，所以需要确保相关表无正在执行的 SQL 语句

在数据库中执行正在执行的 SQL 语句，被操作表无正在执行的 SQL 语句。参考 SQL 如下：

SELECT s.sid, s.serial#, s.username, s.status, t.sql_id, t.sql_text,s.last_call_et, s.logon_time, s.program, s.module, s.action
FROM v$session s, v$sqltext_with_newlines t
WHERE s.sql_id = t.sql_id
AND s.status = 'ACTIVE'
AND s.username <> 'SYS';

以场景一中的 T_PROC 为例

设置 UDF_ENABLED = true #

此操作会将系统中现有的加密表相关的存储过程全部改写，期间不保障数据的有效性，建议停止存储过程调用的相关业务。

SET ENCRYPT UDF_ENABLED = true;

查看 SET ENCRYPT UDF_ENABLED 进度

SHOW ENCRYPT UDF_ENABLED;

转为加密表，并立即洗数 #

此操作会在表中添加相关列，增加 UDF，改写表相关的存储过程，启动异步洗数，洗数完成后加密表相关的存储过程才可用。

CONVERT TABLE t_proc BY ENCRYPT RULE(
COLUMNS(
(NAME=`name`, DATA_TYPE='varchar(100)', PLAIN=`name`,
CIPHER=name_cipher, CIPHER_DATA_TYPE='varchar(1000)',
ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))
)
), QUERY_WITH_PLAIN=true, START_ENCRYPTING_JOB=true);

查看 CONVERT 进度

SHOW CONVERTING LIST;

查看洗数进度

SHOW ENCRYPTING LIST;

删除明文列配置 #

此步骤为可选步骤，在洗数完成后操作，用户可以根据需求确认是否存明文。

CONVERT TABLE t_proc DROP PLAIN COLUMNS(`user_name`);

至此操作完成，通知用户其他业务如定时任务等，应根据 show encrypt procs; 的显示结果访问存储过程应使用改写后的存储过程（存储过程的名字以 SPEX 开头）。

场景三 ：业务运行过程中，设置 UDF_ENABLED = true 后，将加密表转为非加密表 #

将非加密表转为加密表，涉及表结构修改，所以需要确保相关表无正在执行的 SQL 语句

在数据库中执行正在执行的 SQL 语句，被操作表无正在执行的 SQL 语句。参考 SQL 如下：

SELECT s.sid, s.serial#, s.username, s.status, t.sql_id, t.sql_text,s.last_call_et, s.logon_time, s.program, s.module, s.action
FROM v$session s, v$sqltext_with_newlines t
WHERE s.sql_id = t.sql_id
AND s.status = 'ACTIVE'
AND s.username <> 'SYS';

新增明文列 #

将加密表转为非加密表需保障有明文列配置，明文列中有数，且明文列与逻辑列同名，否则无法保障表转换后业务的可用性，此步骤为可选步骤，如果明文列已经满足条件可直接继续后续步骤。

CONVERT TABLE t_proc ADD PLAIN COLUMNS(`name`), START_DECRYPTING_JOB=TRUE;

转为非加密表 #

CONVERT TABLE t_proc DROP ENCRYPT RULE;

查看 CONVERT 进度

SHOW CONVERTING LIST;

至此操作完成，通知用户其他业务如定时任务等，使用原存储过程。