Column encryption - creating new table that has encrypted column(s)

In this recipe, you're going to learn to use TDE column encryption to encrypt columns in a newly created table.

Getting ready

It is assumed that a keystore is opened and a master key is created.

How to do it...

1. Connect to the database as a user who has administer key privilege or SYSKM privilege (for example, maja):

   $ sqlplus maja
   

2. Create a new table (for example, table enc_cols in schema hr) that has, for example, the following structure:

   Column name	Column type	Encrypted
   NAME	VARCHAR2 (50)	No
   CREDIT_LIMIT	NUMBER (10)	Yes, AES192
   SALARY	NUMBER (10)	Yes, AES192

   

   Figure 11 - A syntax to create the table hr.enc_cols

3. Connect to the database as a user who can insert and view data in the table (for example, hr user):

   SQL> connect hr
   

4. Insert several arbitrary values into the table HR.ENC_COLS.

   

   Figure 12 - Test values

5. Verify that the user can view unencrypted values in all columns.

   

   Figure 13- Encryption is transparent

6. Connect...