Skip Headers

Oracle® Database Security Guide
10g Release 1 (10.1)

Part Number B10773-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

16
Developing Applications Using Data Encryption

In addition to controlling access, you can also encrypt data to reduce your security risks. However, data encryption is not an infallible solution. This chapter discusses the appropriate uses of data encryption and provides examples of using data encryption in applications. It contains the following topics:

Securing Sensitive Information

While the Internet poses new challenges in information security, many of them can be addressed by the traditional arsenal of security mechanisms:

Encryption is an important component of several of these solutions. For example, Secure Sockets Layer (SSL), an Internet-standard network encryption and authentication protocol, uses encryption to strongly authenticate users by means of X.509 digital certificates. SSL also uses encryption to ensure data confidentiality, and cryptographic checksums to ensure data integrity. Many of these uses of encryption are relatively transparent to a user or application. For example, many browsers support SSL, and users generally do not need to do anything special to enable SSL encryption.

Oracle has provided network encryption between database clients and the Oracle database since version 7. Oracle Advanced Security, an option to the Oracle database server, provides encryption and cryptographic checksums for integrity checking with any protocol supported by the database, including Oracle Net, Java Database Connectivity (JDBC--both "thick" and "thin" JDBC), and the Internet Intra-Orb Protocol (IIOP). Oracle Advanced Security also supports SSL for Oracle Net, "thick" JDBC, and IIOP connections.

While encryption is not a security cure-all, it is an important tool in addressing specific security threats. In particular, the rapid growth of e-business has spurred increased encryption of stored data, such as credit card numbers. While SSL is typically used to protect these numbers in transit to a Web site, where data is not protected as it is in storage, the file system or database storing them often does so as clear text (un-encrypted). Information stored in the clear is then directly accessible to anyone who can break into the host and gain root access, or gain illicit access to the database. Databases can be made quite secure through proper configuration, but they can also be vulnerable to host break-ins if the host is misconfigured. In well-publicized break-ins, a hacker obtained a large list of credit card numbers by breaking into a database. Had they been encrypted, the stolen information would have been useless. Encryption of stored data can thus be an important tool in limiting information loss even in the normally rare occurrence that access controls are bypassed.

Principles of Data Encryption

While there are many good reasons to encrypt data, there are many bad reasons to encrypt data. Encryption does not solve all security problems, and may even make some problems worse. The following sections describe some misconceptions about encryption of stored data:

Principle 1: Encryption Does Not Solve Access Control Problems

Most organizations need to limit data access to those who have a "need to know." For example, a human resources system may limit employees to viewing only their own employment records, while allowing managers of employees to see the employment records of subordinates. Human resources specialists may also need to see employee records for multiple employees.

This type of security policy-----limiting data access to those with a need to see it-----is typically addressed by access control mechanisms. The Oracle database has provided strong, independently-evaluated access control mechanisms for many years. It enables access control enforcement to an extremely fine level of granularity, through its Virtual Private Database capability.

Because human resources records are considered sensitive information, it is tempting to think that all information should be encrypted "for better security." However, encryption cannot enforce granular access control, and it may actually hinder data access. For example, an employee, his manager, and a human resources clerk may all need to access the employee's record. If all employee data is encrypted, then all three must be able to access the data in un-encrypted form. Therefore, the employee, the manager and the HR clerk would have to share the same encryption key to decrypt the data. Encryption would therefore not provide any additional security in the sense of better access control, and the encryption might actually hinder the proper or efficient functioning of the application. An additional issue is that it is very difficult to securely transmit and share encryption keys among multiple users of a system.

A basic principle behind encrypting stored data is that it must not interfere with access control. For example, a user who has SELECT privilege on EMP should not be limited by the encryption mechanism from seeing all the data he is otherwise allowed to see. Similarly, there is little benefit to encrypting part of a table with one key and part of a table with another key if users need to see all encrypted data in the table; it merely adds to the overhead of decrypting the data before users can read it. If access controls are implemented well, encryption adds little additional security within the database itself. Any user who has privilege to access data within the database has no more nor any less privilege as a result of encryption. Therefore, encryption should never be used to solve access control problems.

Principle 2: Encryption Does Not Protect Against a Malicious DBA

Some organizations, concerned that a malicious user might gain elevated (DBA) privilege by guessing a password, like the idea of encrypting stored data to protect against this threat. However, the correct solution to this problem is to protect the DBA account, and to change default passwords for other privileged accounts. The easiest way to break into a database is by using a default password for a privileged account that an administrator has allowed to remain unchanged. One example is SYS/CHANGE_ON_INSTALL.

While there are many destructive things a malicious user can do to a database after gaining DBA privilege, encryption will not protect against many of them. Examples include corrupting or deleting data, exporting user data to the file system to mail the data back to himself so as to run a password cracker on it, and so on.

Some organizations are concerned that DBAs, typically having all privileges, are able to see all data in the database. These organizations feel that the DBAs should merely administer the database, but should not be able to see the data that the database contains. Some organizations are also concerned about concentrating so much privilege in one person, and would prefer to partition the DBA function, or enforce two-person access rules.

It is tempting to think that encrypting all data (or significant amounts of data) will solve these problems, but there are better ways to protect against these threats. For example, Oracle does support limited partitioning of DBA privileges. Oracle provides native support for SYSDBA and SYSOPER users. SYSDBA has all privileges, but SYSOPER has a limited privilege set (such as startup and shutdown of the database).

Furthermore, an organization can create smaller roles encompassing a number of system privileges. A JR_DBA role might not include all system privileges, but only those appropriate to a junior DBA (such as CREATE TABLE, CREATE USER, and so on).

Oracle also enables auditing the actions taken by SYS (or SYS-privileged users) and storing that audit trail in a secure operating system location. Using this model, a separate auditor who has root privileges on the operating system can audit all actions by SYS, enabling the auditor to hold all DBAs accountable for their actions.

See Also:

"Auditing Administrative Users" for information about using the AUDIT_SYS_OPERATIONS parameter.

The DBA function by its nature is a trusted position. Even organizations with the most sensitive data-----such as intelligence agencies-----do not typically partition the DBA function. Instead, they vet their DBAs strongly, because it is a position of trust. Periodic auditing can help to uncover inappropriate activities.

Encryption of stored data must not interfere with the administration of the database; otherwise, larger security issues can result. For example, if by encrypting data you corrupt the data, you've created a security problem: the data itself has become uninterpretable, and it may not be recoverable.

Encryption can be used to limit the ability of a DBA-----or other privileged user-----to see data in the database. However, it is not a substitute for vetting a DBA properly, or for controlling the use of powerful system privileges. If untrustworthy users have significant privileges, they can pose multiple threats to an organization, some of them far more significant than viewing un-encrypted credit card numbers.

Principle 3: Encrypting Everything Does Not Make Data Secure

A common error is to think that if encrypting some data strengthens security, then encrypting everything makes all data secure.

As the discussion of the prior two principles illustrates, encryption does not address access control issues well, and it is important that encryption not interfere with normal access controls. Furthermore, encrypting an entire production database means that all data must be decrypted to be read, updated, or deleted. Encryption is inherently a performance-intensive operation; encrypting all data will significantly affect performance.

Availability is a key aspect of security. If encrypting data makes data unavailable, or adversely affects availability by reducing performance, then encrypting everything will have created a new security problem. Availability is also adversely affected by the database being inaccessible when encryption keys are changed, as good security practices require on a regular basis. When the keys are to be changed, the database is inaccessible while data is decrypted and re-encrypted with a new key or keys.

However, there may be advantages to encrypting data stored off-line. For example, an organization may store backups for a period of six months to a year off-line, in a remote location. Of course, the first line of protection is to secure the facility storing the data, by establishing physical access controls. Encrypting this data before it is stored may provide additional benefits. Since it is not being accessed on-line, performance need not be a consideration. While an Oracle database server does not provide this capability, there are vendors who can provide such encryption services. Before embarking on large-scale encryption of backup data, organizations considering this approach should thoroughly test the process. It is essential to verify that data encrypted before off-line storage can be decrypted and re-imported successfully.

Solutions For Stored Data Encryption in Oracle Database

The DBMS_CRYPTO package provides several means for addressing the security issues that have been discussed. (For backward compatibility, DBMS_OBFUSCATION_TOOLKIT is also provided.) This section includes these topics:

Oracle Database Data Encryption Capabilities

While encryption is not the ideal solution for addressing a number of security threats, it is clear that selectively encrypting sensitive data before storage in the database does improve security. Examples of such data could include:

To address these needs, Oracle Database provides the PL/SQL package DBMS_CRYPTO to encrypt and decrypt stored data. This package supports several industry-standard encryption and hashing algorithms, including the Advanced Encryption Standard (AES) encryption algorithm. AES has been approved by the National Institute of Standards and Technology (NIST) to replace the Data Encryption Standard (DES).

The DBMS_CRYPTO package enables encryption and decryption for common Oracle datatypes, including RAW and large objects (LOBs), such as images and sound. Specifically, it supports BLOBs and CLOBs. In addition, it provides Globalization Support for encrypting data across different database character sets.

The following cryptographic algorithms are supported:

Block cipher modifiers are also provided with DBMS_CRYPTO. You can choose from several padding options, including PKCS (Public Key Cryptographic Standard) #5, and from four block cipher chaining modes, including Cipher Block Chaining (CBC). (Padding must be done in multiples of eight bytes.)

Table 16-1 compares the DBMS_CRYPTO package features to the other PL/SQL encryption package, the DBMS_OBFUSCATION_TOOLKIT.

Table 16-1  DBMS_CRYPTO and DBMS_OBFUSCATION_TOOLKIT Feature Comparison
Package Feature DBMS_CRYPTO DBMS_OBFUSCATION_TOOLKIT

Cryptographic algorithms

DES, 3DES, AES, RC4, 3DES_2KEY

DES, 3DES

Padding forms

PKCS5, zeroes

none supported

Block cipher chaining modes

CBC, CFB, ECB, OFB

CBC

Cryptographic hash algorithms

MD5, SHA-1, MD4

MD5

Keyed hash (MAC) algorithms

HMAC_MD5, HMAC_SH1

none supported

Cryptographic pseudo-random number generator

RAW, NUMBER, BINARY_INTEGER

RAW, VARCHAR2

Database types

RAW, CLOB, BLOB

RAW, VARCHAR2

DBMS_CRYPTO is intended to replace the obfuscation toolkit, since it is easier to use and supports a range of algorithms accommodating both new and existing systems. Although 3DES_2KEY and MD4 are provided for backward compatibility, you achieve better security using 3DES, AES, MD5, or SHA-1. Thus 3DES_2KEY and MD4 are not recommended.

The DBMS_CRYPTO package includes cryptographic checksumming capabilities (MD5), which are useful for compares, and the ability to generate a secure random number (the RANDOMBYTES function). Secure random number generation is an important part of cryptography; predictable keys are easily-guessed keys, and easily-guessed keys may lead to easy decryption of data. Most cryptanalysis is done by finding weak keys or poorly-stored keys, rather than through brute force analysis (cycling through all possible keys).


Note:
  • Do not use DBMS_RANDOM as it is unsuitable for cryptographic key generation.
  • For more detailed descriptions of both DBMS_CRYPTO and DBMS_OBFUSCATION_TOOLKIT, see also the PL/SQL Packages and Types Reference.

Key management is programmatic. That is, the application (or caller of the function) must supply the encryption key; and this means that the application developer must find a way of storing and retrieving keys securely. The relative strengths and weaknesses of various key management techniques are discussed in the sections that follow. The DBMS_OBFUSCATION_TOOLKIT package, which can handle both string and raw data, requires the submission of a 64-bit key. The DES algorithm itself has an effective key length of 56-bits.

The DBMS_OBFUSCATION_TOOLKIT is granted to PUBLIC by default. Oracle Corporation strongly recommends that you revoke this grant.


Note:

While the DBMS_OBFUSCATION_TOOLKIT package can take either VARCHAR2 or RAW datatypes, it is preferable to use the RAW datatype for keys and encrypted data. Storing encrypted data as VARCHAR2 can cause problems if it passes through Globalization Support routines. For example, when transferring database to a database that uses another character set.

To convert between VARCHAR2 and RAW datatypes, use the CAST_TO_RAW and CAST_TO_VARCHAR2 funtions of the UTL_RAW package.



See Also:

PL/SQL Packages and Types Reference for detailed documentation of the DBMS_CRYPTO, DBMS_OBFUSCATION_TOOLKIT and UTL_RAW packages


Data Encryption Challenges

Even in cases where encryption can provide additional security, it is not without its technical challenges, as described in the following sections:

Encrypting Indexed Data

Special difficulties arise in handling encrypted data that is indexed. For example, suppose a company uses a national identity number--such as the U.S. Social Security number (SSN)--as the employee number for its employees. The company considers employee numbers to be very sensitive data and therefore wants to encrypt data in the EMPLOYEE_NUMBER column of the EMPLOYEES table. Because EMPLOYEE_NUMBER contains unique values, the database designers want to have an index on it for better performance.

However, if DBMS_CRYPTO or the DBMS_OBFUSCATION_TOOLKIT (or another mechanism) is used to encrypt data in a column, then an index on that column will also contain encrypted values. Although such an index can be used for equality checking (for example, 'SELECT * FROM emp WHERE employee_number = '123245'), if the index on that column contains encrypted values, then the index is essentially unusable for any other purpose. Oracle therefore recommends that developers not encrypt indexed data.

Given the privacy issues associated with overuse of national identity numbers (for example, identity theft), the fact that some allegedly unique national identity numbers have duplicates (as with U.S. Social Security numbers), and the ease with which a sequence can generate a unique number, there are many good reasons to avoid using national identity numbers as unique IDs.

Key Management

To address the issue of secure cryptographic key generation, Oracle Database provides support for secure random number generation, the RANDOMBYTES function of DBMS_CRYPTO. (This function replaces the capabilities provided by the GetKey procedure of the earlier DBMS_OBFUSCATION_TOOLKIT.) DBMS_CRYPTO calls the secure random number generator (RNG) previously certified by RSA. Developers should not, under any circumstances use the DBMS_RANDOM package. The DBMS_RANDOM package generates pseudo-random numbers, which, as RFC-1750 states, "The use of pseudo-random processes to generate secret quantities can result in pseudo-security."

Key Transmission

If the key is to be passed by the application to the database, then it must be encrypted. Otherwise, a snooper could grab the key as it is being transmitted. Use of network encryption, such as that provided by Oracle Advanced Security, will protect all data in transit from modification or interception, including cryptographic keys.

Key Storage

Key storage is one of the most important, yet difficult, aspects of encryption. To recover data encrypted with a symmetric key, the key must be accessible to the application or user seeking to decrypt the data. The key needs to be easy enough to retrieve that users can access encrypted data, without significant performance degradation. The key needs to be secure enough not to be easily recoverable by someone who is maliciously trying to access encrypted data which he is not supposed to see.

The three basic options available to a developer are:

Storing the Keys in the Database

Storing the keys in the database cannot always provide infallible security if you are trying to protect against the DBA accessing encrypted data. An all-privileged DBA could still access tables containing encryption keys. However, it can often provide quite good security against the casual snooper or against someone compromising the database file on the operating system.

As a trivial example, suppose you create a table (EMP) that contains employee data. You want to encrypt each employee's Social Security number (SSN) stored in one of the columns. You could encrypt each employee's SSN using a key which is stored in a separate column. However, anyone with SELECT access on the entire table could retrieve the encryption key and decrypt the matching SSN.

While this encryption scheme seems easily defeated, with a little more effort you can create a solution that is much harder to break. For example, you could encrypt the SSN using a technique that performs some additional data transformation on the employee_number before using it to encrypt the SSN. This technique might be something as simple, for example, as XORing the employee_number with the birthdate of the employee.

As additional protection, a PL/SQL package body performing encryption can be "wrapped," (using the WRAP utility) which obfuscates the code. A developer could wrap a package body called KEYMANAGE as follows:

wrap iname=/mydir/keymanage.sql

A developer can subsequently have a function in the package call the DBMS_OBFUSCATION_TOOLKIT with the key contained in the wrapped package.

While wrapping is not unbreakable, it makes it harder for a snooper to get the key. Because literals are still readable within the package file, the key could be split up in the package and then have the procedure reassemble it prior to use. Even in cases where a different key is supplied for each encrypted data value, not embedding the key value within a package, wrapping the package that performs key management (that is, data transformation or padding) is recommended.


See Also:

PL/SQL Packages and Types Reference for additional information about the WRAP utility


An alternative would be to have a separate table in which to store the encryption key and to envelope the call to the keys table with a procedure. The key table can be joined to the data table using a primary key to foreign key relationship. For example, EMPLOYEE_NUMBER is the primary key in the EMPLOYEES table which stores employee information and the encrypted SSN. EMPLOYEE_NUMBER is a foreign key to the SSN_KEYS table which stores the encryption keys for each employee's SSN. The key stored in the SSN_KEYS table can also be transformed before use (by using XORing), so the key itself is not stored unencrypted. The procedure itself should be wrapped, to hide the way in which keys are transformed before use.

The strengths of this approach are:

The weakness in this approach is that a user who has SELECT access to both the key table and the data table, who can derive the key transformation algorithm, can break the encryption scheme.

The preceding approach is not infallible, but it is good enough to protect against easy retrieval of sensitive information stored in clear text.

Storing the Keys in the Operating System

Storing keys in a flat file in the operating system is another option. Oracle Database enables you to make callouts from PL/SQL, which you could use to retrieve encryption keys. However, if you store keys in the operating system and make callouts to it, then your data is only as secure as the protection on the operating system. If your primary security concern driving you to encrypt data stored in the database is that the database can be broken into from the operating system, then storing the keys in the operating system arguably makes it easier for a hacker to retrieve encrypted data than storing the keys in the database itself.

Users Managing Their Own Keys

Having the user supply the key assumes the user will be responsible with the key. Considering that 40% of help desk calls are from users who have forgotten their passwords, you can see the risks of having users manage encryption keys. In all likelihood, users will either forget an encryption key, or write the key down, which then creates a security weakness. If a user forgets an encryption key or leaves the company, then your data is unrecoverable.

If you do elect to have user-supplied or user-managed keys, then you need to make sure you are using network encryption so the key is not passed from client to server in the clear. You also must develop key archive mechanisms, which is also a difficult security problem. Key archives or "backdoors" create the security weaknesses that encryption is attempting to address in the first place.

Changing Encryption Keys

Prudent security practice dictates that you periodically change encryption keys. For stored data, this requires periodically unencrypting the data, and reencrypting it with another well-chosen key. This would likely have to be done while the data is not being accessed, which creates another challenge. This is especially true for a Web-based application encrypting credit card numbers, since you do not want to shut down the entire application while you switch encryption keys.

Binary Large Objects (BLOBS)

Certain datatypes require more work to encrypt. For example, Oracle supports storage of binary large objects (BLOBs), which let users store very large objects (for example, multiple gigabytes) in the database. A BLOB can be either stored internally as a column, or stored in an external file.

For an example of using DBMS_CRYPTO on BLOB data, see the section entitled Example of Encrypt/Decrypt Procedures for BLOB Data.

Example of a Data Encryption PL/SQL Program

The following sample PL/SQL program (dbms_crypto.sql) illustrates encrypting data. This example code does the following:

The dbms_crypto.sql procedure follows:

DECLARE

input_string     VARCHAR2(16) := 'tigertigertigert';
raw_input        RAW(128) :=
UTL_RAW.CAST_TO_RAW(CONVERT(input_string,'AL32UTF8','US7ASCII'));
key_string       VARCHAR2(8)  := 'scottsco';
raw_key          RAW(128) :=
UTL_RAW.CAST_TO_RAW(CONVERT(key_string,'AL32UTF8','US7ASCII'));
encrypted_raw    RAW(2048);
encrypted_string VARCHAR2(2048);
decrypted_raw    RAW(2048);
decrypted_string VARCHAR2(2048); 
-- 1. Begin testing Encryption BEGIN
dbms_output.put_line('> Input String                     : ' || 
CONVERT(UTL_RAW.CAST_TO_VARCHAR2(raw_input),'US7ASCII','AL32UTF8'));
dbms_output.put_line('> ========= BEGIN TEST Encrypt =========');
encrypted_raw := dbms_crypto.Encrypt(
src => raw_input, 
typ => DBMS_CRYPTO.DES_CBC_PKCS5, 
key => raw_key);
   dbms_output.put_line('> Encrypted hex value              : ' || 
rawtohex(UTL_RAW.CAST_TO_RAW(encrypted_raw)));
decrypted_raw := dbms_crypto.Decrypt(
src => encrypted_raw, 
typ => DBMS_CRYPTO.DES_CBC_PKCS5, 
key => raw_key);
decrypted_string := 
CONVERT(UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw),'US7ASCII','AL32UTF8');
dbms_output.put_line('> Decrypted string output : ' ||
decrypted_string);
if input_string = decrypted_string THEN
dbms_output.put_line('> String DES Encyption and Decryption successful');
END if; dbms_output.put_line(''); dbms_output.put_line('> ========= BEGIN TEST Hash =========');
encrypted_raw := dbms_crypto.Hash(
src => raw_input, 
typ => DBMS_CRYPTO.HASH_SH1);
dbms_output.put_line('> Hash value of input string : ' ||
rawtohex(UTL_RAW.CAST_TO_RAW(encrypted_raw)));
dbms_output.put_line('> ========= BEGIN TEST Mac =========');
encrypted_raw := dbms_crypto.Mac(
src => raw_input, 
typ => DBMS_CRYPTO.HMAC_MD5, 
key => raw_key);
dbms_output.put_line('> Message Authentication Code : ' ||
rawtohex(UTL_RAW.CAST_TO_RAW(encrypted_raw)));
dbms_output.put_line(''); dbms_output.put_line('> End of DBMS_CRYPTO tests '); END; /

See Also:

PL/SQL User's Guide and Reference


Example of Encrypt/Decrypt Procedures for BLOB Data

The following sample PL/SQL program (blob_test.sql) illustrates encrypting and decrypting BLOB data. This example code does the following, printing out its progress (or problems) at each step:

The blob_test.sql procedure follows:

-- Create a table for BLOB column.
create table table_lob (id number, loc blob);

-- insert 3 empty lobs for src/enc/dec
insert into table_lob values (1, EMPTY_BLOB());
insert into table_lob values (2, EMPTY_BLOB());
insert into table_lob values (3, EMPTY_BLOB());

set echo on
set serveroutput on

declare

srcdata    RAW(1000);
srcblob    BLOB;
encrypblob BLOB;
encrypraw  RAW(1000);
encrawlen  BINARY_INTEGER;
decrypblob BLOB;
decrypraw  RAW(1000);
decrawlen  BINARY_INTEGER;

leng       INTEGER;
begin

-- RAW input data 16 bytes
srcdata := hextoraw('6D6D6D6D6D6D6D6D6D6D6D6D6D6D6D6D');

dbms_output.put_line('---');
dbms_output.put_line('input is ' || srcdata);
dbms_output.put_line('---');

-- select empty lob locators for src/enc/dec
select loc into srcblob from table_lob where id = 1;
select loc into encrypblob from table_lob where id = 2;
select loc into decrypblob from table_lob where id = 3;

dbms_output.put_line('Created Empty LOBS');
dbms_output.put_line('---');

leng := DBMS_LOB.GETLENGTH(srcblob);
IF leng IS NULL THEN
dbms_output.put_line('Source BLOB Len NULL ');
ELSE
dbms_output.put_line('Source BLOB Len ' || leng);
END IF;

leng := DBMS_LOB.GETLENGTH(encrypblob);
IF leng IS NULL THEN
dbms_output.put_line('Encrypt BLOB Len NULL ');
ELSE
dbms_output.put_line('Encrypt BLOB Len ' || leng);
END IF;

leng := DBMS_LOB.GETLENGTH(decrypblob);
IF leng IS NULL THEN
dbms_output.put_line('Decrypt  BLOB Len NULL ');
ELSE
dbms_output.put_line('Decrypt BLOB Len ' || leng);
END IF;

-- write source raw data into blob
DBMS_LOB.OPEN (srcblob, DBMS_LOB.lob_readwrite);
DBMS_LOB.WRITEAPPEND (srcblob, 16, srcdata);
DBMS_LOB.CLOSE (srcblob);

dbms_output.put_line('Source raw data written to source blob');
dbms_output.put_line('---');

leng := DBMS_LOB.GETLENGTH(srcblob);
IF leng IS NULL THEN
dbms_output.put_line('source BLOB Len NULL ');
ELSE
dbms_output.put_line('Source BLOB Len ' || leng);
END IF;

/*
* Procedure Encrypt
* Arguments: srcblob -> Source BLOB
*            encrypblob -> Output BLOB for encrypted data
*            DBMS_CRYPTO.AES_CBC_PKCS5 -> Algo : AES
*                                         Chaining : CBC
*                                         Padding : PKCS5
*            256 bit key for AES passed as RAW
*                -> 
hextoraw('000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F')
*            IV (Initialization Vector) for AES algo passed as RAW
*                -> hextoraw('00000000000000000000000000000000')
*/

DBMS_CRYPTO.Encrypt(encrypblob,
            srcblob,
            DBMS_CRYPTO.AES_CBC_PKCS5,
            hextoraw 
('000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F'),
            hextoraw('00000000000000000000000000000000'));


dbms_output.put_line('Encryption Done');
dbms_output.put_line('---');

leng := DBMS_LOB.GETLENGTH(encrypblob);
IF leng IS NULL THEN
dbms_output.put_line('Encrypt BLOB Len NULL');
ELSE
dbms_output.put_line('Encrypt BLOB Len ' || leng);
END IF;

-- Read encrypblob to a raw
encrawlen := 999;

DBMS_LOB.OPEN (encrypblob, DBMS_LOB.lob_readwrite);
DBMS_LOB.READ (encrypblob, encrawlen, 1, encrypraw);
DBMS_LOB.CLOSE (encrypblob);

dbms_output.put_line('Read encrypt blob to a raw');
dbms_output.put_line('---');

dbms_output.put_line('Encrypted data is (256 bit key) ' || encrypraw);
dbms_output.put_line('---');

/*
* Procedure Decrypt
* Arguments: encrypblob -> Encrypted BLOB to decrypt
*            decrypblob -> Output BLOB for decrypted data in RAW
*            DBMS_CRYPTO.AES_CBC_PKCS5 -> Algo : AES
*                                         Chaining : CBC
*                                         Padding : PKCS5
*            256 bit key for AES passed as RAW (same as used during Encrypt)
*                -> 
hextoraw('000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F')
*            IV (Initialization Vector) for AES algo passed as RAW (same as 
used during Encrypt)
*                -> hextoraw('00000000000000000000000000000000')
*/

DBMS_CRYPTO.Decrypt(decrypblob,
            encrypblob,
            DBMS_CRYPTO.AES_CBC_PKCS5,
            hextoraw 
('000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F'),
            hextoraw('00000000000000000000000000000000'));

leng := DBMS_LOB.GETLENGTH(decrypblob);
IF leng IS NULL THEN
dbms_output.put_line('Decrypt BLOB Len NULL');
ELSE
dbms_output.put_line('Decrypt BLOB Len ' || leng);
END IF;

-- Read decrypblob to a raw
decrawlen := 999;

DBMS_LOB.OPEN (decrypblob, DBMS_LOB.lob_readwrite);
DBMS_LOB.READ (decrypblob, decrawlen, 1, decrypraw);
DBMS_LOB.CLOSE (decrypblob);

dbms_output.put_line('Decrypted data is (256 bit key) ' || decrypraw);
dbms_output.put_line('---');

DBMS_LOB.OPEN (srcblob, DBMS_LOB.lob_readwrite);
DBMS_LOB.TRIM (srcblob, 0);
DBMS_LOB.CLOSE (srcblob);

DBMS_LOB.OPEN (encrypblob, DBMS_LOB.lob_readwrite);
DBMS_LOB.TRIM (encrypblob, 0);
DBMS_LOB.CLOSE (encrypblob);

DBMS_LOB.OPEN (decrypblob, DBMS_LOB.lob_readwrite);
DBMS_LOB.TRIM (decrypblob, 0);
DBMS_LOB.CLOSE (decrypblob);

end;
/

truncate table table_lob;
drop table table_lob;