Encryption Examples

The purpose of this section is to give an overview of how encryption can be performed with different data. You’ll see a few examples of encrypting data with various algorithms. To start, a simple encryption of character data is presented. The output will be viewed both as a string and as a RAW. The decryption is done to verify that you can successfully recover the original data:

sec_mgr@KNOX10g> DECLARE       2   l_plaintext        VARCHAR2 (12) := 'This is data';    3   l_key              VARCHAR2 (20) := 'This is the Key';    4   l_encrypted_text   VARCHAR2 (24);     5   l_encrypted_raw    RAW (24);    6   l_decrypted_text   VARCHAR2 (`20);    7 BEGIN      8   l_encrypted_raw := data_crypto.encrypt_char (l_plaintext, l_key);    9   l_encrypted_text := data_crypto.encrypt (l_plaintext, l_key);   10   l_decrypted_text := data_crypto.decrypt (l_encrypted_text, l_key);   11   DBMS_OUTPUT.put_line ('PlainText: '          || l_plaintext);   12   DBMS_OUTPUT.put_line ('Key: '                || l_key);   13   DBMS_OUTPUT.put_line ('Encrypted RAW: '      || l_encrypted_raw);   14   DBMS_OUTPUT.put_line ('Encrypted string: '   || l_encrypted_text);   15   DBMS_OUTPUT.put_line ('Decrypted: '          || l_decrypted_text);   16 END;   17 /     PlainText:           This is data    Key:                 This is the Key    Encrypted RAW:       A84513EC540BF7E87BCA3AF29A0CAD34    Encrypted string:    e ^ '  = 8. =  T^¯óRí    Decrypted:           This is data    

Invoking encryption is done very easily. The challenge remains as to how to store encrypted data and how to mange the encryption keys. All of these issues will be addressed after a few more preliminary points are made.

Encrypting Character, Numbers, and Dates

Three of the basic data types you may be encrypting and storing are character, number, and dates. As seen in the next example, this can be accomplished fairly easy using the DATA_CRYPTO package. A simple table is created and populated with an encrypted string, number, and date:

sec_mgr@KNOX10g> CREATE TABLE enc_val_tab     2   (charval RAW(32),   3    dateval RAW(64),   4    numval RAW(32));    Table created.    sec_mgr@KNOX10g> EXEC data_crypto.setkey('EncryptionKey');    PL/SQL procedure successfully completed.   sec_mgr@KNOX10g> INSERT INTO enc_val_tab      2    VALUES (data_crypto.encrypt_char (USER),   3            data_crypto.encrypt_date (SYSDATE),   4            data_crypto.encrypt_number (10));    1 row created.    sec_mgr@KNOX10g> COMMIT ;   

You can see the data is stored encrypted and can be reconstructed through the decryption process:

sec_mgr@KNOX10g> COL "Character Data" format a20    sec_mgr@KNOX10g> COL "Number Data" format a20    sec_mgr@KNOX10g> COL "Date Data" format a20    sec_mgr@KNOX10g> -- Show stored data is encrypted. Have to convert    sec_mgr@KNOX10g> -- data from RAW to view.  sec_mgr@KNOX10g> SELECT UTL_RAW.cast_to_varchar2 (charval)    2 "Character Data",   3 UTL_RAW.cast_to_varchar2 (numval)    4 "Number Data",   5 UTL_RAW.cast_to_varchar2 (dateval)    6 "Date Data"   7 FROM enc_val_tab;    Character Data       Number Data         Date Data    -------------------- -----------------   ------------------ _6-?é?¤+P÷Q¥¯ ÷$     px b?¦$+êUâÿ¦ñ¦ä    ¡ùµ¿ò-¦°'>+" ¦-¦    sec_mgr@KNOX10g> -- Show decrypted values    sec_mgr@KNOX10g> SELECT data_crypto.decrypt_char (charval)    2                                   "Character Data",   3          TO_CHAR(data_crypto.decrypt_number (numval))   4                                      "Number Data",   5          TO_CHAR     6                (data_crypto.decrypt_date (dateval),   7                 'DD-MON-RRRR HH24:MI:SS')    8                                         "Date Data"   9   FROM enc_val_tab;    Character Data      Number Data     Date Data    -----------------   -------------   -----------------   SEC_MGR              10             10-APR-2004  00:00:00    

Everything in the preceding looks functional unless you wanted to preserve the time when you stored the date value.

Encrypting Dates and the NLS_DATE_FORMAT

Encrypting dates is a particularly onerous task. The date has to first be converted to a character string, then to a RAW (RAW data is the preferred way to store data). The decryption process must undergo the same steps in reverse order—RAW data is converted to character, then character is converted back to date.

To do this successfully, you have to ensure your NLS_DATE_FORMAT is set to capture all of the DATE attributes. For example, if the format is DD-MON-RR, then you will lose the time aspect of your date in the data casting operations. The DATA_CRYPTO package will perform the data casting, but you are responsible for ensuring the NLS_DATE_FORMAT is set appropriately.

This can be a bit more challenging than it might first appear. Notice that the user logged in through SQL*Plus does not get the database’s default NLS_DATE_FORMAT setting:

sec_mgr@KNOX10g> -- ** Show that current format only shows date not time **  sec_mgr@KNOX10g> COL date_format format a25    sec_mgr@KNOX10g> SELECT SYS_CONTEXT ('userenv', 'nls_date_format')    2                                         DATE_FORMAT,   3           SYSDATE    4   FROM DUAL;    DATE_FORMAT     SYSDATE    -------------   -------------   DD-MON-RR       10-APR-04       sec_mgr@KNOX10g> -- The current format was not set by the init.ora parameter    sec_mgr@KNOX10g> SHOW parameter nls_date_format       NAME                   TYPE                 VALUE    -------------------   -------------------  ---------   nls_date_format        string               DD-MON-RRRR HH24:MI:SS    

You can reset the date format by issuing an ALTER SESSION, but you will have to remember to do this every time prior to encrypting data. An alternative is to create a database logon trigger. Note that the trigger does not have to set everyone’s date format. The following trigger just alters the date format for the SEC_MGR and SCOTT users:

sec_mgr@KNOX10g> -- Create logon trigger to set date format to capture time.  sec_mgr@KNOX10g> -- Consider only setting format for specific users.  sec_mgr@KNOX10g> CREATE OR REPLACE TRIGGER nls_date_logon       2     AFTER LOGON ON DATABASE      3   BEGIN      4     IF USER IN ('SEC_MGR', 'SCOTT')    5     THEN      6       EXECUTE IMMEDIATE      7        'alter session set nls_date_format=''DD-MON-RRRR HH24:MI:SS'';    8     END IF;     9   END;   10 /       Trigger created.    sec_mgr@KNOX10g> -- Show new format    sec_mgr@KNOX10g> CONN sec_mgr/oracle10g   Connected.  sec_mgr@KNOX10g> SELECT SYS_CONTEXT ('userenv', 'nls_date_format')     2                                        DATE_FORMAT,    3         SYSDATE      4     FROM DUAL;    DATE_FORMAT                SYSDATE    -----------------         -----------------   DD-MON-RRRR HH24:MI:SS     10-APR-2004 11:20:07       sec_mgr@KNOX10g> -- NOTE the format is not set for all users    sec_mgr@KNOX10g> CONN system/manager    Connected.  system@KNOX10g> -- show new format    system@KNOX10g> SELECT SYS_CONTEXT ('userenv', 'nls_date_format')     2                                       DATE_FORMAT,    3         SYSDATE      4     FROM DUAL;    DATE_FORMAT       SYSDATE    --------------    ------------   DD-MON-RR         10-APR-04    

Once the NLS_DATE_FORMAT has been set appropriately, you’ll be able to preserve the time aspects of your date:

sec_mgr@KNOX10g> -- Remove previous records.  sec_mgr@KNOX10g> TRUNCATE TABLE enc_val_tab;    Table truncated.    sec_mgr@KNOX10g> -- Set encryption key    sec_mgr@KNOX10g> EXEC data_crypto.setkey('EncryptionKey');    PL/SQL procedure successfully completed.    sec_mgr@KNOX10g> -- Insert values. NLS_DATE_FORMAT will preserve time.  sec_mgr@KNOX10g> INSERT INTO enc_val_tab        2      VALUES (data_crypto.encrypt_char (USER),    3              data_crypto.encrypt_date (SYSDATE),    4              data_crypto.encrypt_number (10));    1 row created.    sec_mgr@KNOX10g> COMMIT ;    Commit complete.    sec_mgr@KNOX10g> -- Show decrypted values    sec_mgr@KNOX10g> COL "Character Data" format a20   sec_mgr@KNOX10g> COL "Date Data" format a20    sec_mgr@KNOX10g> SELECT data_crypto.decrypt_char (charval)     2                                   "Character Data",    3      TO_CHAR(data_crypto.decrypt_number (numval))    4                                   "Number Data",    5      TO_CHAR      6             (data_crypto.decrypt_date (dateval),    7              'DD-MON-RRRR HH24:MI:SS')     8                                      "Date Data"    9    FROM enc_val_tab;    Character Data      Number Data    Date Data    -----------------  -------------   ----------   SEC_MGR             10             10-APR-2004 11:56:47   

Encrypting CLOBs and BLOBs

The DBMS_CRYPTO package supports the encryption of CLOBs and BLOBs. However, this support is only manifested through the use of procedures. The DATA_CRYPTO package will convert these procedures to functions. For the CLOB data, the ciphertext is also returned as a CLOB.

In the following example, a table is created that holds CLOBs. This simulates a table that might hold large documents. A CLOB is inserted and then encrypted using a simple SQL update statement.

sec_mgr@KNOX10g> CREATE TABLE docs    2  (doc_id NUMBER(4),   3   doc CLOB)    4 /    Table created.    sec_mgr@KNOX10g> INSERT INTO docs    2          VALUES (1, 'This is CLOB data');    1 row created.    sec_mgr@KNOX10g> COMMIT ;    Commit complete.    sec_mgr@KNOX10g> EXEC data_crypto.setkey('This is the Key');    PL/SQL procedure successfully completed.    sec_mgr@KNOX10g> UPDATE docs     2     SET doc = data_crypto.encrypt (doc);    1 row updated.    sec_mgr@KNOX10g> COL Encrypted format a32  sec_mgr@KNOX10g> COL Decrypted format a32   sec_mgr@KNOX10g> SELECT doc encrypted, data_crypto.decrypt (doc) decrypted    2      FROM docs;    ENCRYPTED                        DECRYPTED  ------------------------------  ---------------- ??N??¦ôa+2IDJ°?-#?_-4o_ÉÖx+|     æ{ This is CLOB data   

To test the BLOB encryption, a file is loaded from the file system and stored as a BLOB in the table. The BLOB is then encrypted using a SQL update statement. To verify this process, the BLOB is converted to a CLOB and the value is printed. To begin, a file directory is created and access to the directory is granted to SEC_MGR:

system@KNOX10g> CREATE OR REPLACE DIRECTORY doc_dir AS    2                                     'C:\tmp\esbd';    Directory created.    system@KNOX10g> GRANT ALL ON DIRECTORY doc_dir TO sec_mgr;    Grant succeeded.   

The DOCS table is modified to hold the BLOB files. The file can be loaded into the table using the DBMS_LOB package. Once loaded, the BLOB can be easily encrypted.

sec_mgr@KNOX10g> DROP TABLE docs;    Table dropped.    sec_mgr@KNOX10g> CREATE TABLE docs     2   (doc_id NUMBER(4),    3    doc BLOB)     4   /    Table created.    sec_mgr@KNOX10g>  sec_mgr@KNOX10g> DECLARE     2   l_blob BLOB;    3   l_bfile BFILE;    4 BEGIN    5   INSERT INTO docs    6        VALUES (1, EMPTY_BLOB ())    7   RETURNING doc    8        INTO l_blob;     9    10   l_bfile := BFILENAME ('DOC_DIR', 'sample.xml');   11   DBMS_LOB.fileopen (l_bfile);   12   DBMS_LOB.loadfromfile (l_blob,   13                          l_bfile,    14                          DBMS_LOB.getlength (l_bfile));   15   DBMS_LOB.fileclose (l_bfile);   16 END;   17 /    PL/SQL procedure successfully completed.    sec_mgr@KNOX10g> EXEC data_crypto.setkey('This is the Key');    PL/SQL procedure successfully completed.    sec_mgr@KNOX10g> UPDATE docs     2     SET doc = data_crypto.encrypt (doc);    1 row updated.    sec_mgr@KNOX10g> COMMIT ;    Commit complete.   

Verifying the contents requires a BLOB to CLOB conversion. The BLOB is decrypted, then converted to a CLOB that is printed. The resulting decryption can be compared to the original by using the SQL*Plus Host command:

sec_mgr@KNOX10g> DECLARE     2    l_encrypted_blob   BLOB;    3    l_decrypted_blob   BLOB;    4    l_decrypted_clob   CLOB;    5    l_lang             NUMBER := DBMS_LOB.default_lang_ctx;    6    l_warning          NUMBER;     7    l_t_offset         NUMBER := 1;    8    l_src_offset       NUMBER := 1;    9  BEGIN   10    SELECT doc   11      INTO l_encrypted_blob   12      FROM docs;   13    14    DBMS_LOB.createtemporary (l_decrypted_blob, TRUE);   15    DBMS_LOB.createtemporary (l_decrypted_clob, TRUE);   16    -- decrypt BLOB   17    l_decrypted_blob := data_crypto.decrypt (l_encrypted_blob);   18    -- convert to CLOB for display   19    DBMS_LOB.converttoclob (l_decrypted_clob,   20                            l_decrypted_blob,   21                            DBMS_LOB.lobmaxsize,   22                            l_t_offset,   23                            l_src_offset,   24                            DBMS_LOB.default_csid,   25                            l_lang,   26                            l_warning    27  );   28    DBMS_OUTPUT.put_line (l_decrypted_clob);   29  END;   30 /  <?xml version="1.0"?>     <document>       <value>security rawks</value>    </document>    PL/SQL procedure successfully completed.   

You can see this data corresponds with the data in your file:

sec_mgr@KNOX10g> HOST cat C:\tmp\esbd\sample.xml    <?xml version="1.0"?>       <document>         <value>security rawks</value>      </document>    

This example used an XML file, which would probably be stored in a CLOB or as an XMLType. You cannot encrypt an XMLType because the database interprets and validates the XML automatically; trying to store encrypted data in this type will fail. BLOBs are ideal for storing true binary data, such as images and audio data. Encrypting this data provides added security.