Encrypting Columns in Tables | Utimaco Integration Guides

In order to create a table with an encrypted column, the _ENCRYPT keyword needs to be added, when specifying the _COLUMN. Any user with an access to the encrypted columns can read the data from these columns, but the data is encrypted on the disk.

Example:

›_ Console

SQL> CREATE TABLESPACE notsecurespace 

  DATAFILE '/u01/app/oracle/product/12.1.0.2/db_1/notsecure01.dbf'   SIZE 150M; 

Tablespace created. 

SQL> alter session set "_ORACLE_SCRIPT"=true; 

SQL> CREATE USER user2 IDENTIFIED BY Pwd01 DEFAULT TABLESPACE notsecurespace;  

SQL> GRANT DBA To user2; 

SQL> CONNECT user2/Pwd01 

SQL> CREATE TABLE cust_payment_info  

  (first_name VARCHAR2(11),    last_name VARCHAR2(10),    order_number NUMBER(5),    credit_card_number VARCHAR2(16) ENCRYPT NO SALT,   active_card VARCHAR2(3));

Insert one row:

›_ Console

SQL> INSERT INTO cust_payment_info 

  (first_name, last_name, order_number, credit_card_number, active_card) 

  VALUES ('John', 'White', 22, '2643282394', 'ACT');

Indexes can be created on the encrypted columns:

›_ Console

SQL> CREATE INDEX cust_payment_info_idx ON cust_payment_info (credit_card_number);

If a column in an existing table needs to be encrypted, the table has to be altered:

›_ Console

SQL> ALTER TABLE cust_payment_info MODIFY (first_name VARCHAR2(11) ENCRYPT);