Use the wallet to encrypt tablespace.
We can use the default directory to store the wallet or we use the sqlnet.ora file to specify the path to store it.
Add the following content into sqlnet.ora file then do not forget the restart the listener to take effect.
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=D:oradatawallet)))
We can use GUI to invoke the Oracle Wallet Manager to create the wallet.
We also can use the command : C:/Documents and Settings/Henrybaiwentao>mkstore -wrl c:/wallet -create
Oracle Secret Store Tool: 版本 11.2.0.1.0 - Production
版权所有 (c) 2004, 2009, Oracle 和 / 或其子公司。保留所有权利。
Or the SQL command as below:
SQL> alter system set encryption key identified by "!Q@W#E$R";
系统已更改。
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
E:/APP/HENRYBAIWENTAO/ADMIN/ORCL/WALLET
OPEN
Then we can create a tablespace with encryption.
SQL> create tablespace secure1 datafile 'E:/APP/HENRYBAIWENTAO/ADMIN/ORCL/secure1_01.dbf'
2 size 1m
3 encryption using 'AES128'
4 default storage (encrypt)
5 /
表空间已创建。
SQL>
SQL> create table secure_trans
2 tablespace secure1
3 as select * from dba_objects where rownum<201
4 /
表已创建。
SQL> select * from v$encrypted_tablespaces;
TS# ENCRYPT ENC
---------- ------- ---
7 AES128 YES
Encrypt the table:
SQL> create table accounts
2 (
3 acc_no number not null,
4 first_name varchar2(30) not null,
5 last_name varchar2(30) not null,
6 SSN varchar2(9) ENCRYPT USING 'AES128', --- tde on column.
7 acc_type varchar2(1) not null,
8 folio_id number ENCRYPT USING 'AES128',
9 sub_acc_type varchar2(30),
10 acc_open_dt date not null,
11 acc_mod_dt date,
12 acc_mgr_id number
13 );
SSN varchar2(9) ENCRYPT USING 'AES128', --- tde on column.
*
第 6 行出现错误:
ORA-28336: 不能加密 SYS 所拥有的对象
SQL> create table scott.accounts(
2 id number,
3 name varchar2(30) ENCRYPT USING 'AES128');
表已创建。
SQL> insert into scott.accounts values (1,'HENRY');
已创建 1 行。
SQL> insert into scott.accounts values(2,'BAI');
已创建 1 行。
SQL> select * from scott.accounts;
ID NAME
---------- ------------------------------
1 HENRY
2 BAI
SQL> alter system set wallet close;
alter system set wallet close
*
第 1 行出现错误:
ORA-28390: 自动登录 wallet 未打开
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
E:/APP/HENRYBAIWENTAO/ADMIN/ORCL/WALLET
OPEN
SQL> alter system set encryption wallet close identified by "welcome1";
alter system set encryption wallet close identified by "welcome1"
*
第 1 行出现错误:
ORA-28391: 无法关闭 wallet 或 HSM, 口令不匹配
SQL> alter system set encryption wallet close identified by "!Q@W#E$R";
系统已更改。
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
E:/APP/HENRYBAIWENTAO/ADMIN/ORCL/WALLET
CLOSED
SQL> select * from scott.accounts;
select * from scott.accounts
*
第 1 行出现错误:
ORA-28365: Wallet 未打开
SQL>