Tablespace Encryption Setup
1. Make sure the ORACLE_BASE
variable has been set. If it has not, set it by issuing
$ export ORACLE_BASE=/app/oracle
2. Change to the
ORACLE_BASE directory and then to the admin subdirectory for that instance. In
my case, the instance is named prolin1, so I issue
$ cd $ORACLE_BASE/admin/ora11g
3. Create a directory
called “wallet” to hold the wallet:
$ mkdir wallet
$ sqlplus / as sysdba
SQL> alter system set encryption key authenticated by "ASHOK";
The password is case-sensitive.
After the database is opened, the wallet
remains open until either the wallet is explicitly closed or the database is
shut down, at which time the wallet automatically closes. You can reopen this
wallet after the database is restarted, by using
SQL> alter system set wallet open identified by "ASHOK";
Now that the wallet is set up, we can create
the encrypted tablespace.
1.
The following code sets up an encrypted tablespace named encrypt_data_tbl
2.
create tablespace
encrypt_data_tbl datafile '/app/oracle/oradata/inc11g/encrypt_data01.dbf' size 100m ENCRYPTION USING 'AES256' DEFAULT
STORAGE (ENCRYPT);
Also we can resize and make it auto extent
ALTER
DATABASE DATAFILE
'/app/oracle/oradata/inc11g/encrypt_data01.dbf'
RESIZE
200M;
ALTER
DATABASE DATAFILE
'/app/oracle/oradata/inc11g/encrypt_data01.dbf'
AUTOEXTEND ON;
Note the special encryption using 'AES128’
clause, which indicates that the AES algorithm is to be used with a 128-bit
key. You can also use the values AES192 and AES256 (in place of AES128, the
default value) to use 192- and 256-bit keys, respectively.
3.
Once the tablespace is created, we can create objects in it. For
instance, the following code creates a table called ALL_LOOKENCRYPT
SQL> alter
user ashok quota
unlimited on encrypt_data_tbl;
User altered.
CREATE
TABLE ashok.ALL_LOOKENCRYPT
(
ALL_LOOKUPS_PKEY NUMBER NOT NULL,
TYPE VARCHAR2 (50 BYTE) NOT NULL,
TYPE_DESCRIPTION VARCHAR2 (100 BYTE),
CODE VARCHAR2
(100 BYTE) NOT NULL,
CODE_DESC VARCHAR2
(250 BYTE),
VALUE VARCHAR2 (100 BYTE) NOT NULL,
VALUE_DESC VARCHAR2
(250 BYTE)
)
TABLESPACE encrypt_data_tbl;
SQL>insert into ashok.ALL_LOOKENCRYPT select ALL_LOOKUPS_PKEY,TYPE,TYPE_DESCRIPTION,CODE,CODE_DESC,VALUE,VALUE_DESC from ashokan.all_lookups where ALL_LOOKUPS_PKEY<=10;
To confirm encryption,
This will not show any output, because the MARRIED value has been stored in an encrypted tablespace. Searching for a cleartext value found nothing, as expected. (Note that if we do a string search in a file of an unencrypted tablespace, a successful search will return the cleartext from the file.) see below
Next, let’s create two identical tables— ALL_LOOKENCRYPT and ALL_LOOKNONENCRYPT —in tablespaces normal_ts and encrypt_data_tbl (the encrypted tablespace we created earlier), respectively, and populate the tables in the same way.
create tablespace nonencrypt_data_tbl datafile '/app/oracle/oradata/inc11g/nonencrypt_data01.dbf' size 100m autoextend on next 1M;
CREATE
TABLE ashok.ALL_LOOKNONENCRYPT
(
ALL_LOOKUPS_PKEY NUMBER NOT NULL,
TYPE VARCHAR2 (50 BYTE) NOT NULL,
TYPE_DESCRIPTION VARCHAR2 (100 BYTE),
CODE VARCHAR2
(100 BYTE) NOT NULL,
CODE_DESC VARCHAR2
(250 BYTE),
VALUE VARCHAR2 (100 BYTE) NOT NULL,
VALUE_DESC VARCHAR2
(250 BYTE)
)
TABLESPACE nonencrypt_data_tbl;
insert
into ashok.ALL_LOOKNONENCRYPT select ALL_LOOKUPS_PKEY,TYPE,TYPE_DESCRIPTION,CODE,CODE_DESC,VALUE,VALUE_DESC
from ashok.ALL_LOOKENCRYPT where ALL_LOOKUPS_PKEY<=10;
SET DEFINE OFF;
Insert
into ashok.ALL_LOOKNONENCRYPT
(ALL_LOOKUPS_PKEY, TYPE, CODE, VALUE)
Values
(12, 'MARITAL_STATUS', 'MS1',
'MARRIED');
COMMIT;
strings
nonencrypt_data01.dbf | grep MARRIED
see below
Tablespace Encryption and Performance
An issue with any encryption
method is the negative impact on performance.
For
example, searches for patterns in the values in an encrypted column may or may
not be able to use indexes. That is exactly where TDE tablespace encryption
excels—it allows data to be in cleartext in the buffer cache, where all the
searching occurs.
To
accurately ascertain the performance impact of encryption in the tablespace, we
can do a small test.
Try with
encrypted table
set autot on explain stat
set timing on
SELECT
ALL_LOOKUPS_PKEY, TYPE, TYPE_DESCRIPTION,
CODE, CODE_DESC, VALUE,
VALUE_DESC
FROM ASHOK.ALL_LOOKENCRYPT
Where
TYPE = 'EMPLOYMENT_STATUS'
/
6 rows selected.
Elapsed: 00:00:00.14
Execution Plan
----------------------------------------------------------
Plan hash value: 2677629693
--------------------------------------------------------------------------------
-----
| Id |
Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | 6 | 2700 | 3 (0)| 00:00
:01 |
|* 1 | TABLE ACCESS FULL| ALL_LOOKENCRYPT | 6 | 2700 | 3 (0)| 00:00
:01 |
--------------------------------------------------------------------------------
-----
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TYPE"='EMPLOYMENT_STATUS')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
1032 bytes sent via SQL*Net to client
419 bytes received via
SQL*Net from client
2 SQL*Net roundtrips to/from
client
0 sorts (memory)
0 sorts (disk)
6 rows processed
SQL>
Try with NON
encrypted table
set autot on explain stat
set timing on
SELECT
ALL_LOOKUPS_PKEY, TYPE, TYPE_DESCRIPTION,
CODE, CODE_DESC, VALUE,
VALUE_DESC
FROM ASHOK.ALL_LOOKNONENCRYPT
Where
TYPE = 'EMPLOYMENT_STATUS'
/
6 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2487804367
--------------------------------------------------------------------------------
--------
| Id |
Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 6 | 2700 | 3 (0)| 00
:00:01 |
|* 1 | TABLE ACCESS FULL| ALL_LOOKNONENCRYPT | 6 | 2700 | 3 (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TYPE"='EMPLOYMENT_STATUS')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
1032 bytes sent via SQL*Net
to client
419 bytes received via
SQL*Net from client
2 SQL*Net roundtrips to/from
client
0 sorts (memory)
0 sorts (disk)
6 rows processed
The execution time is not approximately
the same for the query in both the unencrypted and encrypted tablespaces, with
about the same number of blocks fetched in both cases.
Finally, let’s create an
index on each of the tables on the TYPE column.
Create
index all_look_enc_idx
on ALL_LOOKENCRYPT (TYPE)
tablespace
encrypt_data_tbl
/
create
index all_look_nonenc_idx
on ALL_LOOKNONENCRYPT (TYPE)
tablespace
nonencrypt_data_tbl
/
begin
dbms_stats.gather_table_stats
(
ownname => 'ASHOK',
tabname => 'ALL_LOOKENCRYPT',
estimate_percent => 100,
cascade => true
);
end;
/
begin
dbms_stats.gather_table_stats
(
ownname => 'ASHOK',
tabname => 'ALL_LOOKNONENCRYPT',
estimate_percent => 100,
cascade => true
);
end;
/
ALTER SYSTEM FLUSH BUFFER_CACHE;
Index
scan on table in encrypted tablespace
set autot on explain stat
set timing on
SELECT
ALL_LOOKUPS_PKEY, TYPE, TYPE_DESCRIPTION,
CODE, CODE_DESC, VALUE,
VALUE_DESC
FROM ASHOK.ALL_LOOKENCRYPT
Where
TYPE = 'EMPLOYMENT_STATUS'
/
6 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2666554740
--------------------------------------------------------------------------------
----------------
| Id |
Operation | Name | Rows | Bytes | Cost (%
CPU)| Time |
--------------------------------------------------------------------------------
----------------
| 0 | SELECT STATEMENT | | 6 | 228 | 3
(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ALL_LOOKENCRYPT | 6 | 228 | 3
(0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ALL_LOOK_ENC_IDX | 6 | |
1
(0)| 00:00:01 |
--------------------------------------------------------------------------------
----------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TYPE"='EMPLOYMENT_STATUS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1118 bytes sent via SQL*Net to client
419 bytes received via
SQL*Net from client
2 SQL*Net roundtrips to/from
client
0 sorts (memory)
0 sorts (disk)
6 rows processed
SQL>
Index
scan on table in unencrypted tablespace
set autot on explain stat
set timing on
SELECT
ALL_LOOKUPS_PKEY, TYPE, TYPE_DESCRIPTION,
CODE, CODE_DESC, VALUE,
VALUE_DESC
FROM ASHOK.ALL_LOOKNONENCRYPT
Where
TYPE = 'EMPLOYMENT_STATUS'
/
6 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 592077950
--------------------------------------------------------------------------------
-------------------
| Id |
Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
-------------------
| 0 | SELECT STATEMENT | | 6 | 228 |
3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ALL_LOOKNONENCRYPT | 6 | 228 |
3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ALL_LOOK_NONENC_IDX | 6 | |
1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
-------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TYPE"='EMPLOYMENT_STATUS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1118 bytes sent via SQL*Net to client
419 bytes received via
SQL*Net from client
2 SQL*Net roundtrips to/from
client
0 sorts (memory)
0 sorts (disk)
6 rows processed
The execution time is
approximately the same for the query in both the unencrypted and encrypted
tablespaces, with about the same number of blocks fetched in both cases. This
shows that there is no significant performance penalty for querying tables in
encrypted tablespaces.
Comparison
of Tablespace and Column-Level Encryption
create
table ALL_LOOKENCRYPT_FIELD
nologging
as
select
* from ALL_LOOKNONENCRYPT;
alter
table ALL_LOOKENCRYPT_FIELD
modify
TYPE encrypt
using 'AES256'
no salt;
CREATE
INDEX ASHOK.ALL_LOOK_ENC_IDX1 ON ASHOK.ALL_LOOKENCRYPT_FIELD
(TYPE)
LOGGING
TABLESPACE ENCRYPT_DATA_TBL;
SELECT
DISTINCT TABLESPACE_NAME, owner,table_name
FROM all_tables
WHERE owner='ASHOK' and table_name='ALL_LOOKENCRYPT_FIELD';
SELECT DISTINCT TABLESPACE_NAME, TABLE_OWNER
FROM all_indexes
WHERE owner='ASHOK' and table_name='ALL_LOOKENCRYPT_FIELD';
Query comparison of column-level and tablespace
encryption
set autot on explain stat
set timing on
SELECT
count(1)
FROM ASHOK.ALL_LOOKENCRYPT_FIELD
Where
TYPE = 'EMPLOYMENT_STATUS'
/
COUNT(1)
----------
6
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
Plan hash value: 3909791586
--------------------------------------------------------------------------------
-------
| Id |
Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 1 | 61 | 1 (0)| 00:
00:01 |
| 1 | SORT AGGREGATE | | 1 | 61 | |
|
|* 2 | INDEX RANGE SCAN| ALL_LOOK_ENC_IDX1 | 6 | 366 | 1 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TYPE"='EMPLOYMENT_STATUS')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
419 bytes received via
SQL*Net from client
2 SQL*Net roundtrips to/from
client
0 sorts (memory)
0 sorts (disk)
1 rows processed
set autot on explain stat
set timing on
SELECT
count(1)
FROM ASHOK.ALL_LOOKNONENCRYPT
Where
TYPE = 'EMPLOYMENT_STATUS'
/
COUNT(1)
----------
6
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2984913500
--------------------------------------------------------------------------------
---------
| Id |
Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 1 | 17 | 1 (0)| 0
0:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | |
|
|* 2 | INDEX RANGE SCAN| ALL_LOOK_NONENC_IDX | 6 | 102 | 1 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TYPE"='EMPLOYMENT_STATUS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
419 bytes received via
SQL*Net from client
2 SQL*Net roundtrips to/from
client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Consider the difference.
which resulted in a
reduction of consistent gets from 7 to a mere 1 -- less than 6 percent of the original value. This means that the query
against the encrypted tablespace table generated just 6 percent of the logical
I/O of the query on the table with the encrypted column.
A second major difference is in column
restrictions. Certain datatypes can’t be encrypted with TDE column encryption,
columns under TDE can’t be used for foreign keys, only B-tree indexes can be
created against the columns under TDE—and these are just some of the
limitations of TDE column encryption. However, there is no restriction on these
objects in an encrypted tablespace.
TDE column encryption has
additional space requirements. Typically, encrypted values are larger than
unencrypted values, causing a table with column encryption to be larger
overall. To check for the exact increase, we can use the show_space procedure
to show the space inside the tables.
Conclusion
In general, encryption solves a
security issue But degraded performance.
