Oracle PL/SQL for DBAs

A Practical Encryption Example

How can we use our newfound knowledge of encryption in a system that we might encounter in the real world? Let’s examine a table named ACCOUNTS. The table looks like this.

    SQL> DESC accounts
     Name                                      Null?    Type
     ----------------------------------------- -------- -------------------
     ACCOUNT_NO                                NOT NULL NUMBER
     BALANCE                                            NUMBER
     ACCOUNT_NAME                                       VARCHAR2(200)

I want to protect the data by encrypting the columns BALANCE and ACCOUNT_NAME. As I’ve said many times, the most important element is the key, and it must be an appropriate one. I can generate a key, use it to encrypt the column value, and then store the key and the encrypted value somewhere to be retrieved later. How exactly can I do this? I have a few choices:

• I could define a view on the table as follows:

  1. Add the columns ENC_BALANCE and ENC_ACCOUNT_NAME to the table to store the encrypted values of the corresponding columns.

  2. Add another column named ENC_KEY to store the key used for encryption.

  3. Create a view called VW_ACCOUNTS defined as follows:

         CREATE OR REPLACE VIEW vw_accounts
         AS
            SELECT account_no.enc_balance AS balance
                 , enc_account_name AS account_name
              FROM accounts
         /

  4. Create INSTEAD OF triggers to handle updates and inserts to the view, if needed.

  5. Create a public synonym ACCOUNTS for the view VW_ACCOUNTS.

  6. Grant all privileges on VW_ACCOUNTS and revoke all privileges on ACCOUNTS.

  This arrangement ensures that the schema owner, as well as any users who have been given direct privileges on the ACCOUNTS table, ...