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;
Comments
Post a Comment