Column and Tablespace Encryption

This section includes the following topics:

• Column Encryption

• Tablespace Encryption

Column Encryption

1. Create a table with column encryption option.

   CREATE TABLE EMPLOYEES (ID INT, NAME VARCHAR (200), SALARY INT ENCRYPT);
   

2. Enter some values.

   INSERT INTO EMPLOYEES VALUES (1, 'Bob', 10000);
   

3. Access the data using the SELECT query.

   SELECT * FROM EMPLOYEES;
   

4. Check the encrypted columns in the database.

   SELECT * FROM DBA_ENCRYPTED_COLUMNS;
   

Tablespace Encryption

1. Create an encrypted tablespace. Then create a table in that tablespace.

   CREATE TABLESPACE ENCRYPTED_SPACE DATAFILE '$ORACLE_HOME/dbs/enc_space.dbf' SIZE 150M ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT);
   CREATE TABLE CUSTOMERS (ID INT, NAME VARCHAR (200), CREDIT_LIMIT INT) TABLESPACE ENCRYPTED_SPACE;
   

   Note

   • ENCRYPTION USING 'AES256' specifies the encryption algorithm and the key length for the encryption. Enclose this setting in the single quotes (' '). The key lengths are included in the names of the algorithms.

   • If you do not specify an encryption algorithm then the default encryption algorithm AES128 is used. You can choose from the following algorithms: 3DES168, AES128, AES192, AES256. ENCRYPTION keyword in the query is used to specify the encryption algorithm. The ENCRYPT keyword in the query actually encrypts the tablespace.

2. Insert some values into the CUSTOMERS table.

   INSERT INTO CUSTOMERS VALUES (1, 'Bob', 10000);
   

3. Access the data using the select query.

   SELECT * FROM CUSTOMERS;
   

4. Check the encrypted tablespaces in the database:

   SELECT * FROM V$ENCRYPTED_TABLESPACES;