A couple of days ago I needed a quick solution that would limit access to some sensitive data (columns). Only a limited group of end users, selected developer and a DBA should see the content of some columns. Target table is otherwise stored in application schema that is shared by several developers, but only one of them should see the data. I knew that encryption is the way to go. Based on example published in Oracle 10g PL/SQL Packages and type reference for package DBMS_CRYPTO, I wrote simple package that will set decryption key for the session and allow for encryption and decryption of data.

Create or replace package dbcrypt is
  Procedure setkey( p_key  IN VARCHAR2 ); 
  Function encrypt( p_data IN VARCHAR2 ) Return RAW DETERMINISTIC;
  Function decrypt( p_data IN RAW ) Return VARCHAR2 DETERMINISTIC;
end dbcrypt;
/

Create or replace package body dbcrypt is
    
    ecryption_type PLS_INTEGER := SYS.DBMS_CRYPTO.ENCRYPT_AES128 + 
                                  SYS.DBMS_CRYPTO.CHAIN_CBC +
                                  SYS.DBMS_CRYPTO.PAD_PKCS5;
    v_key RAW(16) := null;
    
    Procedure setkey (p_key IN VARCHAR2)
    IS
    BEGIN
      if p_key is not null then
        v_key := UTL_RAW.cast_to_raw(p_key);
       dbms_output.put_line(v_key);
      end if;
    END setkey;
    
    Function encrypt( p_data IN VARCHAR2 ) Return RAW DETERMINISTIC
    IS
      l_data RAW(2048) := UTL_I18N.STRING_TO_RAW(p_data,'AL32UTF8');
      l_encrypted RAW(2048);
    BEGIN
      l_encrypted := sys.dbms_crypto.encrypt                       
                     ( src => l_data,
                       typ => ecryption_type,
                       key => v_key );
      Return l_encrypted;
      EXCEPTION 
        WHEN OTHERS THEN
          -- for the security reason I want to completely silence the error 
          -- stack that could reveal some technical details to imaginary attacker.
          -- Remember, such miss-use of WHEN OTHERS should be considered 
          -- as a bug in almost all other situations.
          RAISE_APPLICATION_ERROR(-20001,'Access denied!');
    END encrypt;

    Function decrypt( p_data IN RAW ) Return VARCHAR2 DETERMINISTIC
    IS
      l_decrypted RAW(2048);
    BEGIN
      l_decrypted := sys.dbms_crypto.decrypt                              
                      ( src => p_data,
                        typ => ecryption_type,
                        key => v_key );
      Return UTL_I18N.RAW_TO_CHAR(l_decrypted,'AL32UTF8');
      EXCEPTION 
        WHEN OTHERS THEN
          RAISE_APPLICATION_ERROR(-20001,'Access denied!');
    END decrypt;
End dbcrypt;
 /

Let’s see dbcrypt package in action:

--
-- Let's pretend we want to protect first and 
-- last name in table MY_EMP. 
-- Note that encrypted values are much larger than
-- non-encrypted, that's why I used VARCHAR2(100)
--
create table my_emp (
 id  number(10) not null,
 first_name varchar2(100),
 last_name  varchar2(100));

--
-- We can prepare view that will decrpyt columns,
-- this view can then be used in application.
--
create or replace view v_my_emp as
  select id, 
  substr(dbcrypt.decrypt(first_name),1,35) as First_Name,
  substr(dbcrypt.decrypt(last_name),1,35) as Last_Name
  from my_emp;  
--
-- once per session we need to set key that
-- is used fro encryption/decryption for the duration of session.
--
execute dbcrypt.setkey('mysecretpassword');
--
--
-- now, wen can insert some encrypted data
--
insert into my_emp values(1,dbcrypt.encrypt('Bruce'),dbcrypt.encrypt('Scott'));
--
-- you can check that column data is encrypted
-- 
select id, first_name, last_name from my_emp;

  ID FIRST_NAME                       LAST_NAME
---- -------------------------------- --------------------------------
   1 372EC43843E2782F30B56DA46034324D 65FE9535E2FF1FA61B495C7A29F1779F
--
-- since encryption key was already set (see execute statement above),
-- we can simply select from the view and view will take care 
-- of decryption.
--
SQL> select * from v_my_emp;

  ID FIRST_NAME                       LAST_NAME
---- -------------------------------- ----------
   1 Bruce                            Scott

Of course we must be careful that encryption key is not lost and/or exposed (network sniffing, written in some configuration file for application etc.), but this is a different story.