Tuesday, October 2, 2012

Oracle 11g new features: Tablespace Encryption


Oracle 11g introduce tablespace encryption on base entire contexts of a tablespace rather than column basis.
Before creating an encryption tablespace a wallet must be created to keep encryption key.

To do that add below to sqlnet.ora

ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/admin/testdb/wallet)))

then follow instruction

[oracle@localhost admin]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 2 14:41:40 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected.
SQL> alter system set encryption key identified by "ulfet";
alter system set encryption key identified by "ulfet"
*
ERROR at line 1:
ORA-28353: failed to open wallet


Hmmmmm, could not create wallet...

Check directory if not exists (probably not exists) create it and restart database.

[oracle@localhost testdb]$ pwd
/u01/app/oracle/admin/testdb

[oracle@localhost testdb]$ ls
adump  dpdump  pfile

[oracle@localhost testdb]$ mkdir wallet


SQL> startup force;
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             306186624 bytes
Database Buffers          109051904 bytes
Redo Buffers                6094848 bytes
Database mounted.
Database opened.
SQL> 

then check again

SQL> alter system set encryption key identified by "ulfet";
System altered.

SQL> 

You can check for to be sure, created ewallet.p12 file on mentioned location.

Now let`s go to create encrypted tablespace and new user which default tablespace will be secure tablespace

SQL> create tablespace encrypt_data
datafile '/u01/app/oracle/oradata/testdb/secure_data01.dbf' size 20m
AUTOEXTEND ON NEXT 1M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE (ENCRYPT);  2    3    4    5  

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/testdb/system01.dbf
/u01/app/oracle/oradata/testdb/sysaux01.dbf
/u01/app/oracle/oradata/testdb/undotbs01.dbf
/u01/app/oracle/oradata/testdb/users01.dbf
/u01/app/oracle/oradata/testdb/example01.dbf
/u01/app/oracle/oradata/testdb/secure_data01.dbf

6 rows selected.

SQL> 


SQL> create user secure_bala identified by secure_bala default tablespace encrypt_data;

User created.


SQL> grant connect, resource to secure_bala;
Grant succeeded.

SQL> 

Now, connect as secure_bala and create table


SQL> conn secure_bala/secure_bala
Connected.

SQL> create table test (id number, name varchar2(15));

Table created.

SQL> insert into test values(1, 'TEHLUKELI');

1 row created.

SQL> insert into test values(2, 'DANGEROUS');

1 row created.

SQL> commit;

Commit complete.

SQL> 


However secure_bala user`s default tablespace is encrypt_data, we will provide to him unlimited quota to another unencrypted tablespace : USERS tablespace. To check which tablespace is encrypted you can select: 

SQL> conn /as sysdba
Connected.

SQL> select tablespace_name, encrypted FROM dba_tablespaces;

TABLESPACE_NAME                ENC
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          NO
EXAMPLE                        NO
ENCRYPT_DATA                   YES
7 rows selected.

SQL> 

SQL> alter user secure_bala quota unlimited on users;

User altered.

SQL> 


SQL> conn secure_bala/secure_bala
Connected.

SQL> create table test2 (id number, name varchar2(15)) TABLESPACE users;

Table created.

SQL> insert into test2 values(1, 'TEHLUKESIZ');
1 row created.

SQL> commit;
Commit complete.

SQL> 

To make sure the data is written to the datafile flush buffer:


SQL> conn /as sysdba
Connected.
SQL> alter system flush buffer_cache;

System altered.

SQL> 


Now try to open both datafile with advanced edit tool, example : editplus and search text : TEHLUKESIZ on users01.dbf tablespace you will see 







Now try to open with same tool and look up TEHLUKELI and DANGEROUS context




P.S: Wallets 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 "ulfet";

ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;

No comments:

Post a Comment