Tablespace Encryption

It introduced in 10g R2 .

It prevents the access to it from the Operating system. It allows encryption of the entire content of tablespace instead of column-by-column basis.

Wallet Creation:

Wallet must be created to hold the encryption key.

To setup the Encryption wallet Location, Configure the parameter in sqlnet.ora file

ENCRYPTION_WALLET_LOCATION=

  (SOURCE=(METHOD=FILE)(METHOD_DATA=

    (DIRECTORY=/u01/app/oracle/admin/DB11G/encryption_wallet/)))

Then the following command creates and open the wallet.

CONN sys/password@db11g AS SYSDBA

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "myPassword";

WALLET must be reopened after an instance restart and can be closed to prevent access to encrypted data

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "myPassword";

ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;

Tablespace Creation:

CREATE TABLESPACE encrypted_ts

DATAFILE '/u01/app/oracle/oradata/DB11G/encrypted_ts01.dbf' SIZE 128K

AUTOEXTEND ON NEXT 64K

ENCRYPTION USING 'AES256'

DEFAULT STORAGE(ENCRYPT);

ALTER USER test QUOTA UNLIMITED ON encrypted_ts;

SELECT tablespace_name, encrypted FROM dba_tablespaces;

TABLESPACE_NAME                ENC

------------------------------ ---

SYSTEM                         NO

SYSAUX                         NO

UNDOTBS1                       NO

TEMP                           NO

USERS                          NO

ENCRYPTED_TS                   YES

6 rows selected.

Test Encryption:

CONN test/test@db11g

CREATE TABLE ets_test (

  id    NUMBER(10),

  data  VARCHAR2(50)

)

TABLESPACE encrypted_ts;

CREATE INDEX ets_test_idx ON ets_test(data) TABLESPACE encrypted_ts;

INSERT INTO ets_test (id, data) VALUES (1, 'This is a secret!');

COMMIT;

Database Startup :

SQL>CONN / AS SYSDBA

Connected.

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP

ORACLE instance started.

Total System Global Area  626327552 bytes

Fixed Size                  2255832 bytes

Variable Size             234882088 bytes

Database Buffers          381681664 bytes

Redo Buffers                7507968 bytes

Database mounted.

Database opened.

SQL> SELECT tablespace_name, encrypted, status FROM dba_tablespaces;

TABLESPACE_NAME                ENC STATUS

------------------------------ --- ---------

SYSTEM                         NO  ONLINE

SYSAUX                         NO  ONLINE

UNDOTBS1                       NO  ONLINE

TEMP                           NO  ONLINE

USERS                          NO  ONLINE

EXAMPLE                        NO  ONLINE

SOE                            NO  ONLINE

ENCRYPTED_TS                   YES ONLINE

8 rows selected.

SQL> SELECT * FROM test.ets_test;

select * from test.ets_test

                   *

ERROR at line 1:

ORA-28365: wallet is not open

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "myPassword";

System altered.

sys@db11g> select * from test.ets_test;

        ID DATA

---------- --------------------------------------------------

         1 This is a secret!

1 row selected.

SQL>