Info Encriptacion Y Desencriptacion - Oracle

eral Examples of Using DBMS_CRYPTO to Encrypt/Decrypt Table Data [ID 863071.1]

Modified 30-AUG-2011 Type HOWTO Status PUBLISHED


In this Document
Goal
1. Simple encryption using one encryption key per table.
2. Double encryption - records are encrypted with specific record key, encrypted in turn with the table master key. Record encryption key is kept inlinein the table.
Solution
1. Simple encryption using one encryption key per table.
2. Double encryption - records are encrypted with specific record key, encrypted in turn with the table master key. Record encryption key is kept inline in the table.
References


Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 and later [Release: 10.2 and later ]Information in this document applies to any platform.
Goal

Several examples of using dbms_crypto:
1. Simple encryption using one encryption key per table.
2. Double encryption - records are encrypted with specific record key, encrypted in turn with the table master key. Record encryption key is kept inline in the table.
Solution
1. Simple encryption using one encryption key per table.
/*Descryption:
In this example, there is one encryption key generated for each table. This encryption key is stored at OS level and it is used to encrypt the table data.
#1. For this example, 2 tables are used.
#2. Each table has its own master encryption key. The master keys are stored in a directory at OS level.
#3. There is a context holding the encryption keys for the tables to be updated.
#4.The user performing the example is test.
*/

--Creation of objects needed for the example
--1. the below can also be used to clean up the objects created by this example
conn / as sysdba
DROP USER test CASCADE;
DROP CONTEXT enkeys;
--2. Create test user and grant needed privileges.
CREATE USER test IDENTIFIED BY test;
GRANT connect,resource TO test;
GRANT EXECUTE ON DBMS_CRYPTO TOtest;
GRANT CREATE ANY CONTEXT TO test;
GRANT EXECUTE ON DBMS_SESSION TO test;


--Encrypted table definition

CREATE TABLE encrypted_table1
(
id NUMBER,
encrypted_value RAW(48)
);

CREATE TABLE encrypted_table2
(
ID NUMBER,
encrypted_value RAW(48)
);

--Encryption key directory storage:
create directory enkey as '/opt/oracle/test/test111/enkey';
grant all on directory enkey totest;

--Context holding the encryption keys. The context can hold encryption keys for multiple tables in the same time.
CREATE CONTEXT enkeys USING encryption_context_pkg;

--Package to enable the context values.
CREATE OR REPLACE PACKAGE encryption_context_pkg
AS
PROCEDURE set_encryption_key(p_table_name IN VARCHAR2);
END encryption_context_pkg;
/

CREATE OR REPLACE PACKAGE BODYencryption_context_pkg
AS
PROCEDURE set_encryption_key(p_table_name IN VARCHAR2)
AS
v_encrkey RAW(32);
BEGIN
v_encrkey:=encryption_pkg.get_key(p_table_name); --because of this dependency, the initial compilation will fail. Create encryption_pkg to solve it.
DBMS_SESSION.set_context ('enkeys', p_table_name, v_encrkey);
END set_encryption_key;
END encryption_context_pkg;
/

--Package toperform the actual encryption
CREATE OR REPLACE PACKAGE encryption_pkg
IS
--Generate the encryption key for a given table
PROCEDURE generate_and_store_key(p_table_name IN VARCHAR2);
--Retrieve the encryption key from the local storage
FUNCTION get_key(p_table_name IN VARCHAR2) RETURN RAW;
--Function used to encrypt a given string
FUNCTION encryptor (
p_input_string IN VARCHAR2,p_table_name IN VARCHAR2
) RETURN RAW;
--Function used to decrypt a given string
FUNCTION decryptor (
p_encrypted_value IN RAW,
p_table_name IN VARCHAR2
) RETURN VARCHAR2;
END encryption_pkg;
/


CREATE OR REPLACE PACKAGE BODY encryption_pkg
IS
SQLERRMSG VARCHAR2(255);
SQLERRCDE NUMBER;

ENC_TYP_AES256 CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256
+ DBMS_CRYPTO.CHAIN_CBC
+...