Encryption and Decryption of data is very important for everyone of us . I was just scrolling through found lot of options provide by Oracle.
Oracle is provide an API -DBMS_OBFUSCATION_TOOLKIT that enables an application to encrypt data using either the Data Encryption Standard (DES) or the Triple DES algorithms
Oracle installs this package in the SYS schema. You can then grant package access to existing users and roles as needed. The package also grants access to the PUBLIC role so no explicit grant needs to be done.
When we do Encryption and Decryption, Most Important part of this Process is Key Management.
- Key Management
- Key Generation
- Key Maintainance.
Key management, including both generation and secure storage of cryptographic keys, is one of the most important aspects of encryption. If keys are poorly chosen or stored improperly, then it is far easier for a malefactor to break the encryption. Rather than using an exhaustive key search attack (that is, cycling through all the possible keys in hopes of finding the correct decryption key), cryptanalysts typically seek weaknesses in the choice of keys, or the way in which keys are stored.
Key generation is an important aspect of encryption. The DBMS_OBFUSCATION_TOOLKIT package includes tools for generating random material that can be used for encryption keys, but it does not provide a mechanism for maintaining them. Care must be taken by the application developer to ensure the secure generation and storage of encryption keys used with this package.
As per mosr author on this subject ,we have three options available for Key Maintainance and those are
- Store the key in the database
- Store the key in the operating system
- Have the user manage the key
In this post I will not discuss about what option one should choose or what are the Pros and Cons of these 3 .
Here I will just talk about the Encryption and Decryption API provide by Oracle.API DBMS_OBFUSCATION_TOOLKIT provide by Oracle has 3 procedures/Functions
- DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT
- DBMS_OBFUSCATION_TOOLKIT.DES3Encrypt
- DBMS_OBFUSCATION_TOOLKIT.DES3GetKey
Each of these 3 has 4 options (Please refere Oracle user guide or seach www.oracle.com for more details).I will discuss only 1 example each for these 3.
DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT - this subprogram generate the decrypted form of the input data.
DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT(
input IN RAW,
key IN RAW,
which IN PLS_INTEGER DEFAULT TwoKeyMode
iv IN RAW DEFAULT NULL)
RETURN RAW;
Please refere oracle.com for other DECRYPT options provide by this API.
Restrictions
You must supply a single key of either 128 bits for a 2-key implementation (of which only 112 are used), or a single key of 192 bits for a 3-key implementation
DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT – this subprogram generate the encrypted form of the input data by passing it through the Triple DES (3DES) encryption algorithm.
DBMS_OBFUSCATION_TOOLKIT.DES3Encrypt(
input IN RAW,
key IN RAW,
which IN PLS_INTEGER DEFAULT TwoKeyMode
iv IN RAW DEFAULT NULL)
RETURN RAW;
Please refere oracle.com for other Encrypt options provide by this API.
Restrictions
The DES3ENCRYPT procedure has two restrictions. The first is that the DES key length for encryption is fixed at 128 bits (for 2-key DES) or 192 bits (for 3-key DES); you cannot alter these key lengths.
The second is that you cannot execute multiple passes of encryption using 3DES.
DBMS_OBFUSCATION_TOOLKIT.DES3GetKey – this subprogram take a random value and uses it to generate an encryption key.
DBMS_OBFUSCATION_TOOLKIT.DES3GetKey(
which IN PLS_INTEGER DEFAULT TwoKeyMode,
seed IN RAW)
RETURN RAW;
Below is one complete Test case of these 3.
Test case – Store Customer # and Seed in database table ( Restrict the access to Seed Key).
Now Run the pl/sql program , It will take
Employee_id
Credit card # and
Operation (E- Encrypt / D-Decrupt) as parameter
If operation is E, it encrypt the Credit crad # and store the encrypted value in database.
If operation is D, it then Decrypt the encrypted CC# and Print it on the screen.
My test exapmle is bad example of encryption and description, but purpose of this post is to just show how this API work .
create table test_mk
( eid NUMBER,
sed RAW(1000),
ekey RAW(1000),
evalue RAW(1000));
select * from test_mk
insert into test_mk
(eid,sed)
values
(1,’22222222222222222AAAAAAAAAAA67465465465465465AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAFFFFFFFFFFAAAAAAAAAACCCCCCCCCCDDDDDDDDDDEEEEEEEEEEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’)
8988988899898898
drop table test_mk
l_string RAW(2000);– :=’22222222222222222AAAAAAAAAAA67465465465465465AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAFFFFFFFFFFAAAAAAAAAACCCCCCCCCCDDDDDDDDDDEEEEEEEEEEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’;
declare
l_encrypted_data RAW(1000);
l_decrypted_data RAW(1000);
l_key RAW(1000);
l_emp_id NUMBER :=&Enter_Emp_id;
l_seed RAW(1000);
no_rec EXCEPTION;
l_case VARCHAR2(1):=’&EnterCase’;
l_evalue RAW(1000);
l_ovalue VARCHAR2(20):=’&EnterOriginal_Value’;
BEGIN
BEGIN
SELECT sed, ekey,evalue
INTO l_seed,l_key,l_evalue
FROM test_mk
WHERE eid = l_emp_id;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(’Error = ‘||Sqlerrm);
raise no_rec;
END;
CASE
WHEN l_case = ‘E’ THEN
–l_ovalue:=’8988988899898898′;
dbms_output.put_line(l_ovalue);
dbms_output.put_line(l_seed);
dbms_output.put_line(’l_input = ‘||l_emp_id);
l_key:=DBMS_OBFUSCATION_TOOLKIT.DES3GetKey(
which =>0,
seed =>l_seed
);
dbms_output.put_line(’l_key = ‘||l_key);
l_encrypted_data :=DBMS_OBFUSCATION_TOOLKIT.DES3Encrypt
(input =>l_ovalue
,key =>l_key
,which =>0
,iv =>NULL
);
dbms_output.put_line(’l_encrypted_data = ‘||l_encrypted_data);
update test_mk
set evalue = l_encrypted_data,
ekey = l_key
where eid = l_emp_id;
WHEN l_case = ‘D’ THEN
l_decrypted_data:=DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT
(
input=>l_evalue
,key =>l_key
,which =>0
,iv =>NULL
);
dbms_output.put_line(’l_decrypted_data = ‘||l_decrypted_data);
END CASE;
EXCEPTION
WHEN no_rec THEN
dbms_output.put_line(’Exception raised- Quitting Process’);
WHEN OTHERS THEN
dbms_output.put_line(’Error - ‘||sqlerrm);
END;
