|
|
Welcome to my blog, I have been looking for a simple easy way to keep my scripts handy, indexed and searchable. By storing them here, I can keep them up-to-date and share them with others as well.
|
Guest Book
Tell a Friend
Login
| February, 2012
Using DBMS_CRYPTO to encrypt data
In addition to controlling access to the tables with user grants, you can reduce security risks by encrypting data. Data encryption is not an infallible solution for data security but is an important tool in addressing specific security threats. While getting past the access controls is a rare occurrence, hackers have obtained large lists of credit card numbers after breaking into corporate databases. If the data was encrypted, the stolen information would be useless. Encryption of stored data is thus an important tool in limiting information loss.
Securing sensitive data from users while providing access to the rest of the data can be accomplished with the use of the basic cryptographic functions and procedures available in the DBMS_CRYPTO packages of Oracle 10g and Oracle 11g. You can encrypt/decrypt common Oracle datatypes including RAW and LOBs and it specifically supports BLOBS and CLOBS. There is also globalization support for encrypting data across different database character sets. Prior to DBMS_CRYPTO, Oracle provided the DBMS_OBFUSCATION_KIT in Oracle 8i and Oracle 9i but the DBMS_CRYPTO implementation is easier to use and manage and has more cryptographic algorithms available. To use these correctly and securely, a level of expertise is assumed and you should carefully test this before implementing in your production environment or you will be rebuilding the data from scratch.
Please do not confuse DBMS_CRYPTO with Oracle Transparent Data Encryption in 10g, which will encrypt data "at rest" in the data files. With TDE, when the data is generated, it is provided to the DB in plain text and then Oracle automatically converts that plain text into an encrypted form for storage. With TDE, if a user has select access to the table, they see the unencrypted values of the encrypted columns. With DBMS_CRYPTO, a user with select access will see the encrypted values. The security is gained by not allowing the user access to execute the procs that encrypt or decrypt the data. You simply allow the application layer access to execute these procs and then the application can use the unencrypted value. Note that you should be "wrapping” the proc so the user community cannot see the key used in the DBA_SOURCE views to encrypt or decrypt the data.
With TDE, the front end doesn't need to know that the data was encrypted. The whole point behind transparent encryption is to keep the sensitive data in the data files safe which is especially helpful if the backups are ever compromised. Just make sure you do not store the wallet file with the backups of the datafiles and the data will be safe even if you automate the opening of the wallet during database startup.
For Batch operations, these encryption and decryption activities do require a small amount of overhead on the DB which will incrementally manifest itself during larger batch operations and will slow them down. A bit of performance testing will help you figure out if the incremental increase in time is acceptable.
Let’s look at an example using DBMS_CRYPTO to secure sensitive data from the users or applications. Let’s setup a table with a password column. When users select the data, they will see an encrypted value. Then we will setup a stored procedure to encrypt the data when inserting into the table. When you want to use the unencrypted value in your application, you simply use the proc to decrypt it and compare it to the password provided by the end user. Limit the access to the proc to prevent users from seeing the unencrypted values outside of the application. This way only the users with access to the application can utilize the stored proc and the application can have a built in audit trail of who is accessing the secured data.
/* Start a sqlplus session as SCOTT and create a table for the username and password columns */
CREATE TABLE user_pwd (
username VARCHAR2(30),
password VARCHAR2(200)
);
insert into user_pwd values ('dhansen','dhansen123');
insert into user_pwd values ('eshaffer','eshaffer456');
insert into user_pwd values ('kjames','kjames789');
commit;
|
This table has the user logons and passwords in plain text. You can also create a primary key to keep the usernames unique in this table and perhaps a foriegn key to the primary user table so you know who is using the username, what department they work in, and the rest of the related user attributes.
Now let's create a stored procedure for the encryption and decryption of the password field.
/* Start a sqlplus session as sysdba and create this package */
CREATE OR REPLACE PACKAGE gen_encr
AS
FUNCTION encrypt (p_PlainText VARCHAR2) RETURN RAW DETERMINISTIC;
FUNCTION decrypt (p_Encrypted RAW) RETURN VARCHAR2 DETERMINISTIC;
END;
/
CREATE OR REPLACE PACKAGE BODY gen_encr
AS
EncryptionType PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
/* ENCRYPT_AES256 is the encryption algorithem AES 256-bit algorithm.
CHAIN_CBC for Cipher Block Chaining, a process that hides plain text patterns
by XORing blocks with the previous ciphertext block, and then encrypted.
PAD_PKCS5 Provides padding which complies with the
PKCS #5 Password-Based Cryptography Standard. */
EncryptionKey RAW (32) := UTL_RAW.cast_to_raw('ThisTextIsMyEncryptionKeyForThisPackage');
-- The encryption key for AES256 algorithem, should be 32 bytes or more.
FUNCTION encrypt (p_PlainText VARCHAR2) RETURN RAW DETERMINISTIC
IS
encrypted_raw RAW (2048);
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_Encrypted RAW) RETURN VARCHAR2 DETERMINISTIC
IS
decrypt_raw RAW (2048);
BEGIN
decrypt_raw := DBMS_CRYPTO.DECRYPT
( src => p_Encrypted,
typ => encryption_type,
key => encryption_key
);
RETURN (UTL_RAW.CAST_TO_VARCHAR2 (decrypt_raw));
END decrypt;
END;
/
grant execute on gen_encr to scott;
create public synonym gen_encr for sys.gen_encr;
exit;
|
The funciton will always return the same output for any given input arguement so we are using a deterministic clause in the function so the database won't re-execute the function once it has been executed for a specific input value. This will increase performance of the package. The encryption or decryption of a VARCHAR2 doesn't work directly using DBMS_CRYPTO, so we have to convert it to RAW before encrypting it.
Once we have a package with the encryption and decryption functions, let's see how to use it.
/* From the sqlplus session logged on as scott, use the package to show the encrypted value */
select gen_encr.encrypt('Hello World') encrypted from dual;
ENCRYPTED
----------------------------------
89738046FA0CFDD2581198FBF98DE2C5
/* Now let's try to decrypt that value using the package we created. */
select gen_encr.decrypt('89738046FA0CFDD2581198FBF98DE2C5') decrypted from dual;
DECRYPTED
------------------
Hello World
/* Now let's use the package with a table to store the encrypted values. */
column username format a10
column password format a12
select * from user_pwd;
USERNAME PASSWORD
---------- ------------
DHANSEN dhansen123
ESHAFFER eshaffer456
KJAMES kjames789
/* The passwords are in plain text above, let's encrypt them in the table. */
update users set password = gen_encr.encrypt(password);
commit;
/* The passwords are now encrypted using the algorithm and key specified in the package gen_encr. */
column password format a32
select * from user_pwd;
USERNAME PASSWORD
---------- --------------------------------
DHANSEN D705C2186A64B1A6FF3B6E6220746731
ESHAFFER 98DDCC4DAB5F13140C8D657D381E05FC
KJAMES D9A656AD83B7ADC7443D6BECD173715E
/* You should see encrypted passwords now instead of plain text. */
insert into user_pwd values ('STIGER', gen_encr.encrypt('scott456'));
commit;
select * from user_pwd;
USERNAME PASSWORD
---------- --------------------------------
DHANSEN D705C2186A64B1A6FF3B6E6220746731
ESHAFFER 98DDCC4DAB5F13140C8D657D381E05FC
KJAMES D9A656AD83B7ADC7443D6BECD173715E
STIGER 41D69256E23E7A3D2AFEFF2E5C082FFD
/* Notice the newly created record for SCOTT has an encrypted password. */
grant select on user_pwd to hr;
|
These encrypted values can be seen by the users who have SELECT access on the user_pwd table. The data can only be decrypted using the same key and algorithem it was encrypted with so all the password values can only be decrypted using the package gen_encr. I have granted SELECT on table to HR. Lets see what happens when he querys data from the user_pwd table.
/* start a sqlplus session as the hr user */
column username format a10
column password format a32
select * from user_pwd;
USERNAME PASSWORD
---------- --------------------------------
DHANSEN D705C2186A64B1A6FF3B6E6220746731
ESHAFFER 98DDCC4DAB5F13140C8D657D381E05FC
KJAMES D9A656AD83B7ADC7443D6BECD173715E
STIGER 41D69256E23E7A3D2AFEFF2E5C082FFD
/* Now try to decrypt the passwords */
SQL> select gen_encr.decrypt(password) from scott.user_pwd;
select gen_encr.decrypt(password) from scott.user_pwd
*
ERROR at line 1:
ORA-00904: : invalid identifier
SQL> desc gen_encr
ERROR:
ORA-04043: object "SYS"."gen_encr " does not exist
|
Since the user HR has no access on the gen_encr package they cannot see the encrypted data. This will 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 gen_encr package itself.
encryption_key RAW (32) := UTL_RAW.cast_to_raw('ThisTextIsMyEncryptionKeyForThisPackage');
encryption_type PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256
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. I have seen this happen even though it was inadvertent. 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_gen_encr_package.sql and then run the wrap utility to wrap the code into a new file named create_gen_encr_package.wrp.
$ wrap iname=create_gen_encr_package.sql oname=create_gen_encr_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_gen_encr_package.sql to create_gen_encr_package.wrp
|
Lets see the contents of this new file create_gen_encr_package.wrp.
$ more create_gen_encr_package.wrp
CREATE OR REPLACE PACKAGE gen_encr 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=
|
The wrap utility actually has encrypted the PL/SQL code in the .sql file, and made it unreadable for anyone. Now use create_gen_encr_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_gen_encr_package.wrp
Package created.
Package body created.
SQL> column text format a72
SQL> select text from dba_source where name = 'gen_encr';
TEXT
----------------------------------------------------------------------
PACKAGE gen_encr 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=
PACKAGE BODY gen_encr wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
40a 1de
e5uq2Fhk2UgpK5ktxFEVOrE3zyowg5DxLdxqfC9AWE6OGeznw1zpVqLpwIrvVN522Z83WRwv
HQw142Mg0KQxSHaso6WOT7ud5P5VvVmrcR3le4Pvj9tpyogriMDGGQGWIR5T3g4s5tMka Qj
TA4FsoMpOy3 bK/y/VW u8 zHHC1m0LOziMSmnhkB nM U1jEvvRFGGXfOJrOSmXs VcyVr8
pyIFRQgr3JDZotwcfIZAw10k4Dcm87LMeBk6c0q2wdqgqcA422/awXKrAODetRti870jSTpn
46w5MWX/ickZHdrfBh6mMttQ8x4jDaNEcZR3X7VRdReUt05S6/LToL4T/VwlYFIqbzH7rbOR
kaEYBQchlWDg5n3hRBahHVLvEeOuoQVsdBqMwA55PfP1yqqsYWSBW4Mm4OYFJP/ry1NJYbbA
wVAA/SBw965bdu5doXjpf6y7D5dHh5dtIOL9uUA=
|
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.
posted by admin February 01, 2012 0:00 | permalink | comments (1) | Encryption
|
|
Copyright © 2007 - 2012 DataToTheMax.com, LLC. All rights reserved.
|
