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/tiger CREATE TABLE users ( userid NUMBER, username VARCHAR2(30), userlocation VARCHAR2(30), password VARCHAR2(200), CONSTRAINT users_pk PRIMARY KEY (userid) ); insert into users values (1,'JAMES','TEXAS','james123'); insert into users values (2,'JONES','TEXAS','jones001'); insert into users values (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 sysdba
CREATE OR REPLACE PACKAGE enc_dec
AS
FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC;
FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC;
END;
/
CREATE OR REPLACE PACKAGE BODY enc_dec
AS
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/tiger
select 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') decrypted
from dual;
DECRYPTED
------------------
Hello World
/* The same value decrypted using the package we just created. */
column username format a10
column userlocation format a10
column password format a10
select * 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 a32
select * 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 a10
column encrypted_password format a32
select username ,
enc_dec.decrypt (password) decrypted_password,
password encrypted_password
from users;
USERNAME DECRYPTED_ ENCRYPTED_PASSWORD
---------- ---------- --------------------------------
JAMES james123 D705C2186A64B1A6FF3B6E6220746731
JONES jones001 98DDCC4DAB5F13140C8D657D381E05FC
ALLEN allen789 D9A656AD83B7ADC7443D6BECD173715E
SCOTT scott456 41D69256E23E7A3D2AFEFF2E5C082FFD
SQL> 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/hr
Connected.
column username format a10
column userlocation format a10
column password format a32
select * from scott.users;
USERID USERNAME USERLOCATI PASSWORD
---------- ---------- ---------- --------------------------------
1 JAMES TEXAS D705C2186A64B1A6FF3B6E6220746731
2 JONES TEXAS 98DDCC4DAB5F13140C8D657D381E05FC
3 ALLEN TEXAS D9A656AD83B7ADC7443D6BECD173715E
4 SCOTT TEXAS 41D69256E23E7A3D2AFEFF2E5C082FFD
SQL> select enc_dec.decrypt(password) from scott.users;
select enc_dec.decrypt(password) from scott.users
*
ERROR at line 1:
ORA-00904: : invalid identifier
SQL> desc enc_dec
ERROR:
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.wrp PL/SQL Wrapper: Release 10.2.0.1.0- Production on Sun Aug 15 06:44:32 2010 Copyright (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.wrp CREATE OR REPLACE PACKAGE enc_dec wrapped a000000 1 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd 9 a6 b2 ceYtyd1wwstfJ/3xbNlo4sobVxYwg0xHAMusaS/pOPYrUgzeeSYbTuJ789ScOKWw4LWYL191 ERLxTlyzbW7nRf8Cg4W0plfc4t7qD8d69uAPwYNtQpv3U6F9kwZQZnVeV+a5FlnUcEgL7J8k hQZIhcYLQoTZ/irf0ixRnEj+4VqG1c4= / CREATE OR REPLACE PACKAGE BODY enc_dec wrapped a000000 1 abcd abcd abcd abcd abcd
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 sysdba Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> @create_enc_dec_package.wrp Package created. Package body created. SQL> column text format a70 SQL> select text from dba_source where name = 'ENC_DEC'; TEXT ---------------------------------------------------------------------- PACKAGE enc_dec wrapped a000000 1 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd 9 a6 b2 ceYtyd1wwstfJ/3xbNlo4sobVxYwg0xHAMusaS/pOPYrUgzeeSYbTuJ789ScOKWw4LWYL1 91 ERLxTlyzbW7nRf8Cg4W0plfc4t7qD8d69uAPwYNtQpv3U6F9kwZQZnVeV+a5FlnUcEgL7J 8k hQZIhcYLQoTZ/irf0ixRnEj+4VqG1c4= PACKAGE BODY enc_dec wrapped a000000 1 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd b 40a 1de e5uq2Fhk2UgpK5ktxFEVOrE3zyowg5DxLdxqfC9AWE6OGeznw1zpVqLpwIrvVN522Z83WR wv HQw142Mg0KQxSHaso6WOT7ud5P5VvVmrcR3le4Pvj9tpyogriMDGGQGWIR5T3g4s5tMka+ Qj TA4FsoMpOy3+bK/y/VW+u8+zHHC1m0LOziMSmnhkB+nM+U1jEvvRFGGXfOJrOSmXs+VcyV r8 pyIFRQgr3JDZotwcfIZAw10k4Dcm87LMeBk6c0q2wdqgqcA422/awXKrAODetRti870jST pn 46w5MWX/ickZHdrfBh6mMttQ8x4jDaNEcZR3X7VRdReUt05S6/LToL4T/VwlYFIqbzH7rb OR kaEYBQchlWDg5n3hRBahHVLvEeOuoQVsdBqMwA55PfP1yqqsYWSBW4Mm4OYFJP/ry1NJYb bA wVAA/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.
