Encrypt or Decrypt sensitive data using PLSQL - DBMS_CRYPTO

Oracle 10g introduced Transparent Data Encryption, which is about storing data physically as encrypted in data files. The users always create/retrieve data as plain text. When the data is created, user provides plain text but Oracle automatically converts that plain text into encrypted form and stores it in the data files and whenever users access that data, Oracle decrypts the data and show it to the users. This encryption and decryption is completely transparent to the users. They never know that data was encrypted. The whole point behind transparent encryption is to keep the sensitive data in the data files safe.

Oracle 10g引进TDE（透明数据加密）,由数据文件存储加密数据。用户无需关心加密过程，可正常创建/查询平面文本。

Encrypting sensitive data for users or applications is a different thing at all and has no similarity with Transparent Data Encryption. If you want some column in the table to be shown as encrypted to the users, then you will have to encrypt it yourself at the time of creating that data. And when you want to use that data you will have to decrypt it first. Oracle provides utilities to perform encryption and decryption, e.g. DBMS_OBFUSCATION_KIT in 8i and 9i. This package was replaced by DBMS_CRYPTO in 10g, is more easier to use and have more cryptographic algorithms.

8i和9i可以使用DBMS_OBFUSCATION_KIT 包，10g使用DBMS_CRYPTO 包。

本案例将实现加密SCOTT用户下users表的password列：

In this tutorial we will use a "users" table with a field "password". The password field is suppose to show an encrypted value when queried, but should give the real password value (decrypted) when needed by the application.

$ sqlplus scott/tigerCREATE TABLE users (   userid       NUMBER,   username     VARCHAR2(30),   userlocation VARCHAR2(30),   password     VARCHAR2(200),   CONSTRAINT users_pk PRIMARY KEY (userid) );insert into usersvalues (1,'JAMES','TEXAS','james123');insert into usersvalues (2,'JONES','TEXAS','jones001');insert into usersvalues (3,'ALLEN','TEXAS','allen789');commit;exit;

We just created the table and created some plain text passwords. Lets now develop an encryption/decryption mechanism for the password field.

$ sqlplus / as sysdbaCREATE OR REPLACE PACKAGE enc_decAS   FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC;   FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC;END;/CREATE OR REPLACE PACKAGE BODY enc_decAS     encryption_type    PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES                                     + DBMS_CRYPTO.CHAIN_CBC                                     + DBMS_CRYPTO.PAD_PKCS5;     /*       ENCRYPT_DES is the encryption algorithem. Data Encryption Standard. Block cipher.        Uses key length of 56 bits.       CHAIN_CBC Cipher Block Chaining. Plaintext is XORed with the previous ciphertext        block before it is encrypted.       PAD_PKCS5 Provides padding which complies with the PKCS #5: Password-Based        Cryptography Standard     */     encryption_key     RAW (32) := UTL_RAW.cast_to_raw('MyEncryptionKey');     -- The encryption key for DES algorithem, should be 8 bytes or more.     FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC     IS        encrypted_raw      RAW (2000);     BEGIN        encrypted_raw := DBMS_CRYPTO.ENCRYPT        (           src => UTL_RAW.CAST_TO_RAW (p_plainText),           typ => encryption_type,           key => encryption_key        );       RETURN encrypted_raw;     END encrypt;     FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC     IS        decrypted_raw      RAW (2000);     BEGIN        decrypted_raw := DBMS_CRYPTO.DECRYPT        (            src => p_encryptedText,            typ => encryption_type,            key => encryption_key        );        RETURN (UTL_RAW.CAST_TO_VARCHAR2 (decrypted_raw));     END decrypt;END;/grant execute on enc_dec to scott;create public synonym enc_dec for sys.enc_dec;exit;

Using the same encryption algorithm and key, the functions "encrypt" and "decrypt" will always produce same results for same input parameters e.g. for a value ABC the function will always return same encrypted value. Therefore, it makes a lot of sense to create these functions as deterministic. Once a function is created as deterministic, and is being executed second time against same input parameters, Oracle doesn't really executes it the second time but it uses the results of its previous execution against the same input which increases the performance to a high extent.

The encryption or decryption on VARCHAR2 doesn't work directly using DBMS_CRYPTO, therefore, I have converted it to RAW before encrypting it.

For more information of cryptographic algorithms please see:
DBMS_CRYPTO Algorithms

$ sqlplus scott/tigerselect enc_dec.encrypt('Hello World') encrypted from dual;ENCRYPTED----------------------------------89738046FA0CFDD2581198FBF98DE2C5/* A simple value encrypted using the package we just created. */select enc_dec.decrypt('89738046FA0CFDD2581198FBF98DE2C5') decryptedfrom dual;DECRYPTED------------------Hello World/* The same value decrypted using the package we just created. */column username format a10column userlocation format a10column password format a10select * from users;    USERID USERNAME   USERLOCATI PASSWORD---------- ---------- ---------- ----------         1 JAMES      TEXAS      james123         2 JONES      TEXAS      jones001         3 ALLEN      TEXAS      allen789/* We can see Password data in plain text from above. */SQL> update users  2  set password = enc_dec.encrypt (password);3 rows updated.SQL> commit;Commit complete./*    We just encrypted the password data using the algorithm and key specified in the    package ENC_DEC.   We also need to make sure any newly created record has Password value encrypted using    the package ENC_DEC.*/column password format a32select * from users;    USERID USERNAME   USERLOCATI PASSWORD---------- ---------- ---------- --------------------------------         1 JAMES      TEXAS      D705C2186A64B1A6FF3B6E6220746731         2 JONES      TEXAS      98DDCC4DAB5F13140C8D657D381E05FC         3 ALLEN      TEXAS      D9A656AD83B7ADC7443D6BECD173715E/* All existing passwords are now encrypted */SQL> insert into users  2  values (4,'SCOTT','TEXAS',enc_dec.encrypt('scott456'));1 row created.SQL> commit;Commit complete.SQL> select * from users;    USERID USERNAME   USERLOCATI PASSWORD---------- ---------- ---------- --------------------------------         1 JAMES      TEXAS      D705C2186A64B1A6FF3B6E6220746731         2 JONES      TEXAS      98DDCC4DAB5F13140C8D657D381E05FC         3 ALLEN      TEXAS      D9A656AD83B7ADC7443D6BECD173715E         4 SCOTT      TEXAS      41D69256E23E7A3D2AFEFF2E5C082FFD/* The newly created record of user SCOTT has its password encrypted. */column decrypted_password format a10column encrypted_password format a32select username ,                                                     enc_dec.decrypt (password) decrypted_password,       password encrypted_passwordfrom users;USERNAME   DECRYPTED_ ENCRYPTED_PASSWORD---------- ---------- --------------------------------JAMES      james123   D705C2186A64B1A6FF3B6E6220746731JONES      jones001   98DDCC4DAB5F13140C8D657D381E05FCALLEN      allen789   D9A656AD83B7ADC7443D6BECD173715ESCOTT      scott456   41D69256E23E7A3D2AFEFF2E5C082FFDSQL> grant select on users to hr;Grant succeeded.

These encrypted values can only be seen by the users who have EXECUTE access to the ENC_DEC package. The data can only be decrypted using the same key and algorithem it was encrypted with. So all these password values can only be decrypted using the KEY and ALGORITHEM specified in the package ENC_DEC.
I have granted SELECT on table "users" to HR. Lets see what he sees when he querys data from users tables.

SQL> conn hr/hrConnected.column username format a10column userlocation format a10column password format a32select * from scott.users;    USERID USERNAME   USERLOCATI PASSWORD---------- ---------- ---------- --------------------------------         1 JAMES      TEXAS      D705C2186A64B1A6FF3B6E6220746731         2 JONES      TEXAS      98DDCC4DAB5F13140C8D657D381E05FC         3 ALLEN      TEXAS      D9A656AD83B7ADC7443D6BECD173715E         4 SCOTT      TEXAS      41D69256E23E7A3D2AFEFF2E5C082FFDSQL> select enc_dec.decrypt(password) from scott.users;select enc_dec.decrypt(password) from scott.users       *ERROR at line 1:ORA-00904: : invalid identifierSQL> desc enc_decERROR:ORA-04043: object "SYS"."ENC_DEC" does not exist

Since the user HR has no access on the ENC_DEC package he cannot see the encrypted data.

Keep your encrypted data safe from intruders

Its all about keeping your encryption algorithm and key hidden. If they are exposed, anyone can decrypt your encrypted data and see it all. In our case the key and the algorithm is stored in the ENC_DEC package itself.

encryption_key     RAW (32) := UTL_RAW.cast_to_raw('MyEncryptionKey');encryption_type    PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES                                + DBMS_CRYPTO.CHAIN_CBC                                + DBMS_CRYPTO.PAD_PKCS5;

Anyone having DBA privileges can see the source code of the package and leak out the encryption algorithm with the key. We must wrap the code to hide the stuff in the code before we create or ship the package with the application. Here is how to wrap your PL/SQL code to hide it from users.

I put CREATE PACKAGE statements in a file named create_enc_dec_package.sql and then run the wrap utility to wrap the code into a new file named create_enc_dec_package.wrp.

$ wrap iname=create_enc_dec_package.sql oname=create_enc_dec_package.wrpPL/SQL Wrapper: Release 10.2.0.1.0- Production on Sun Aug 15 06:44:32 2010Copyright (c) 1993, 2004, Oracle.  All rights reserved.Processing create_enc_dec_package.sql to create_enc_dec_package.wrp

Lets see the contents of this new file create_enc_dec_package.wrp.

$ more create_enc_dec_package.wrpCREATE OR REPLACE PACKAGE enc_dec wrapped a0000001abcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcd9a6 b2ceYtyd1wwstfJ/3xbNlo4sobVxYwg0xHAMusaS/pOPYrUgzeeSYbTuJ789ScOKWw4LWYL191ERLxTlyzbW7nRf8Cg4W0plfc4t7qD8d69uAPwYNtQpv3U6F9kwZQZnVeV+a5FlnUcEgL7J8khQZIhcYLQoTZ/irf0ixRnEj+4VqG1c4=/CREATE OR REPLACE PACKAGE BODY enc_dec wrapped a0000001abcdabcdabcdabcdabcd

The wrap utility actually has encrypted the PL/SQL code in the .sql file, and made it unreadable for anyone. Now use create_enc_dec_package.wrp file to create the package. Keep your .sql source file safe with you as if you want to make changes to the package later on, you will need it i.e. make changes to the .sql file, wrap it again and recreate the package.

$ sqlplus / as sysdbaConnected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> @create_enc_dec_package.wrpPackage created.Package body created.SQL> column text format a70SQL> select text from dba_source where name = 'ENC_DEC';TEXT----------------------------------------------------------------------PACKAGE enc_dec wrappeda0000001abcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcd9a6 b2ceYtyd1wwstfJ/3xbNlo4sobVxYwg0xHAMusaS/pOPYrUgzeeSYbTuJ789ScOKWw4LWYL191ERLxTlyzbW7nRf8Cg4W0plfc4t7qD8d69uAPwYNtQpv3U6F9kwZQZnVeV+a5FlnUcEgL7J8khQZIhcYLQoTZ/irf0ixRnEj+4VqG1c4=PACKAGE BODY enc_dec wrappeda0000001abcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdb40a 1dee5uq2Fhk2UgpK5ktxFEVOrE3zyowg5DxLdxqfC9AWE6OGeznw1zpVqLpwIrvVN522Z83WRwvHQw142Mg0KQxSHaso6WOT7ud5P5VvVmrcR3le4Pvj9tpyogriMDGGQGWIR5T3g4s5tMka+QjTA4FsoMpOy3+bK/y/VW+u8+zHHC1m0LOziMSmnhkB+nM+U1jEvvRFGGXfOJrOSmXs+VcyVr8pyIFRQgr3JDZotwcfIZAw10k4Dcm87LMeBk6c0q2wdqgqcA422/awXKrAODetRti870jSTpn46w5MWX/ickZHdrfBh6mMttQ8x4jDaNEcZR3X7VRdReUt05S6/LToL4T/VwlYFIqbzH7rbORkaEYBQchlWDg5n3hRBahHVLvEeOuoQVsdBqMwA55PfP1yqqsYWSBW4Mm4OYFJP/ry1NJYbbAwVAA/SBw965bdu5doXjpf6y7D5dHh5dtIOL9uUA=SQL>

As can be seen that after creating the package from wrapped sql script the package source code is unreadable to anyone, even the owner of the package. This way we can hide the encryption logic completely from every one.