Logo
Oracle Stored procedure encryption

Overview #

Supporting Oracle’s stored procedures in encryption scenarios makes up for the lack of data encryption processing capabilities in the original library’s calculations. The current function introduces a schema-level parameter UDF_ENABLED. Turning this parameter on or off only affects the parsing logic of stored procedures included in SQL, and has no effect on stored procedures not included in SQL. Engine will automatically identify the stored procedures involved in the tables in the Oracle database. When UDF_ENABLED = true, Engine will automatically create encryption-related UDFs (user defined functions) and copy and rewrite the identified stored procedures, which will involve encryption. SQL is rewritten using UDF. When the original stored procedure is accessed, Engine automatically parses the SQL and rewrites the SQL to access the rewritten stored procedure. The original mapping of the rewritten stored procedure can be queried through DistSQL.

Prerequisites for use #

  1. Database: Oracle 11g and above
  2. Oracle JDK version: JDK1.5 and above
  3. Supported encryption algorithms: AES, RC4, MD5, SphereEx:CHAR_TRANSFORM_LIKE, SphereEx:SM3

To use the SphereEx:SM3 algorithm, you need to load the dependent jar package in advance and grant the corresponding permissions.

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

  1. Have LOADJAVA environment

  2. Minimize permissions

To use Oracle’s stored procedure encryption function, users involved in the storage unit registered to the logical library need to add the following additional permissions:

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;

Instructions for use #

  • When using Oracle’s stored procedure encryption function, the logical column must have the same name as the plaintext column;
  • During the execution of SET ENCRYPT UDF_ENABLED = true/false, the relevant stored procedures involving the encrypted table cannot be called, and the validity of its data cannot be guaranteed;
  • Before execution of SET ENCRYPT UDF_ENABLED = true/false, it is necessary to ensure that there are no unfinished transactions or scheduled tasks in the table;
  • If DDL changes to the encrypted table involve changes to stored procedures, Engine needs to pass SET ENCRYPT UDF_ENABLED = false and SET ENCRYPT UDF_ENABLED = true to refresh the changed stored procedures. Before the Engine is refreshed to the changed stored procedure, its data validity cannot be guaranteed;
  • After UDF_ENABLED = true, when the added stored procedure involves an encrypted table, Engine needs to refresh the newly added stored procedure through SET ENCRYPT UDF_ENABLED = false and SET ENCRYPT UDF_ENABLED = true. Before the Engine is refreshed to the newly added stored procedure, its data validity cannot be guaranteed;
  • Original encryption syntax CREATE ENCRYPT RULE + SET ENCRYPT UDF_ENABLED supports stand-alone mode.
  • The new CONVERT TABLE… BY ENCRYPT RULE does not support stand-alone mode.
  • The setting of UDF_ENABLED parameters can only be done through Proxy
  • Stored procedures are only available through Driver
    • The call command is supported in Proxy, but it will only be transparently transmitted to Oracle and will not judge UDF_ENABLED and perform related logic;
    • When the driver calls the stored procedure, it will determine UDF_ENABLED and execute related logic.
  • Multiple logical libraries are not supported to reference the same storage unit.
  • After UDF_ENABLED = true, the SQL parsing and rewriting logic involving stored procedures in Engine is not affected by the query_with_plain parameter.
  • After UDF_ENABLED = true, only one logical library is supported to add a storage unit, and the type of storage unit is Oracle.
  • After UDF_ENABLED = true, only the following ALTER ENCRYPT RULE statements can be executed, and errors will be reported when executing other ALTER ENCRYPT RULEs.
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;

Usage example #

Environment Monitoring #

  1. Log in to Oracle to view the database version
  2. Check the JDK version
  3. Export the DDL of all tables, stored procedures, packages, functions, indexes, and views under the schema

The logged-in user executes the following SQL. filename is the file that stores the execution results.

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

Initialize the LOADJAVA environment #

  1. Load the third-party jar package to the host where the Oracle database is located

  2. Copy the binary package to the Oracle image

docker cp /opt/sphereex-dbplusengine-udf-5.4.1-SNAPSHOT.tar.gz oracle19c:/opt/
  1. Build the udf binary package sphereex-dbplusengine-udf-5.4.1-SNAPSHOT

Execute ./start.sh 脚本

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

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

Scenario 1: New business access, after setting UDF_ENABLED = true, add a table that needs to be encrypted #

Initialize the cluster environment of DBPlusEngine #

Initialize the cluster environment with relevant configurations based on business conditions

  1. Create logical database

create database encrypt_db;

use encrypt_db;

  1. Register data source
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')
);

Note: Encrypt_user needs to be given minimal permissions

  1. Create rule
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
create table T_PROC (ID int, NAME varchar(100));

Set UDF_ENABLED switch #

  1. Enable UDF_ENABLED
SET ENCRYPT UDF_ENABLED = true;
  1. View the UDF function name in Proxy
SHOW ENCRYPT TABLE UDF T_PROC;

Create stored procedure #

  1. Oracle underlying creation stored procedure
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 executes SET ENCRYPT UDF_ENABLED for refresh adaptation
SET ENCRYPT UDF_ENABLED = false;

SET ENCRYPT UDF_ENABLED = true;
  1. View the correspondence between the rewritten stored procedure and the original stored procedure
show encrypt procs;

Remove plaintext column configuration #

This step is optional. If the new business access does not require plain text storage, you can not add the plain text column configuration when creating encryption rules and tables.

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

At this point, the operation is completed, and the user is notified of other services such as scheduled tasks. The user should access the stored procedure according to the display results of show encrypt procs; and should use the rewritten stored procedure (the name of the stored procedure starts with SPEX).

Scenario 2: Existing business access, after setting UDF_ENABLED = true, convert the non-encrypted table to an encrypted table #

Converting a non-encrypted table to an encrypted table involves modifying the table structure, so ensure that no SQL statements are being executed in the relevant table.

Execute the SQL statement being executed in the database. There is no SQL statement being executed in the operated table. The reference SQL is as follows:

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';

Take T_PROC in Scenario 1 as an example

Set UDF_ENABLED = true #

This operation will rewrite all stored procedures related to the existing encryption tables in the system. The validity of the data is not guaranteed during this period. It is recommended to stop the related business of stored procedure calls.

SET ENCRYPT UDF_ENABLED = true;

View SET ENCRYPT UDF_ENABLED progress

SHOW ENCRYPT UDF_ENABLED;

Convert to the encrypted table and immediately shuffle the data #

This operation will add relevant columns to the table, add UDFs, rewrite the stored procedures related to the table, and start an asynchronous data shuffle. The stored procedures related to the encrypted table will be available only after the data shuffle is completed.

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);

View CONVERT progress

SHOW CONVERTING LIST;

View shuffle progress

SHOW ENCRYPTING LIST;

Remove plaintext column configuration #

This step is optional and should be performed after the data shuffle is completed. The user can confirm whether the clear text is stored according to the needs.

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

At this point, the operation is completed, and the user is notified of other services such as scheduled tasks. The user should access the stored procedure according to the display results of show encrypt procs; and should use the rewritten stored procedure (the name of the stored procedure starts with SPEX).

Scenario 3: During business operation, after setting UDF_ENABLED = true, convert the encrypted table to a non-encrypted table #

Converting a non-encrypted table to an encrypted table involves modifying the table structure, so you need to ensure that there are no SQL statements being executed in the relevant table.

Execute the SQL statement being executed in the database. There is no SQL statement being executed in the operated table. The reference SQL is as follows:

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';

Add plain text column #

When converting an encrypted table to a non-encrypted table, you must ensure that there is a clear text column configuration, that there are numbers in the plain text columns, and that the plain text columns have the same name as the logical columns. Otherwise, the availability of the business after the table conversion cannot be guaranteed. This step is optional. If the plain text columns have met Conditions allow you to proceed directly to subsequent steps.

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

Convert to non-encrypted table #

CONVERT TABLE t_proc DROP ENCRYPT RULE;

View CONVERT progress

SHOW CONVERTING LIST;

At this point, the operation is completed, and the user is notified of other services such as scheduled tasks, etc., using the original stored procedure.